Question

• You are planning to retire when you turn 65.
• You currently have $25,000 in your retirement account.
• Based on actuary tables, you expect to live to be 100 years old.
• During each year of retirement you want to be able to withdraw $50,000 from your retirement account (at the beginning of the year).
• The interest rate is 3%.
• You plan to make deposits into your retirement account on your birthday, each year until you retire.

How much will you need to deposit into your retirement account at the beginning of each year (starting today) until you retire, so that you can withdraw $50,000 each year during retirement, and have an empty bank account at the end of your 100^th year?

Your age	Beginning Balance	Annual deposit	Annual withdrawal	Interest Rate	Required @ Year 64
28	25,000.00	$15,401.10	(50,000.00)	3.00%	1,124,361.00
Your age	Beginning Account Balance	Deposit/Withdrawal	Balance after Deposit/Withdrawal	Interest Earned	Ending Account Balance
23	25,000.00	$15,401.10	40,401.10	1,212.03	41,613.13
24	41,613.13	$15,401.10	57,014.23	1,710.43	58,724.66
25	58,724.66	$15,401.10	74,125.75	2,223.77	76,349.53
26	76,349.53	$15,401.10	91,750.62	2,752.52	94,503.14
27	94,503.14	$15,401.10	109,904.24	3,297.13	113,201.37
28	113,201.37	$15,401.10	128,602.46	3,858.07	132,460.54
29	132,460.54	$15,401.10	147,861.64	4,435.85	152,297.49
30	152,297.49	$15,401.10	167,698.58	5,030.96	172,729.54
31	172,729.54	$15,401.10	188,130.64	5,643.92	193,774.56

An so on until age 100. My balance at year 100 should be zero and its not working. I need excel formulas to compare.

I am using beg balance+deposit

Balance after deposit * interest

Carry balance forward and repeat. My problem comes in when I start paying out my withdraws?

Answer 1

It is assumed that your current age is 23.
Also, you will retire at the age of 65 and will withdraw $50,000 from the age 65 to 100 (both included), that means 36 times.
Total withdraw = 36*50,000 = $18,00,000
Now, from age 23 to 64, you will deposit a fixed amount for retirement, so deposits will be 42 times (Both included).

Now, we need to reverse calculate the amount that should be the closing balance on the end of age 64 to be 0 at the end of age 100.
then, we will find the amount required to be deposited every year which is $12625.04.

Age	Opening Balance	Annual Deposit	Interest Rate	Interest Amt	Withdrawl	Closing Balance
23	25,000	12625.04124	3%	1128.751237	0	38,754
24	38,754	12625.04124	3%	1541.365011	0	52,920
25	52,920	12625.04124	3%	1966.357199	0	67,512
26	67,512	12625.04124	3%	2404.099152	0	82,541
27	82,541	12625.04124	3%	2854.973364	0	98,021
28	98,021	12625.04124	3%	3319.373802	0	1,13,965
29	1,13,965	12625.04124	3%	3797.706253	0	1,30,388
30	1,30,388	12625.04124	3%	4290.388678	0	1,47,303
31	1,47,303	12625.04124	3%	4797.851576	0	1,64,726
32	1,64,726	12625.04124	3%	5320.53836	0	1,82,672
33	1,82,672	12625.04124	3%	5858.905748	0	2,01,156
34	2,01,156	12625.04124	3%	6413.424158	0	2,20,194
35	2,20,194	12625.04124	3%	6984.578119	0	2,39,804
36	2,39,804	12625.04124	3%	7572.8667	0	2,60,002
37	2,60,002	12625.04124	3%	8178.803938	0	2,80,806
38	2,80,806	12625.04124	3%	8802.919294	0	3,02,234
39	3,02,234	12625.04124	3%	9445.75811	0	3,24,304
40	3,24,304	12625.04124	3%	10107.88209	0	3,47,037
41	3,47,037	12625.04124	3%	10789.86979	0	3,70,452
42	3,70,452	12625.04124	3%	11492.31712	0	3,94,570
43	3,94,570	12625.04124	3%	12215.83787	0	4,19,410
44	4,19,410	12625.04124	3%	12961.06425	0	4,44,997
45	4,44,997	12625.04124	3%	13728.64741	0	4,71,350
46	4,71,350	12625.04124	3%	14519.25807	0	4,98,495
47	4,98,495	12625.04124	3%	15333.58705	0	5,26,453
48	5,26,453	12625.04124	3%	16172.3459	0	5,55,251
49	5,55,251	12625.04124	3%	17036.26751	0	5,84,912
50	5,84,912	12625.04124	3%	17926.10677	0	6,15,463
51	6,15,463	12625.04124	3%	18842.64121	0	6,46,931
52	6,46,931	12625.04124	3%	19786.67169	0	6,79,342
53	6,79,342	12625.04124	3%	20759.02308	0	7,12,726
54	7,12,726	12625.04124	3%	21760.545	0	7,47,112
55	7,47,112	12625.04124	3%	22792.11259	0	7,82,529
56	7,82,529	12625.04124	3%	23854.62721	0	8,19,009
57	8,19,009	12625.04124	3%	24949.01726	0	8,56,583
58	8,56,583	12625.04124	3%	26076.23902	0	8,95,284
59	8,95,284	12625.04124	3%	27237.27742	0	9,35,147
60	9,35,147	12625.04124	3%	28433.14698	0	9,76,205
61	9,76,205	12625.04124	3%	29664.89263	0	10,18,495
62	10,18,495	12625.04124	3%	30933.59065	0	10,62,053
63	10,62,053	12625.04124	3%	32240.3496	0	11,06,919
64	11,06,919	12625.04124	3%	33586.31133	0	1091612.62
65	1091612.62	0	3%	32,748.38	50,000	1074361.00
66	1074361.00	0	3%	32,230.83	50,000	1056591.83
67	1056591.83	0	3%	31,697.76	50,000	1038289.59
68	1038289.59	0	3%	31,148.69	50,000	1019438.28
69	1019438.28	0	3%	30,583.15	50,000	1000021.42
70	1000021.42	0	3%	30,000.64	50,000	980022.07
71	980022.07	0	3%	29,400.66	50,000	959422.73
72	959422.73	0	3%	28,782.68	50,000	938205.41
73	938205.41	0	3%	28,146.16	50,000	916351.57
74	916351.57	0	3%	27,490.55	50,000	893842.12
75	893842.12	0	3%	26,815.26	50,000	870657.38
76	870657.38	0	3%	26,119.72	50,000	846777.11
77	846777.11	0	3%	25,403.31	50,000	822180.42
78	822180.42	0	3%	24,665.41	50,000	796845.83
79	796845.83	0	3%	23,905.37	50,000	770751.21
80	770751.21	0	3%	23,122.54	50,000	743873.74
81	743873.74	0	3%	22,316.21	50,000	716189.96
82	716189.96	0	3%	21,485.70	50,000	687675.65
83	687675.65	0	3%	20,630.27	50,000	658305.92
84	658305.92	0	3%	19,749.18	50,000	628055.10
85	628055.10	0	3%	18,841.65	50,000	596896.75
86	596896.75	0	3%	17,906.90	50,000	564803.66
87	564803.66	0	3%	16,944.11	50,000	531747.77
88	531747.77	0	3%	15,952.43	50,000	497700.20
89	497700.20	0	3%	14,931.01	50,000	462631.21
90	462631.21	0	3%	13,878.94	50,000	426510.14
91	426510.14	0	3%	12,795.30	50,000	389305.45
92	389305.45	0	3%	11,679.16	50,000	350984.61
93	350984.61	0	3%	10,529.54	50,000	311514.15
94	311514.15	0	3%	9,345.42	50,000	270859.57
95	270859.57	0	3%	8,125.79	50,000	228985.36
96	228985.36	0	3%	6,869.56	50,000	185854.92
97	185854.92	0	3%	5,575.65	50,000	141430.57
98	141430.57	0	3%	4,242.92	50,000	95673.48
99	95673.48	0	3%	2,870.20	50,000	48,544
100	48,544	0	3%	1,456	50,000	0