Excel RRI Function
RRI is an Excel function that calculates the periodic equivalent interest on a loan or investment over a period given its present value, future value and total number of periods.
RRI return a rate for the same time unit in which we specify the number of periods. For example, if number of periods are expressed in quarters, RRI returns the quarterly equivalent interest rate.
Let’s say you invested $100,000 with a bank, the interest rate for the first three years were 5%, 6% and 4% respectively. Your investment value at the end of third year would equal $115,752 (=$100,000 × (1 + 5%) × (1 + 6%) × (1 + 4%)).
You might be interested in finding out a single rate that when applied to $100,000 gives you $115,752. RRI calculates exactly that. The algebra behind RRI calculation is as follows:
$$ \text{RRI}=\left(\frac{\text{FV}}{\text{PV}}\right)^\frac{\text{1}}{\text{NPER}}-\text{1} $$
Syntax
RRI syntax is:
RRI(nper, pv, fv)
Where nper is the total number of periods, pv stands for present value and fv stands for future value.
There is no periodic cash flow.
In the above example, RRI equals 5% as illustrated below.
The same value can be obtained using the formula above:
$$ \text{RRI}=\left(\frac{\text{115,752}}{\text{100,000}}\right)^\frac{\text{1}}{\text{3}}-\text{1} $$
The rate obtained above is for the same time unit in which we enter NPER.
Where the period length is a year, RRI function can be used to calculate the compound annual growth rate (CAGR) which is a useful measure.
by Obaidullah Jan, ACA, CFA and last modified on