Loan Payment Table Generator Example (Screen Shot)

This screen shot shows a table of loan payments for a range of loan amounts and interest rates. The spBasic program below is used to generate the table from information provided by the user in cells b3 to b7. The program uses the Excel 'PMT' function.

The screen shot below shows the spBasic Edit Form for the 'PaymentTable' program.

You can copy the listing below and insert it into your Edit Form. Insert the variables into a worksheet column. This program is also included in the 'C:\Program Files\spBasic\Examples\' directory.

NOTE: Install the Analysis Toolpak AddIn to use the advanced Excel functions.

// variables
i
k

End

// ----- PaymentTable -----
// NOTE: anything after the characters "//" are comments and
//             are ignored by the program

clear c2:i22
// --- fill the amount cells ---
d2 = b3                                          // initial amount in cell b3 (set by user)
for i = 5 to 9                                   // col e to I
  cellv(i,2) = cellv(i-1,2) + b4          // amount Increment in cell b4 (set by user)
next
// --- fill the interest cells ---
c3 = b5                                          // interest in cell b5 (set by user)
for i = 4 to 22 //row 3 to 21
  cellv("c",i) = cellv("c",i-1) + b6     // interest Increment in cell b6 (set by user)
next
// --- fill in the payment matrix ---
for i= 4 to 9                                    // loop over amount columns
  for k=3 to 22                               // loop over interest rows
    // --- use Excel PMT function ---
    // PMT(interest, # periods, present value, Future Value = 0, pay at the end of the period = 0)
    cellv(i,k) = -PMT(cellv(3,k)/12 ,b7 ,cellv(i,2) ,0, 0)       // # periods in cell b7 (set by user)
  next k
next i

End