Annuity draw-down over 25 years period

[Johnson1992]

Hi gurus,

I will be very grateful if any one of you can assist on this. One of our team members who created the below has left the company and when we recover the file, we realised the formulas are totally gone! And none of us are able to work out the formula.

Appreciate if any kind soul can help us. Thank you in advance!!!

Link from other posted forum: https://www.excelforum.com/excel-general/1286591-annuity-draw-down-over-25-years-period.html

Account A balance 243364 Account B balance 563535 Current age 55 First withdrawal age 65 Annual increase in withdrawal 3% Final withdrawal age 90 Age Beginning balance of account A Withdrawal from account A (beginning period) [1] Interest credited at the end of period for Account A Begin balance of Account B Withdrawal from Account B (beginning period) [2] Interest credited at the end of period for Account B Total withdrawal = [1] + [2] 55 243364 0 9734.56 563535 0 14088.38 0 56 253098.56 0 10123.94 577623.38 0 14440.58 0 57 263222.5 0 10528.9 592063.96 0 14801.6 0 58 273751.4 0 10950.06 606865.56 0 15171.64 0 59 284701.46 0 11388.06 622037.2 0 15550.93 0 60 296089.52 0 11843.58 637588.13 0 15939.7 0 61 307933.1 0 12317.32 653527.83 0 16338.2 0 62 320250.42 0 12810.02 669866.03 0 16746.65 0 63 333060.44 0 13322.42 686612.68 0 17165.32 0 64 346382.86 0 13855.31 703777.99 0 17594.45 0 65 360238.17 -39937.19 12812.04 721372.44 0 18034.31 39937.19 66 333113.02 -41135.31 11679.11 739406.75 0 18485.17 41135.31 67 303656.82 -42369.37 10451.5 757891.92 0 18947.3 42369.37 68 271738.95 -43640.45 9123.94 776839.22 0 19420.98 43640.45 69 237222.45 -44949.66 7690.91 796260.2 0 19906.51 44949.66 70 199963.7 -46298.15 6146.62 816166.71 0 20404.17 46298.15 71 159812.17 -47687.09 4485 836570.87 0 20914.27 47687.09 72 116610.08 -49117.71 2699.69 857485.15 0 21437.13 49117.71 73 70192.06 -50591.24 784.03 878922.28 0 21973.06 50591.24 74 20384.86 -20384.86 0 900895.33 -31724.12 21729.28 52108.98 75 0 0 0 890900.5 -53672.25 20930.71 53672.25 76 0 0 0 858158.96 -55282.41 20071.91 55282.41 77 0 0 0 822948.46 -56940.88 19150.19 56940.88 78 0 0 0 785157.76 -58649.11 18162.72 58649.11 79 0 0 0 744671.37 -60408.58 17106.57 60408.58 80 0 0 0 701369.35 -62220.84 15978.71 62220.84 81 0 0 0 655127.22 -64087.47 14775.99 64087.47 82 0 0 0 605815.75 -66010.09 13495.14 66010.09 83 0 0 0 553300.8 -67990.39 12132.76 67990.39 84 0 0 0 497443.17 -70030.11 10685.33 70030.11 85 0 0 0 438098.39 -72131.01 9149.18 72131.01 86 0 0 0 375116.56 -74294.94 7520.54 74294.94 87 0 0 0 308342.16 -76523.79 5795.46 76523.79 88 0 0 0 237613.84 -78819.5 3969.86 78819.5 89 0 0 0 162764.19 -81184.09 2039.5 81184.09 90 0 0 0 83619.61 -83619.61 0 83619.61 <tbody> </tbody>

<tbody>
</tbody>

 

[mrshl9898]

What parts of this aren't formulas?

Are the balances fixed values?

How do you know the first withdraw amount?

How does the interest work?

 

[mrshl9898]

With the table in A8:H44. Headers in Row 8.

B9 = =B1
B10 = =ROUND(B9+D9-C9,2) dragged down
C9 = =IF(B9-H9>0,H9,IF(B9-H9<=0,B9,0)) dragged down

E9 = =B2
E10 = =ROUND(E9+G9-F9,2) dragged down
F10 = =IF(B9=C9,H9-C9,0) dragged down

H10 = =IF(IF(SUM(I$9:I9)=1,$E$1,IF(SUM(I$9:I9)>1,H8*(1+$B$5),0))>SUM(E9,B9),SUM(E9,B9),IF(SUM(I$9:I9)=1,$E$1,IF(SUM(I$9:I9)>1,H8*(1+$B$5),0))) dragged down

Then added a column:
I10 = =IF(AND(A9<=$B$6,A9>=$B$4),1,0) dragged down

And added the withdraw amount 39937.19 to E1.

Interest looks variable, so no idea what to do there