Compounded Annual Growth Rate

hooknows

New Member
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
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

hooknows

New Member
Damon, thank you. This worked perfectly!

Jimmy

Ekim

Well-known Member
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
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

Replies
1
Views
143
Replies
1
Views
29
Replies
1
Views
148
Replies
2
Views
161
Replies
1
Views
140

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

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.

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

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