CAGR

[satishrana]

Hi. Is there any simple formula in excel for calculating Compounded Annual Growth Rate (CAGR)?

 

[sudeepmirza]

How does the abovementioned CAGR formula work in case where the first value is negative?

like, i am trying to find a CAGR of profit for say 5 years with values <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 width=64 align=right x:num="-10000"><FORM id=aspnetForm method=post name=aspnetForm action=http://office.microsoft.com/en-us/excel-help/calculate-a-compound-annual-growth-rate-cagr-HP001122506.aspx>-10,000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 width=64 align=right x:num="2750">2,750</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 width=64 align=right x:num="4250">4,250</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 width=64 align=right x:num="3250">3,250</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 width=64 align=right x:num="2750">2,750</TD></TR></TBODY></TABLE></FORM>

how will the function perform?

 

[deepspacedave]

Shifting a little from the last couple posts, this formula works great, I love it, but I want to add to it. I track our retirement and it works great to get the average of how much our accounts grow on average per year. BUT, I want to know how much, without any contributions, they grew as well.

So the base formula just to restate is

CAGR: =((End Value/Start Value)^(1/(Periods - 1)) -1

I modified it to be dynamic ans exclude the current year (as it is not a complete year) so my formula looks like this 9Used a -2 vs a -1 at the end to exclude the current year):

=(INDEX(D241:D269,(COUNT(D241:D269)-1))/INDEX(D241:D269,((COUNT(D241:D269))-(COUNT(D241:D269)-1))))^(1/(COUNT(D241:D269)-2))-1


My initial thought was to change This
CAGR: =((End Value/Start Value)^(1/(Periods - 1)) -1

to

CAGR: =(((End Value-SUM(contributions))/Start Value)^(1/(Periods - 1)) -1

But the number does not come our correctly. Any thoughts? Thanks!

 

[mbeal]

It certainly does, thank you!

 

[ExcelIsEasy]

Here you have free tutorial and free spreadsheet to download: how to calculate CAGR in Excel tutorial

 

[lmbeharry]

Hi - is there a formula that properly calculates CAGR when you end up with a negative number (e.g.) over a 5-year period you go from making $100 in Year 1 to losing $50 in year 5 - what is the -CAGR in this calculation)?

Thanks in advance for any help that can be offered.

Try this:

(IF(AND((((TerminalValue/InitialValue)^(1/(Period))-1)<0),TerminalValue>InitialValue),IRR(InitialValue:TerminalValue,10%),+(TerminalValue/InitialValue)^(1/(Period))-1)

 

[lmbeharry]

A simple algorithm to grow an initial negative value to a terminal value by a defined number over the course of n-periods. An example. Fields: [B1], [B2], [B3] and [B5] are variable fields. Use Goal Seek to isolate [B5] by setting [B4] to 0.000

Initial Value [A1]: -105.000 [B1]
Terminal Value [A2]: 147.000 [B2]
Periods [A3]: 5 [B3]
Use Goal Seek to change the Rate to set this calculation to 0 [A4]: =($B$1-$B$3*($B$1*+$B$5))-$B$2 [B4]
Rate [A5]: 48.000% [B5]


In other words, after five periods by growing -105.000 by +48.000%, you would gain the result 147.000 . It seems that the logic works to impute the CAGR over the items in a series.

 

[lmbeharry]

A simple algorithm to grow an initial negative value to a terminal value by a defined number over the course of n-periods. An example. Fields: [B1], [B2], [B3] and [B5] are variable fields. Use Goal Seek to isolate [B5] by setting [B4] to 0.000

Initial Value [A1]: -105.000 [B1]
Terminal Value [A2]: 147.000 [B2]
Periods [A3]: 5 [B3]
Use Goal Seek to change the Rate to set this calculation to 0 [A4]: =($B$1-$B$3*($B$1*+$B$5))-$B$2 [B4]
Rate [A5]: 48.000% [B5]


In other words, after five periods by growing -105.000 by +48.000%, you would gain the result 147.000 . It seems that the logic works to impute the CAGR over the items in a series.

Following the last post, this "If Statement" might suit the need for contingencies in coding for an initial negative number, versus initial positive number - insert in cell [B4] (when laying out CAGR in columnar series):
=IF(B1<0,($B$1-$B$3*($B$1*+$B$5))-$B$2,($B$1*(1+$B$5)^$B$3)-$B$2)