The Excel RRI function uses the following calculation to calculate the interest rate that is equivalent to an investment with present value pv having future value fv after Nper periods:
The Excel RRI function calculates the equivalent interest rate for an investment with specified present value, future value and duration.
Note: the RRI function was introduced in Excel 2013 and so is not available in earlier versions of Excel.
The syntax of the function is:
Where the arguments are as follows:
|nper||-||The number of periods over which the investment is made.|
|pv||-||The present value of the investment.|
|fv||-||The future value of the investment.|
The following spreadsheet shows the Excel RRI function used to calculate the interest rate required for an investment of $10,000, to reach a value of $15,000 over 10 periods.
The above formula returns the value 0.041379744, or 4.14%.
I.e. an interest rate of 4.14% per period would be required for an investment of $10,000 to reach a value of $15,000 over 10 periods.
Note that, in the above spreadsheet, cell A2 has been formatted to show percentages. If your result is showing a decimal value (e.g. 0.041379744), you can format the cell as a percentage in one of the following ways:
The easiest way to change cell formatting is to select the cell(s) to be formatted and then select the percentage button from the 'Number' group on the Home tab of the Excel ribbon (see below):
If you have an older version of Excel, that does not have the ribbon, you can change the formatting of an Excel cell via the following steps:
Further examples of the Excel RRI function are provided on the Microsoft Office website.
If you get an error from the RRI function, this is likely to be one of the following:
|#VALUE!||-||Occurs if any of the supplied arguments are non-numeric.|