Calculate Interest Rate Method

The Calculate Interest Rate method calculates, and then returns the interest rate for each period for an annuity or a loan. This method is iterative. It improves a guess over multiple iterations until the result is within 0.00001 percent. If it does not converge to a result in 20 iterations, then it displays a failure message.

Format

Rate(nper, pmt, pv, fv, due, guess)

For more information, see Arguments You Can Use with Financial Methods.

Example

The following example calculates the interest rate on a 10 year, $25,000 annuity that pays $100 for each month:

Sub Button_Click
   Dim aprate
   Dim periods
   Dim payment, annuitypv
   Dim annuityfv, due
   Dim guess
   Dim msgtext as String
   periods = 120
   payment = 100
   annuitypv = 0
   annuityfv = 25000
   guess = .1
   ' Assume payments are made at end of month
   due = 0
   aprate = Rate(periods,-payment,annuitypv,annuityfv, _ 
   due, guess)
   aprate = (aprate * 12)
   msgtext = "The percentage rate for a 10-year $25,000 _
   annuity"
   msgtext = msgtext & "that pays $100/month has "
   msgtext = msgtext & "a rate of: " & Format(aprate, _
      "Percent")
End Sub