Page 236 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

intRate = InputBox("What is the interest rate (as an integer)?")
intPer = InputBox("For which month do you want to find the interest?")
intNper = InputBox("How many payments will you make on the loan?")
curPv = InputBox("How much did you borrow?")

With Application.WorksheetFunction
curInterest = -1 *(.IPmt(intRate / 1200, intPer, intNper, curPv))
End With
ActiveCell.Value = curInterest
End Sub

Note This procedure multiplies curInterest by -1 to produce a positive result. It’s true that
your cash flow is negative, but most folks like to think of payments in positive numbers (if
not positive terms).

You can list how much each payment contributes to interest by adding a For…Next loop
around the periodic interest calculation, which places the resulting values in a column start­
ing with the active cell.
Public Sub DetermineAllInterest()
Dim intRate, intPer, intNper, intPayment As Integer
Chapter 9
Dim curPv, curInterest As Currency
intRate = InputBox("What is the interest rate (integer number only)?")
intNper = InputBox("How many payments will you make on the loan?")
curPv = InputBox("How much did you borrow?")
For intPer = 1 To intNper
With Application.WorksheetFunction
curInterest = -1 * (.IPmt(intRate / 1200, intPer, intNper, curPv))
'Divide by 1200 to get a monthly percentage (12 months * 100 per cent)
End With
ActiveCell.Value = curInterest
ActiveCell.Offset(1, 0).Activate
Next intPer

End Sub
The complementary function of IPMT is PPMT, which determines the amount of a payment
that is devoted to the loan’s principal. The PPMT function’s syntax is exactly the same as that
of IPMT, but the result is the dollar amount of a payment that is applied to the principal.

Now that you’ve seen the functions you use to determine your payments, and what share of
those payments go toward interest and principal, it’s time to show you how to reverse engi­
neer an interest rate from a known payment schedule. Yes, it’s rare that you would need to
figure out your interest rate for a home loan (because it’s something few folks forget, and it’ll
be right there on the loan papers), but just in case you need to, here it is.

210
Part 4: Advanced VBA