If RATE does not converge, RATE will return a #NUM! error. Try different values for guess between 0 and 1. Guess (optional) - guess is a seed value to use for iteration. Use 0 for payments due at the end of the period (regular annuities) and 1 for payments due at the end of the period (annuities due). Type (optional) - type is a boolean that controls when when payments are due. When fv is omitted, it defaults to zero (0) and pmt must be provided.

This is the cash balance required after all payments have been made.įv (optional) - The future value, or a cash balance required after the last payment is made. Note: If pmt is not provided, the optional fv argument must be supplied. In annuity functions, cash paid out is represented by a negative number. This number cannot change over the life of the annuity. Pmt (required) - The payment made each period. You can enter nper as 5*12 to show how the number was determined. For example, a 5-year car loan with monthly payments has 60 periods. Nper (required) - The total number of payment periods in the annuity.

The RATE function takes six separate arguments, three of which are required as explained below. If the results of RATE do not converge within 20 iterations, RATE returns the #NUM! error value. The RATE function calculates by iteration. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function returns the interest rate per period of an annuity.

Excel iterative function