Loan Balance Calculation using Excel PMT Function (Screen Shot)

This screen shot shows a table of loan balance vs. dates using the Excel PMT function. The payment remains constant over the loan period, but the last payment is adjusted to take care of roundoff occuring during the interest calculations. The spBasic program below is used to generate the table from information provided by the user in the variable values c3 to c6. The date calculation uses the Excel DATE , YEAR, MONTH, and DAY functions.

The screen shot below shows the spBasic Edit Form for the 'LoanPayment' 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
startDate
loanAmount
interestRate
numMonths
payment
i

End

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

Clear d2:i50
// -- cells c3 through c6 are set by the user --
startDate = c3
loanAmount = c4
InterestRate = c5
numMonths = c6
// -- get a constant payment using the PMT function --
payment = -PMT(interestRate/12,numMonths, loanAmount, 0, 0)
// -- loop over number of months --
for i = 2 to numMonths + 1
  if i = 2 then
    // -- set the initial values on the first loop --
    d2 = startdate
    e2 = loanAmount
  else
    // -- after the initial value, use previous cell info --
    d<<i>> = DATE(year(d<<i-1>>),month(d<<i-1>>)+1,day(d<<i-1>>))     // new date
    e<<i>> = i<<i-1>> // loan balance
  endif
  g<<i>>=(interestRate/12) * e<<i>>    // interest amount = interest * loan balance
  // -- compute the payment --
  // -- check for the last month --
  if i = numMonths + 1 then
    // -- if the last month, final payment = loan balance + interest --
    f<<i>> = e<<i>> + g<<i>>      // final payment
  else
    // -- if not the last month, keep the payment constant --
    f<<i>> = payment
  endif
  // amount paid on principle = payment - interest
  h<<i>> = f<<i>> - g<<i>>
  // loan balance = original balance - amount paid on principle
  i<<i>> = e<<i>> - h<<i>>
next
End

Loan Balance Calculation using Excel IPMT, PPMT Functions (Screen Shot)

This screen shot shows a table of loan balance vs. dates using the Excel functions IPMT and PPMT. These functions give the interest and principle payments for a loan over a fixed payment period. The spBasic program below is used to generate the table from information provided by the user in the variable values c3 to c6. The date calculation uses the Excel DATE , YEAR, MONTH, and DAY functions. Note that the payment is not constant.

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

You can copy the listing below and insert it into your Edit Form. Insert the variables into a worksheet column.

// variables
startDate
loanAmount
interestRate
numMonths
i

End

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

Clear d2:i50
// -- cells c3 through c6 are set by the user --
startDate = c3
loanAmount = c4
InterestRate = c5
numMonths = c6
// -- loop over number of months --
for i = 2 to numMonths + 1
  if i = 2 then
    // -- set the initial values on the first loop --
    d2 = startdate
    e2 = loanAmount
  else
    // -- after the initial value, use previous cell info --
    d<<i>> = DATE(year(d<<i-1>>),month(d<<i-1>>)+1,day(d<<i-1>>))     // new date
    e<<i>> = i<<i-1>>
  endif
  g<<i>>=-IPMT(interestRate/12,i-1,numMonths,loanAmount,0)                   // interest amount
  h<<i>> = -PPMT(interestRate/12,i-1,numMonths,loanAmount,0)               // amount paid on principle
  // -- check for the last month --
  if i = numMonths + 1 then
    // -- if the last month, final payment = loan balance + interest --
    f<<i>> = e<<i>> + g<<i>>
  else
    // -- if not the last month, payment = amount paid on principle + interest --
    f<<i>> = g<<i>> + h<<i>>
  endif
  i<<i>> = e<<i>> - h<<i>>   // loan balance = previous loan balance - amount pain on principle
next
End