How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.

[OaklandJim]

Team Mr. Excel

I have an investments portfolio worksheet (Excel) that has various asset-specific criteria. A simplified version/example of the worksheet is shown below.

Portfolio_Ver1o.xlsm
	A	B	C	D	E	F	G	H	I
3					Gains
4		Years Held	Current Value	Cost Basis	$ Total	% Total	Annual Average	%/Year
5	Asset 1	2	5,000.0	3,000.0	2,000.0	66.7%	1,000.0	33.3%
6	Asset 2	7	6,000.0	4,000.0	2,000.0	50.0%	285.7	7.1%
7	Asset 3	11	14,000.0	10,000.0	4,000.0	40.0%	363.6	3.6%
8		Total	25,000.0	17,000.0	8,000.0	20.6%	1,649.4	??
9
10			Portfolio Total Gain		8,000/17,000 =	47.1%	$ Total Gain / $ Cost
11
12			"Composite" Years Held		8,000/1,649 =	4.9	$ Total Cost / $ Annual Gain
13
14			Portfolio Total Gain		1,649/8,000 =	20.6%	$ Annual Gain / $ Cost
15
16			Portfolio Annual Average Gain ?		47.1%/4.9 =	9.7%	$ Total Gain / Years
Sheet1

Cell Formulas
Range	Formula
E5:E7	E5	=C5-D5
F5:F7	F5	=E5/D5
G5:G7	G5	=E5/B5
H5:H7	H5	=F5/B5
C8:E8,G8	C8	=SUM(C5:C7)
F8	F8	=G8/E8
E10	E10	=TEXT(E8,"#,##0") & "/" & TEXT(D8,"#,##0") & " = "
F10	F10	=E8/D8
E12	E12	=TEXT(E8,"#,##0") & "/" & TEXT(G8,"#,##0") & " = "
F12	F12	=E8/G8
E14	E14	=TEXT(G8,"#,##0") & "/" & TEXT(E8,"#,##0") & " = "
F14	F14	=G8/E8
E16	E16	=TEXT(F10,"0.0%") & "/" & TEXT(F12,"#,##0.0") & " = "
F16	F16	=F10/F12

Here is what I need help with (this is a math question more than spreadsheet...). I want to calculate the annual average GROWTH rate (price appreciation only, no reinvestment of dividends) for the portfolio as a whole.

As shown in the example, I have asset-specific criteria that include: 1. years held, 2. current value, 3. cost basis, 4. $ total gain, 5. % total gain, 6. $ annual average gain and 7. % annual average gain.

I hope that someone can either tell me a) how to calculate the annual average growth for the portfolio as a whole or b) whether the approach shown yields the correct value.

I really appreciate your assistance.

Jim

 

[alansidman]

If I am understanding your need, then you should be using the Compound Annual Growth Rate often referred as CAGR

=rate(Number or years held +1,,-Cost, Current Value)

 

[alansidman]

Here is another means to the same CAGR

'=(EndValue/StartValue)^(1/yearsHeld)-1

 

[kvsrinivasamurthy]

Try.

=SUM(G5:G8)/Sum(D5:D8)