We can use the RRI function in Google Sheets for calculating the compound annual growth rate, aka CAGR, of an investment. So you won’t find any CAGR function in it.
In other words, use the RRI function in Google Sheets to calculate the interest rate needed for an investment to grow from its beginning balance (present value) to its ending balance (future value).
Even though there is a function, you can calculate the compound annual growth rate using the below CAGR formula in Google Sheets.
CAGR = (future_value/present_value^(1/number_of_years))-1
For example, consider the below input values.
Nper = 5 (number of period)
Fv = 15,000 (future value)
Pv = 10,000 (present value)
We can use the following CAGR formula to return the compound annual growth rate of the above investment which will be 8.45%.
=((15000/10000)^(1/5))-1We can calculate the CAGR easily using the Google Sheets RRI function.
But before going to that formula, let me introduce you to the syntax and arguments of this function in Google Sheets.
Syntax:
RRI(number_of_periods, present_value, future_value)Arguments:
number_of_periods (Nper): The number of periods for the investment.
present_value (Pv): The present value of the investment.
future_value (Fv) – The future value of the investment.
Example to the RRI Function in Google Sheets:
See the RRI formula in the formula bar and the output (CAGR) in cell C6, which is in number format.
How to covert the CAGR formula output into percentage? Let’s find it.
How to Format the CAGR Formula Output into Percentage in Google Sheets
There are two ways, and here are them (I prefer the second one, though).
1. Using the Format Menu:
Make sure that the active cell is C6. Then, open the Formant menu, select ‘Number’ and then ‘Percent.’
2. Using a Function:
I prefer the function To_Percent to format the RRI function result from number format to percentage format. Here is how.
=TO_PERCENT(rri(C2,C3,C4))RRI Function in Google Sheets – Errors and Possible Reasons
Before concluding how to use the RRI function in Google Sheets, let me point out some of the errors that may surface when using it.
| #NUM! | 1 | This error surfaces when any provided arguments except Nper are negative numbers. |
| 2 | The same error will occur if Pv is 0. | |
| #DIV/0! | 1 | If you happen to see it, make sure that the Nper is not 0 or blank. |
| #VALUE! | 1 | This error occurs when using string input values. You may note that the RRI function even accepts numbers formatted as text. |
That’s all about the CAGR formula in Google Sheets. Enjoy!
