Using the Excel RATE Function
SummaryThe 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.
Syntax=RATE (nper, pmt, pv, [fv], [type], [guess])
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.
Optional. The user's guess as to what the rate will be. If omitted, it is assumed to be 10%.
Usage NotesRATE 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.