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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top