The RRI function calculates the equivalent interest rate for an investment with specified present value, future value and duration.

  • This function was introduced in Excel 2013 and so is not available in earlier versions.

Syntax

=RRI(nper,pv,fv)

Arguments

Argument Description
nper The number of periods for the investment
pv The present value of the investment
fv The future value of the investment

Examples

  A B C D
1 Data      
2 5 Investment term – years    
3 $10,000 Prevent value    
4 $20,000 Future value    
5        
6 Formula Result Notes
7 =RRI(A2*12,A3,A4) 0.0116% Returns an equivalent monthly interest rate for the growth of an investment with the terms above
8 =RRI(A2*12,A3,A4)*12 14.87% Returns an equivalent annual interest rate for the growth of an investment with the terms above (0.0116% x 12)

Note: In the example, the number of years had to be converted into months (=5*12)

Note: In the above spreadsheet, cells C7 and C8 have been formatted to show percentages. If your result in C7 is showing a decimal value, e.g. 0.01161944, you need to format the cell as a percentage.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
#NUM! Occurs if either:

  the nper or the pv argument is zero
  any of the supplied arguments are negative
Result is presented as a decimal or 0% Likely to be due to the formatting of the cell containing the function and can be fixed by formatting the cell as a percentage, with decimal places

The Excel RRI function uses the following calculation to calculate the interest rate that is equivalent to an investment:  

    \[ \frac{fv^ \frac{1}{nper}}{pv} - 1  \]