Excel RATE Function

Using the Excel RATE Function


The RATE function is one of Excel's financial functions and returns the interest rate per period of an investment or loan. RATE can have zero or more solutions and is calculated by iteration. The function returns the #NUM! error if the successive results do not converge within 0.0000001 after 20 iterations.


=RATE (nper, pmt, pv, [fv], [type], [guess])

Syntax Breakdown

Required. Total number of payments for the loan or investment.

Required. The payment made each period. This value cannot change over the life of the loan or investment and only includes principal and interest. The pmt argument does not include taxes and fees that may be associated with a loan.

Required. The present value that a series of future payments is worth today. PV should be entered as a negative number.

Optional. The future value desired after the last payment is made. If omitted, FV is assumed to be zero (0).

Optional. Argument indicating when payments are due. Use 1 for beginning of the period and zero (0) for end of the period.

Excel RATE Type Example

Optional. The user's guess as to what the rate will be. If omitted, it is assumed to be 10%.

Usage Notes

RATE is one of Excel's financial functions that returns the interest rate per period for an annuity. RATE will calculate periodic interest, and then the user can multiply as needed to determine the annual rate.

Nper and Guess Units
It is critical that you are consistent with the units used when entering values for nper and guess. Some conversion (i.e. from annual to monthly) may be required.

For example, if you are making monthly payments on a 2-year loan at 5% annual interest, use 5%/12 for guess and 2*12 for nper. If you are making annual payments, then you would use 5% for guess and 2 for nper.

Failing to ensure the units are the same will produce incorrect results.