Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a slightly modified Internal Rate of Return calculation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a slightly modified Internal Rate of Return calculation
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a slightly modified Internal Rate of Return calculation
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a slightly modified Internal Rate of Return calculation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Internal Rate of Return (IRR) | Excel Worksheet Functions | |||
compound rate of return calculation | Excel Worksheet Functions | |||
RATE - what is the internal function? | Excel Worksheet Functions | |||
delete row code to be modified very slightly?? | Excel Programming | |||
help needed for Internal rate of return | Excel Worksheet Functions |