Microsoft Office Tutorials and References

In Depth Information

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

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
type
)
fv,

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

determine 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