Question

Today is YOUR birthday. Use a base age of 23 plus the month of your actual birthdate (e.g. January is 1, June is 6, December is 12) to calculate your “age” for this project. Your age is: 28

• 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		50,000.00	3.00%
Your age	Beginning Account Balance	Deposit/Withdrawal	Balance after Deposit/Withdrawal	Interest Earned	Ending Account Balance
28	25,000.00
29	-
30	-
31	-
32	-
33	-
34	-
					An so on until age 100...…..

Answer 1

Current age	28	years
Retirement age	65	years
Life expectancy	100	years
Time to retire	37	years
Life after retirement	36	years
Interest rate	3%
Yearly withdrawal in retirement	$                     50,000	Fixed amount

This can be solved very easily using Time Value of Money functions. I will solve using both- excel functions and long method

Using Excel functions
Amount required at beginning of 65th birthday	=PV(3%,(100-65+1),-50000,0,1)
	$1,124,361.00
Amount to be deposited every birthday	=PMT(3%,(64-28+1),25000,-1124361,1)
	$15,401.10

Using Excel tables

Retirement withdrawals
Age	Starting balance	Amount withdrawn	Ending balance
100	$50,000.00	$50,000.00	$0.00
99	$98,543.69	$50,000.00	$48,543.69
98	$145,673.48	$50,000.00	$95,673.48
97	$191,430.57	$50,000.00	$141,430.57
96	$235,854.92	$50,000.00	$185,854.92
95	$278,985.36	$50,000.00	$228,985.36
94	$320,859.57	$50,000.00	$270,859.57
93	$361,514.15	$50,000.00	$311,514.15
92	$400,984.61	$50,000.00	$350,984.61
91	$439,305.45	$50,000.00	$389,305.45
90	$476,510.14	$50,000.00	$426,510.14
89	$512,631.21	$50,000.00	$462,631.21
88	$547,700.20	$50,000.00	$497,700.20
87	$581,747.77	$50,000.00	$531,747.77
86	$614,803.66	$50,000.00	$564,803.66
85	$646,896.75	$50,000.00	$596,896.75
84	$678,055.10	$50,000.00	$628,055.10
83	$708,305.92	$50,000.00	$658,305.92
82	$737,675.65	$50,000.00	$687,675.65
81	$766,189.96	$50,000.00	$716,189.96
80	$793,873.74	$50,000.00	$743,873.74
79	$820,751.21	$50,000.00	$770,751.21
78	$846,845.83	$50,000.00	$796,845.83
77	$872,180.42	$50,000.00	$822,180.42
76	$896,777.11	$50,000.00	$846,777.11
75	$920,657.38	$50,000.00	$870,657.38
74	$943,842.12	$50,000.00	$893,842.12
73	$966,351.57	$50,000.00	$916,351.57
72	$988,205.41	$50,000.00	$938,205.41
71	$1,009,422.73	$50,000.00	$959,422.73
70	$1,030,022.07	$50,000.00	$980,022.07
69	$1,050,021.42	$50,000.00	$1,000,021.42
68	$1,069,438.28	$50,000.00	$1,019,438.28
67	$1,088,289.59	$50,000.00	$1,038,289.59
66	$1,106,591.83	$50,000.00	$1,056,591.83
65	$1,124,361.00	$50,000.00	$1,074,361.00

Sample formula linkages-

As you can see, the beginning balance at the of 65 years is the same i.e. $1,124,361

Earning contributions	- by trial an error
Age	Starting balance	Amount Invested	Ending balance
64	$          1,076,211.53	$               15,401.10	$       1,124,361.00
63	$          1,029,464.46	$               15,401.10	$       1,076,211.53
62	$             984,078.96	$               15,401.10	$       1,029,464.46
61	$             940,015.37	$               15,401.10	$          984,078.96
60	$             897,235.19	$               15,401.10	$          940,015.37
59	$             855,701.02	$               15,401.10	$          897,235.19
58	$             815,376.60	$               15,401.10	$          855,701.02
57	$             776,226.67	$               15,401.10	$          815,376.60
56	$             738,217.02	$               15,401.10	$          776,226.67
55	$             701,314.46	$               15,401.10	$          738,217.02
54	$             665,486.73	$               15,401.10	$          701,314.46
53	$             630,702.52	$               15,401.10	$          665,486.73
52	$             596,931.45	$               15,401.10	$          630,702.52
51	$             564,144.00	$               15,401.10	$          596,931.45
50	$             532,311.52	$               15,401.10	$          564,144.00
49	$             501,406.20	$               15,401.10	$          532,311.52
48	$             471,401.04	$               15,401.10	$          501,406.20
47	$             442,269.82	$               15,401.10	$          471,401.04
46	$             413,987.07	$               15,401.10	$          442,269.82
45	$             386,528.10	$               15,401.10	$          413,987.07
44	$             359,868.90	$               15,401.10	$          386,528.10
43	$             333,986.19	$               15,401.10	$          359,868.90
42	$             308,857.34	$               15,401.10	$          333,986.19
41	$             284,460.39	$               15,401.10	$          308,857.34
40	$             260,774.04	$               15,401.10	$          284,460.39
39	$             237,777.58	$               15,401.10	$          260,774.04
38	$             215,450.93	$               15,401.10	$          237,777.58
37	$             193,774.56	$               15,401.10	$          215,450.93
36	$             172,729.54	$               15,401.10	$          193,774.56
35	$             152,297.49	$               15,401.10	$          172,729.54
34	$             132,460.54	$               15,401.10	$          152,297.49
33	$             113,201.37	$               15,401.10	$          132,460.54
32	$               94,503.14	$               15,401.10	$          113,201.37
31	$               76,349.53	$               15,401.10	$            94,503.14
30	$               58,724.66	$               15,401.10	$            76,349.53
29	$               41,613.13	$               15,401.10	$            58,724.66
28	$               25,000.00	$               15,401.10	$            41,613.13

Sample formula linkages-