Exceljet

Quick, clean, and to the point

Excel RATE Function

Excel RATE function
Summary 
The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration.
Purpose 
Get the interest rate per period of an annuity
Return value 
the interest rate per period
Syntax 
=RATE (nper, pmt, pv, [fv], [type], [guess])
Arguments 
  • nper - The total number of payment periods.
  • pmt - The payment made each period.
  • pv - The present value, or total value of all loan payments now.
  • fv - [optional] The future value, or desired cash balance after last payment. Default is 0.
  • type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.
  • guess - [optional] Your guess on the rate. Default is 10%.
Usage notes 

RATE is calculated by iteration. If the results of RATE do not converge within 20 iterations, RATE returns the #NUM! error value.

Notes:

You must be consistent with units for guess and nper. If you make monthly payments on a five-year loan at 10 percent annual interest, use 10%/12 for guess and 5*12 for nper. If you make annual payments on the same loan, use 10% for guess and 5 for nper.