CAGR.XLS | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ||||||
2 | CAGR | |||||
3 | ||||||
4 | Rate | 5.0% | ||||
5 | 1991 | 1,234 | ||||
6 | 1992 | 1,296 | =C5*(1+C$4) | |||
7 | 1993 | 1,360 | =C6*(1+C$4) | |||
8 | 1994 | 1,429 | =C7*(1+C$4) | |||
9 | 1995 | 1,500 | =C8*(1+C$4) | |||
10 | Years | 4 | =B9-B5 | |||
11 | CAGR | 5.0% | =(C9/C5)^(1/C10)-1 | |||
12 | CAGR | 5.0% | =RATE(C10,,-C5,C9) | |||
Sheet1 |
Function CAGR(InitialValue As Range, EndValue As Range, Years As Tange, Optional TrailingZeroes As Integer = -999)
If TrailingZeroes = -999 Then TrailingZeroes = 1
If TrailingZeroes > 13 Then
MsgBox "Accuracy is limited to 13 digits"
TrailingZeroes = 13
End If
CAGR = FormatPercent((EndValue / InitialValue) ^ (1 / Years) - 1, TrailingZeroes)
End Function