Page 235 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

If you wanted to borrow $20,000 at an 8 percent interest rate and pay the loan back over 24
months, you could write the formula in a worksheet cell as =PMT(8%/12, 24, 20000), which
results in a monthly payment of $904.55. In Excel VBA, you would once again use the
Application.WorksheetFunction object to call the PMT function within a VBA function pro­
cedure. The sample procedure assumes the rate is in cell B2, the number of payments is in cell
B3, the amount borrowed is in cell B4, the amount owed at the end of the loan (always 0) is
in cell B5, and the time when a payment is made (leave blank to use the default value) is in
cell B6.

Public Function MonthlyPayment(rate, nper, pv, fv, when) As Currency
With Application.WorksheetFunction
MonthlyPayment = .Pmt(rate / 12, nper, pv, fv, when)
End With
End Function

Public Sub Payment()
MsgBox (MonthlyPayment(Range("B2"), Range("B3"), Range("B4"), _
Range("B5"), Range("B6")))
End Sub
Chapter 9

Warning You might have noticed that in the MonthlyPayment function the type argument
is replaced with when—it’s because type is a reserved word in VBA and Excel generates an
error when the compiler encounters it.

There are also Excel worksheet functions that you can use to determine the amount of a pay­
ment devoted to interest and to the loan’s principal. These calculations are important for tax
reasons. For example, if the interest on your home loan is tax-deductible, it helps to know
exactly how much of each monthly payment represents interest and how much pays down
the principal. The IPMT worksheet function lets you calculate how much of a payment goes
toward interest. The syntax of the IPMT function is similar to the PMT function’s syntax, but
there are some key differences.

IPMT(rate,per,nper,pv,fv,type)
The rate, pv, fv, and type arguments all mean the same as they do in the PMT function, but
the per argument is new. The per argument represents the period for which you want to find
the interest and must be somwhere between 1 and nper. For example, if you wanted to deter-
mine how much of each month’s payment is devoted to interest, you could do so using the
following procedure, which places the resulting value in the active cell:

Public Sub DetermineInterest()
Dim intRate, intPer, intNper As Integer
Dim curPv, curInterest As Currency

209
Part 4 Advanced VBA