is there a function to work out Compound annual growth rate?

[bartoni]

Hi,

is there a function to work out Compound annual growth rate?

Thanks

 

[Damon Ostrander]

Hi Bartoni,

I believe the formula is i = (P/P0)^(1/n) - 1

where n is the number of years over which the growth as occurred, P is the ending principle, P0 is the beginning principle.

Just as an example if P0 = $1000, P = $1500, n = 5 years, the effective annual interest rate would be 8.4%.

Note that n can include partial years.

Keep Excelling.

Damon

 

[bartoni]

Thanks
is there a function that performs this?

 

[Andrew Poulsom]

You can use the RATE function like this:

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

The formula in C11 (like Damon's) is:

=(C9/C5)^(1/C10)-1

The formula in C12 is:

=RATE(C10,,-C5,C9)

 

[sweater_vests_rock]

bartoni.

didn't know about the rate function, but since i wrote this, you might as well have it

ben.

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

book1
	A	B	C	D
1	InitialValue	EndValue	Years	CAGR
2	10000	20000	5	15%
3	10000	20000	5	14.9%
4	10000	20000	5	14.87%
5	10000	20000	5	14.870%
Sheet1