The difficulty is that "SpendingYear1", "SpendingYear2", etc. Unless you have some formula for giving that in terms of the Year Value and the specific year, no there is not going to be any simple formula.
Hi I am working on putting together an excel formula that will allow me produce the correct constant return number which will allow me to reach a fixed end value.
I have a fixed spending stream & beginning value and want to figure out what constant return will get me to my prespecified end value.
Beginning Value: $100
Each year do $10 spending growing at 3% per year... so 10, 10.3, 10.609, etc.
I need a formula that will give me the constant return I need to have for the end value to equal $200 in year 20.
Year 0 = 100
Year 1 = Year 0 Value-(SpendingYear1-constant return*Year 0 Value)
Year 2 = Year 1 Value-(SpendingYear2-contstant return*Year 1 Value)
...
Year 20 = 200
The difficulty is that "SpendingYear1", "SpendingYear2", etc. Unless you have some formula for giving that in terms of the Year Value and the specific year, no there is not going to be any simple formula.
I can force it to zero in a certain year by using the IRR of the spending for each year until I want it to run out like in the attachement. but is there a way to instead of forcing it to zero, force it to another value in the last year?