+ Reply to Thread
Results 1 to 3 of 3

CAGR function with 4 amounts option?

  1. #1
    Registered User
    Join Date
    06-26-2009
    Location
    Slovakia
    MS-Off Ver
    Excel 2010
    Posts
    21

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,769

    Re: CAGR function with 4 amounts option?

    Quote Originally Posted by Efendi View Post
    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.

  3. #3
    Registered User
    Join Date
    06-26-2009
    Location
    Slovakia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Thumbs up 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] HELP - This Macro works on small amounts of data but fails on large amounts
    By BookmanNLA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-18-2015, 12:40 AM
  2. Index function top three amounts next to.
    By wongkey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-22-2014, 01:42 PM
  3. Replies: 2
    Last Post: 02-23-2014, 09:56 PM
  4. [SOLVED] Add Invoice Amounts and Paid Amounts based on user inputs.
    By s2jrchoi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-18-2013, 01:49 PM
  5. sum function for multiple amounts w/various tenors
    By finance14 in forum Excel General
    Replies: 4
    Last Post: 11-10-2011, 06:07 PM
  6. contain amounts:Date Function
    By wcheaib in forum Excel General
    Replies: 1
    Last Post: 09-18-2011, 12:46 AM
  7. How do I calculate CAGR other than using XIRR function?
    By rrigney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-10-2006, 03:29 AM

Tags for this Thread

Bookmarks

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