In the previous chapter, we have shown you how to program a VBA program 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 we use the built-in worksheet function, PMT. It is very much easier to program than the previous one. The syntax of this function is
WorksheetFunction.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 Code:
As people tend key in the annual interest rate as an integer rather than in decimal form, so we need to divide the rate by 100 and then divide it again by 12 to get the monthly rate.
We 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.
Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved