Need help with a slightly modified Internal Rate of Return calculation

[sandeep6699]

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

 

[joeu2004]

I need [...] a [...] calculation that finds the baseline rate of return, given annual deviations from the baseline rate and the CAGR for the entire duration.

Enter the following data and formulas, leaving B3 empty. Then see the Solver instructions below.

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

	A	B
1	required CAGR	10.00%
2	solved CAGR	10.00000000847840%	B2: {=GEOMEAN(1+B3+A5:A9)-1}
3	solved baseline	9.40377574587676%
4	specified deviations	calculated returns
5	5.00%	14.40377574587680%	B5: =$B$3+A5
6	-3.00%	6.40377574587676%	copy B5 into B6:B9
7	0.00%	9.40377574587676%
8	3.40%	12.80377574587680%
9	-2.20%	7.20377574587676%
10	actual CAGR	10.00000000847840%	B10: {=GEOMEAN(1+B5:B9)-1}

<tbody>
</tbody>

Use Solver as follows to derive the baseline rate of return in B3.

Target cell: B2
Equal to: Value of: 10%
By changing cells: B3

As you can see, Solver derives a different baseline rate of return, which results in an actual CAGR that is closer to goal.

The use of Solver could be put into a macro, if you prefer. That would allow you to specify a cell reference for "Value of" instead of a constant.