Microsoft Office Tutorials and References

In Depth Information

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

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.