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

dmzeigler

New Member
Joined
Nov 21, 2018
Messages
2
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.
 
Last edited:
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top