# 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  Register To Reply

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

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.  Register To Reply

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  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1