Compounded Annual Growth Rate

hooknows

New Member
Joined
Feb 2, 2004
Messages
36
Hi,

Is there a formula in Excel that returns the Compounded Annual Growth Rate(CAGR)? For example, I have a column with Yearly $ amounts with 10 years of data. With a financial calculator, if I have the starting $ amount, the ending $ dollar amount and the number of years(or periods), I can calculate the CAGR. Can Excel do something similar with the data?

Thanks!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi hooknows,

I believe the formula is

=((Pf/P0)^(1/n)-1)*m

where P0 is the initial investment amount, Pf is the final (or current) value, n is the total number of periods between P0 and Pf, and m is the number of periods per year. This will give a fractional value that could be formatted as a percent value.

Of course, none of this takes into account additions or subtractions from the principle amount along the way due to additional investments (deposits) or disbursements (withdrawals) fromt he account.

Damon
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
You can also use Excel’s RATE function (useful to cross check the traditional CAGR formula).

So if 1000 increased to 1200 in 5 years:

=(1200/1200)^(1/5)-1
=3.713729%

or:
=RATE(5,0,-1000,1200,0,0)
=3.713729%

Regards,

Mike
 

rjm10

New Member
Joined
Jun 27, 2007
Messages
1
Hi,
Is there a formula that will calculate the number of years it will take to achieve a certain amount if I have the amount today and the CAGR?

For eg I have $700 today, my CAGR is 5% and I want to have $1,500 at some point in time. What would be the formula to calculate how many years will it take?

Cheers
Richard
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,095
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top