Solving for a Constant Return to arrive at fixed end value

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

Re: Solving for a Constant Return to arrive at fixed end value

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.

1 Attachment(s)

Re: Solving for a Constant Return to arrive at fixed end value

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?