Withdrawing from retirement portfolio

Hi gurus,

I need some help here on my retirement (investment) portfolio draw-down spreadsheet. There are 2 phases, 1) accumulation phase and 2) withdrawal phase in a single tab.

There are nine columns, A-I, and I am done with C-E, which is the accumulation phase. I am half completed with column A (year) and column B (age) only because that is dependent on columns F-I, which is the withdrawal phrase I am having challenge with. I have tried IF(ISERROR and NA() but it ends with a negative value, which i wanted it to be zero.

In the spreadsheet:

1) Words highlighted in red are for reference
2) Column H, highlighted in blue to be left blank
3) At column F,G and I you will see "0" because that is not the draw-down phase, and "X" is the intended withdrawal phase. These are for illustration purposes.

Thank you in advance!

Look at this link and post #5 https://www.excelforum.com/excel-gen...-template.html

Alan

In G48 =B7
In I48 =(F48-G48)*1.04
In G49 =G48*1.02 copy down
In I49 =(I48-G49)*1.04 copy down

I believe that you could accomplish what you are asking without using the 'Beginning Portfolio Balance' column (and Ending Portfolio value for that matter, but I left it in).
Rewrite the formula for Beginning Portfolio value to read: =I13
Carry the current formula for E13 all the way down the Ending Portfolio value column.
Withdrawal amount is populated using: =IF(B13<B$6,0,B$7*(1+E$7)^(B13-B$6))
Remaining Portfolio balance is populated using: =E13-SUM(G13:H13)
The last year of withdrawal could be populated using: =AGGREGATE(14,6,B13:B104/(C13:C104>0),1)
Let us know if you have any questions.

To populate the Beginning Portfolio balance column paste the following into cell F13 then double click on the fill handle: =IF(B13>=B$6,C13,0)
Let us know if you have any questions.

"...is it possible to have a flat zero value once column I is depleted, instead of negative values?"
Change the formula in the Withdrawal amount column to read: =IF(B13<B$6,0,MIN(E13,B$7*(1+E$7)^(B13-B$6)))
Change the formula in the Remaining Portfolio balance column to read: =MAX(0,E13-SUM(G13:H13))
As for column F and I, can we link cells within F-I only? Reason being C-E and F-I are two separate phrases.
I assume that you want the investment to continue earing 4% so the accumulation phase continues throughout.
I suppose there's a misunderstanding here.
I believe you made your request plainly. I made some adjustments in an attempt to produce what I consider to be an efficient use of Excel. If you would like to start over using the file attached to post #1 I'll be glad to ask one of the other contributors to take a look.
Let us know if you have any questions.