[[email protected]]

I need to find a "baseline" rate of return calculation that finds the baseline rate of return, given annual deviations from the baseline rate and the CAGR for the entire duration. This could be either a formula or a VBA macro.

Here's an example.

CAGR 10%

Year# Beginning Amount Deviations
from baseline
rate of return
1 1000 5.00%
2 1100 -3.00%
3 1210 0.00%
4 1331 3.40%
5 1464.1 -2.20%
6 1610.51

Baseline rate of return (computed): 9.41%

Thanks,
SS

[[email protected]]

A slight clarification. Column #2 above provides the amounts at the end of each year, if we had assumed a uniform CAGR for the entire duration. They are not used in the computation of the baseline rate of return.

Thanks,
SS

[GS[_6_]]

Please use cross-posting instead of posting separately to multiple
groups. Cross-posting allows a reply from any 1 group to appear in all
groups you cross-post to. This feature exists for your benefit as well
as for the benefit of those trying to help you out because not all of
us follow all groups you might post to.

I will not repeat this reply in the other group you posted to...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[[email protected]]

On Thursday, January 7, 2016 at 5:50:53 PM UTC-6, wrote:
I need to find a "baseline" rate of return calculation that finds the baseline rate of return, given annual deviations from the baseline rate and the CAGR for the entire duration. This could be either a formula or a VBA macro.



The baseline rate of return in this case is 9.41%. Since the deviations from baseline rate are as follows:
Year# Deviations from baseline rate of return
1 5.00%
2 -3.00%
3 0.00%
4 3.40%
5 -2.20%



if the rate annual rate of return year-on-year is as shown below, the net growth will be the same as with a CAGR of 10%:
Year# Rate of return
1 14.41%
2 6.41%
3 9.41%
4 12.81%
5 7.21%

I have figured out the pseudo code for this, using a successive approximation algorithm, and it is given below.

1) Start with some precision requirement (e.g. 0.01%). The successive approximation stops when the result is within this range
2) Start with a VERY SAFE assumption for the baseline rate (e.g. 10 times the CAGR)
3) Keep on halving the baseline rate until the amount for the final year undershoots the amount for the final year, using the CAGR calculation. Let's call this value X.
4) If the result of step 3 is within the precision range, the algorithm stops
5) We pick up the median point between X and 2*X and compare the result for the final year amount with the final year amount with CAGR. Let us call this value Y
6) If the result of step 5 is within the precision rate, the algorithm stops.
7) If the result of step 5 undershoots the amount for the final year, using the CAGR calculation, we repeat step 5 between value Y and 2*X
8) If the result of step 5 overshoots the amount for the final year, using the CAGR calculation, we repeat step 5 between value X and Y
9) These steps are repeated until the within the amount for the final year, using the CAGR calculation and the amount for the final year computed by this algorithm are within the precision range, at which point the algorithm stops.

Thanks,
SS