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



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

 

[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