|
|
Yesterday I have shown you how to program
a VBA that can calculate monthly payment for a loan taken by a borrower. In this
example, the financial VBA calculator is doing the same job but I use the
built-in worksheet function, PMT. It is very much easier to program than the
previous one. The format of this function is
pmt (rate,
N, amount) where rate is the interest rate, N is the period of payments (of
number of periodic payments) and amount is the amount borrowed.
Here is the VBA program:
Private Sub CommandButton1_Click()
Dim rate, N As Integer
Dim amt, payment As Double
amt = Cells(2, 2)
rate = (Cells(3, 2) / 100) / 12
N = Cells(4, 2) * 12
payment = WorksheetFunction.pmt(rate, N, -amt)
Cells(5, 2) = Format(payment, "$##,###.00")
End Sub
|
Explanation:
Normally people will
key in the annual interest rate as an integer rather than in decimal
form, so I need divide the rate by 100 and then divide again by 12
to get the monthly rate.
I put a negative
sign in front of the amount borrowed because this is the amount the
borrower owed the financial institute, so it should be negative. If
we don't put negative, the payment will have a negative sign.
|
|
