sandeep6699
New Member
- Joined
- Jan 11, 2016
- Messages
- 1
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
1) 1000
2) 1100
3) 1210
4) 1331
5) 1464.1
6) 1610.51
Deviations from baseline rate:
Year#) Deviations from baseline rate of return
1) 5.00%
2) -3.00%
3) 0.00%
4) 3.40%
5) -2.20%
Baseline rate of return (computed): 9.41%
If the rate annual rate of return year-on-year is as follows, 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 [COLOR=blue !important][FONT=inherit !important][COLOR=blue !important][FONT=inherit !important]us [/FONT][COLOR=blue !important][FONT=inherit !important]call[/FONT][/COLOR][/FONT][/COLOR][/COLOR] 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
Here's an example.
CAGR 10%
Year#) Beginning Amount
1) 1000
2) 1100
3) 1210
4) 1331
5) 1464.1
6) 1610.51
Deviations from baseline rate:
Year#) Deviations from baseline rate of return
1) 5.00%
2) -3.00%
3) 0.00%
4) 3.40%
5) -2.20%
Baseline rate of return (computed): 9.41%
If the rate annual rate of return year-on-year is as follows, 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 [COLOR=blue !important][FONT=inherit !important][COLOR=blue !important][FONT=inherit !important]us [/FONT][COLOR=blue !important][FONT=inherit !important]call[/FONT][/COLOR][/FONT][/COLOR][/COLOR] 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