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

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

which is about 0.47878682%.

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.

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

Regards,

Marian