VBA RATE Function

HomeVBA Functions LIST (Category Wise)How to use the VBA RATE Function (Syntax + Example)

What is VBA RATE Function

The VBA RATE function is listed under the financial category of VBA functions. When you use it in a VBA code, it returns the interest rate per period (with the equal cash flow) for a loan or investment.

how to use it

To use VBA's RATE function you need to understand its syntax and arguments:

Syntax

RATE(Nper, Pmt, Pv, [Fv], [Due], [Guess])

Arguments

  • Nper: The number of periods to pay the loan or investment.
  • Pmt: The fixed amount of payment per period.
  • PV: The present value of investment or loan.
  • [FV]: The future value of the loan/investment (This is an optional argument and if omitted VBA takes 0 by default).
  • [Due]: Defines whether the payment is due at the start or the end of the period, use 0 for the end of the period and 1 for the start of the period [This is an optional argument and if omitted VBA takes the end of the period by default].
  • [Guess]: An initial estimate (guess) what will be the IRR [This is an optional argument and if omitted VBA takes 10% (=0.1) by default].

Example to use RATE Function in VBA

To practically understand how to use VBA RATE function, you need to go through the below example where we have written a vba code by using it:

Sub example_RATE()
Range("A8") = Rate(60, -1200, 0, 98000, 1) * 12
End Sub

In the above code, we have used RATE to calculate the interest rate of the investment where we have 60 months, 1200 of the monthly payment, and 98000 of the future value of the investment and in the end, we have multiplies it with the 12 to get the annual rate of the interest.

Notes

Below are some important points which you need to take care while using RATE function in VBA.

  • While using RATE any cash paid out is represented by a negative number and any cash received is represented by a positive number.
  • It calculates rate iteration. It can’t find the result in 20 tries, it returns a run-time 5.

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.