Calculating the "initial Investment Value" with CAGR formula and average value

  • I have been trying to rework a common CAGR formula to be able to calculate the initial investment value if all I have if the annualized rate and the average value over the term.


    So I know the standard formula is:


    The formula is: R = (F/I)^(1/Y)-1


    R = annualized rate
    I = initial investment value
    F = final investment value
    Y = no of years


    But what I have is the average value instead of the initial investment value or the final investment value.


    For example, if I know the average over 5 years is $1000, and the annualized rate of return is 4%, what is the formula to compute the initial investment value.


    I know by trial and error the 5 values should be:


    $923.14
    $960.06
    $998.46
    $1038.40
    $1.079.94


    But I can't find the formula for this.


    Thank you,
    dmzeigler

  • Hi and Welcome to the Forum ...:smile:


    If your objective is to get your result ... as quickly as possible ... you can use from the main Menu : data > What-if Analysis > Goal Seek ...


    But ... if you really need to build the formula ... would suggest you attach your file ... for test purposes ...


    generic formula should be : I = F/(1+R)^Y


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you for the response. I started with that formula which works well if you have the end value. But I am trying to start with the average value.


    If I use your formula to go from the average value back to the initial value and use half the timeframe, then I get these values:
    $924.56
    $961.54
    $1000.00
    $1040.00
    $1081.60


    Average: $1,001.54


    So it is close, but basically you are not getting the right compounding for the second half of the period figured into the initial value. Obviously, I get the right middle value.


    There has to be a formula to compute the initial value given the average value instead of the end value.


    Thanks,
    dmzeigler

  • Glad to hear the formula works fine ...


    As I said ... for building a customized model ...please attach your file with your next message ..:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Admittedly, I've never used Goal Seek a lot and what you created is pretty cool, what I am really after is a formula to produce this. I would like to be able to use this in some much larger applications, and I am sure there has to be a relatively simple formula for it, but I haven't been able to find it. They all rely on having either the initial value or the end value first. And I want to be able to compute this from the average value.


    Any idea of what the formula is? Or where I could find it?

  • I think that will work. It's interesting, I noticed that the higher the interest rate, the farther from the average it will get, but it is off by a relatively small percentage. SoI think that will serve my purpose.


    I'm surprised there isn't a specific formula that will give you an exact number. Really appreciate the help though. Excellent job.

  • Thanks for your Thanks ... AND for the Like ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim -


    I think I found a different approach that gives the exact answer I was looking for.


    If I use PMT ( R, Y, 0, - (Y*AvgVal) ), it gives me the exact first year amount that I can then increase by the (1+R). i never thought to think of it as a simple PMT function.


    But I really appreciate all of your help on this. I learned a lot on the Goal Seek function if nothing else.


    Dmzeigler

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!