The formula
Excel & Google SheetsExcelGoogle Sheets
=(B2/B1)^(1/B3)-1In D1 → 12.91%
Using RRIExcelGoogle Sheets
=RRI(B3,B1,B2)In D2 → 12.91%
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start value | 12,000 | 12.91% | |
| 2 | End value | 19,500 | 12.91% | |
| 3 | Years | 4 |
How it works
- B2/B1
- Total growth multiple.
- ^(1/B3)
- The yearly root of that multiple.
- -1
- Turns the multiple into a rate.
- RRI(n,pv,fv)
- Same calculation as a function.
Common errors and fixes
Why does the formula return #NUM!?
Start or end value is negative or zero.
Why does the formula return #DIV/0!?
Years is 0.
Using AVERAGE of yearly returns?
That's the arithmetic mean, which overstates growth when returns vary.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.