Calculate the interest rate per period charged on a loan or the rate of return needed to reach a specified amount on an investment over a given period.


Annuity_Rate(nper, pmt, pv, [fv], [type], [guess])


nperYesdoubleTotal number of payment periods in the annuity
pmtYesdoublePayment to be made each period
pvYesdoublePresent value, of a series of future payments or receipts
fvNodoubleFuture value you want after you make the final payment (Defaults 0)
typeNointIndicates when payments are due. Use 0 for payments due at the end of the payment period. Or 1 due at the beginning of the period (Defaults 0)
guessNodoubleValue you estimate will be returned by the function (Defaults 0.1)

Example 1

Annuity_Rate(60, -100, 5000) Returns 0.006183

Example 2

You are taking out a loan of $5,000. The loan term is 5 years and payments are made monthly (60 payments). Loan payments are $100 per month. What would the yearly interest rate be for this loan?

Annuity_Rate(60, -100, 5000) * 100 * 12 Returns (7.42% yearly)

The function returns the monthly rate so we multiply by 100 to percentage & 12 to get yearly.