# CAGR function with 4 amounts option?

1. ## CAGR function with 4 amounts option?

Hello there,

I would like to use a CAGR function in Excel. I found on internet a spreadsheet with example, but with begginning / ending periods options only.
Would someone advice how to write a formula for 4 amounts (4 periods of time) as is in attached file?
Thank you very much for your help.

Regards,

Marian

2. ## Re: CAGR function with 4 amounts option?

Originally Posted by Efendi
I would like to use a CAGR function in Excel. I found on internet a spreadsheet with example, but with begginning / ending periods options only. Would someone advice how to write a formula for 4 amounts (4 periods of time) as is in attached file?
It really is no different than the formula you have in C3, although I would write it as:

=(C7/C5)^(365/(C8-C6)) - 1

For the example with 4 amounts, the values in between do not matter. The formula in F11 is (formula #1):

=(F8/F5)^(365/(G8-G5)) - 1

which is about 0.47834892%. As a double-check, note that:

=F5*(1+0.47834892%)^((G8-G5)/365)

is about 1.579809524, the approximate value in F8.

Alternatively, you might want to treat every year as the same duration (ignoring leap years). In that case, the CAGR can be calculated more simply by (formula #2):

=(F8/F5)^(1/3) - 1

As a double-check, temporarily enter =G5+1 into G6 and copy down through G8. (The dates become Dec 31 instead of Jan 1 because 2012 is a leap year.)

Note that the result of formula #2 is about the same as the result of formula #1.

Both formulas are "correct". It depends on your assumptions and what accuracy you require.

3. ## Re: CAGR function with 4 amounts option?

Joeu, thank you very much for explaining both type of formulas, now all is clear and works in my Excel!

Regards,

Marian

There are currently 1 users browsing this thread. (0 members and 1 guests)