Rate Function Example

This example calculates the interest rate of a loan given the total number of payments (TotPmts), the amount of the loan payment (Payment), the present value or principal of the loan (PVal), the future value of the loan (FVal), a number that indicates whether the payment is due at the beginning or end of the payment period (PayType), and an approximation of the expected interest rate (Guess).

Const ENDPERIOD = 0, BEGINPERIOD = 1     ' When payments are made.
Const MB_YESNO = 4                       ' Define Yes/No buttons.
Const ID_NO = 7                          ' Define No as a response.
Fmt = "##0.00"                           ' Define percentage format.
FVal = 0                                 ' Usually 0 for a loan.
Guess = .1                               ' Guess of 10 percent.
PVal = InputBox("How much did you borrow?")
Payment = InputBox("What's your monthly payment?")
TotPmts = InputBox("How many monthly payments do you have to make?")
PayType = MsgBox("Do you make payments at the end of the month?", MB_YESNO)
If PayType = ID_NO Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
APR = (Rate(TotPmts, -Payment, PVal, FVal, PayType, Guess) * 12) * 100
MsgBox "Your interest rate is " & Format(CInt(APR), Fmt) & " percent."