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

[dmzeigler]

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
Avg = The average value of income over Y 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


The best formula I have found so far to create the initial value is: Avg/(1+I)^(((Y+1)/2)+LN(1+I)). From this initial value, I can multiple it and the amount each year by (1+I) and get the a close approximation to the values. But it is not exact. As the Avg value gets higher or the interest rate gets higher, the income stream deviates from the Avg I am trying to attain.

Does anyone have a better formula?


Thank you,
dmzeigler

 

[joeu2004]

I would call that a 4-year investment. But we can model it as an investment of 5 payments (the first payment is your initial investment to be determined) that total 5*average (5000).

Thus, the initial investment is:

=PMT(4%,5,0,-5000)

If you would like to understand the algebraic formula, see the PV help page.

Remember that in Excel, most financial functions require signed cash flows, with inflows and outflows having opposite signs. The choice of negative for one and positive for the other is arbitrary. I choose them so that the function result is positive.

-----

The derivation of that model is.... You want:

( PV + PV*(1+r) + PV*(1+r)^2 + PV*(1+r)^3 + PV*(1+r)^4 + ... + PV*(1+r)^(n-1) ) / n = avg

where r=4%, n=5 and avg = 1000.

Note that if we substitute "PMT" for "PV", the numerator is the form of an investment with zero initial balance, n payments (TBD), and a future value of n*avg.

 

[dmzeigler]

I never thought to think of it as a pmt, but that makes perfect sense. And gives me exactly what I wanted. Thank you so much!




-------------

I would call that a 4-year investment. But we can model it as an investment of 5 payments (the first payment is your initial investment to be determined) that total 5*average (5000).

Thus, the initial investment is:

=PMT(4%,5,0,-5000)

If you would like to understand the algebraic formula, see the PV help page.

Remember that in Excel, most financial functions require signed cash flows, with inflows and outflows having opposite signs. The choice of negative for one and positive for the other is arbitrary. I choose them so that the function result is positive.

-----

The derivation of that model is.... You want:

( PV + PV*(1+r) + PV*(1+r)^2 + PV*(1+r)^3 + PV*(1+r)^4 + ... + PV*(1+r)^(n-1) ) / n = avg

where r=4%, n=5 and avg = 1000.

Note that if we substitute "PMT" for "PV", the numerator is the form of an investment with zero initial balance, n payments (TBD), and a future value of n*avg.[/QUOTE]

 

[MickG]

This is my take on the problem, perhaps you would like to like to compare it with your data. ???

The Derived equation to return the Initial investment is as follow:-
Initial investment = Total sum of yearly returns*(1.04-1)/1.04^(n+1) - 1.04)

Reduced to :-
Initial Investment = 5000*(1.04-1)/(1.04^(5+1)-1.04) = 887.6304

You can prove this equation by showing how this value 887.6304 Compounded for 5 periods, results in the sum of 5000

887.6304*1.04 +887.6304*1.04^2+887.6304*1.04^3+887.6304*1.04^4+887.6304*1.04^5 =5000

NB:- The value 923.14 that the PMT value shows is the Basic value 887.6304*1.04

Regrds Mick

 

[joeu2004]

Initial Investment = 5000*(1.04-1)/(1.04^(5+1)-1.04) = 887.6304

Which we can calculate more simply using =PMT(4%,5,0,-5000,1).

And I would agree, if dmzeigler had said that 923.14 is the amount after the first year, and the initial investment is not part of the 5-year average.

In other words, your interpretation is a true 5-year investment, and 923.14 is not the initial investment in dmzeigler's initial investment.

Only dmzeigler can say which interpretation is correct.

He never corrected me when I wrote about his original example: "I would call that a 4-year investment", and I made it clear that I thought he intended for 923.14 to be the initial investment in his example.

 

[MickG]

Thank you for your reply
I appreciate you revised PMT function, which indeed gives the Initial investment value.

I hope by vertue of this new information "dmzeigler" wiil get the value that is actually correct for him.
Regrds Mick

 

[joeu2004]

Errata (too late to edit)....

In other words, your interpretation is a true 5-year investment, and 923.14 is not the initial investment in dmzeigler's initial investment.

That got mangled due to editing, and it does not reflect what I was trying to say. It should read....

In other words, your interpretation is a true 5-year investment, if 923.14 is not the initial investment in dmzeigler's original example.

 

[MickG]

That is correct, but my basic formula is designed to return the initial investment required when only the Investment period, the sum of those compounded periods and the interest rate are known.