Question

We will do one more quick retirement account analysis problem to see the impact of: (1) trying to save either a fixed amount each year or a constant percentage of your salary each year and (2) starting your retirement saving immediately or waiting 10 years to really start your retirement savings. Let’s assume that you put a savings deposit into your 401k account at the end of each year by saving money over that previous year period (i.e. so I normally think of them as beginning of year transactions for my cash flow table since I think of the “Period” column in my present value table as “time elapsed between time zero (e.g. today or whenever the cash flow table starts) and when the cash flow will take place.” (e.g. a cash flow in the row labelled “Period 1” occurs at the end of Year 0 or beginning of Year 1, so 1 years time has elapsed since “time 0”) Assume you graduate with your B.S. in Chemical Engineering in Spring semester 2023, take few months off to travel around Europe, and then start work in January 2024 with a starting salary of $70,000. You can also assume where it becomes important that you get an average yearly raise of 2% each year (i.e. so you make $70,000 your first year on the job, $71,400 your second year, etc.). Assume that you are going to retire 40 years later in January 2064. Assume that you are going to live the average of 20 years into retirement, i.e. that you will die in January 2084, and that you want to pay yourself $100,000 per year in retirement income each year, and that both while saving and throughout retirement that your 401k earns 7% in effective interest compounded yearly. Case 1: Using an NPV analysis and assuming that you want to completely expend your retirement savings right when you die (i.e. NPV=0 for the entire series of cash flows then in this case) and that you start saving with your first deposit at the end of 2024, how much would you have to save each year into your retirement account if you wanted to save the exact same amount each year? Case 2: Using an NPV analysis and assuming that you want to completely expend your retirement savings right when you die (i.e. NPV=0 for the entire series of cash flows then in this case) and that you start saving with your first deposit at the end of 2024, how much would you have to save each year on a percentage of your salary basis into your retirement account if you wanted to save the exact same percentage of your salary each year you are working (HINT: Here you may want to add a column to your cash flow table to track your yearly salary as it increase due to raises)? Case 3: Using an NPV analysis and assuming that you want to completely expend your retirement savings right when you die (i.e. NPV=0 for the entire series of cash flows then in this case) and that you have fun in your 20’s and early 30’s and wait to start saving with your first deposit at the end of 2034 (i.e. so now retirement is only 30 years after you start saving), how much would you have to save each year into your retirement account if you wanted to save the exact same amount each year? Write a short discussion of how you feel about your ability to achieve these types of retirement goals and savings and comment on the effect of waiting to start saving for retirement.

Answer 1

The Given data is:

To answer all the questions, create a table for the given date like this:

S.No	Year End	Salary	Savings/(Expenses)	Discounted Value
0	2024	70,000.00
1	2025	71,400.00
2	2026	72,828.00
3	2027	74,284.56
4	2028	75,770.25
5	2029	77,285.66
6	2030	78,831.37
7	2031	80,408.00
8	2032	82,016.16
9	2033	83,656.48
10	2034	85,329.61
11	2035	87,036.20
12	2036	88,776.93
13	2037	90,552.46
14	2038	92,363.51
15	2039	94,210.78
16	2040	96,095.00
17	2041	98,016.90
18	2042	99,977.24
19	2043	101,976.78
20	2044	104,016.32
21	2045	106,096.64
22	2046	108,218.58
23	2047	110,382.95
24	2048	112,590.61
25	2049	114,842.42
26	2050	117,139.27
27	2051	119,482.05
28	2052	121,871.69
29	2053	124,309.13
30	2054	126,795.31
31	2055	129,331.22
32	2056	131,917.84
33	2057	134,556.20
34	2058	137,247.32
35	2059	139,992.27
36	2060	142,792.11
37	2061	145,647.96
38	2062	148,560.92
39	2063	151,532.13
40	2064	154,562.78
41	2065	-	(100,000.00)	(5,832.86)
42	2066	-	(100,000.00)	(5,451.27)
43	2067	-	(100,000.00)	(5,094.64)
44	2068	-	(100,000.00)	(4,761.35)
45	2069	-	(100,000.00)	(4,449.86)
46	2070	-	(100,000.00)	(4,158.75)
47	2071	-	(100,000.00)	(3,886.68)
48	2072	-	(100,000.00)	(3,632.41)
49	2073	-	(100,000.00)	(3,394.78)
50	2074	-	(100,000.00)	(3,172.69)
51	2075	-	(100,000.00)	(2,965.13)
52	2076	-	(100,000.00)	(2,771.15)
53	2077	-	(100,000.00)	(2,589.86)
54	2078	-	(100,000.00)	(2,420.43)
55	2079	-	(100,000.00)	(2,262.08)
56	2080	-	(100,000.00)	(2,114.10)
57	2081	-	(100,000.00)	(1,975.79)
58	2082	-	(100,000.00)	(1,846.53)
59	2083	-	(100,000.00)	(1,725.73)

QUESTION 1:

Fill the Salary column as per given question.

The Discounted Value is given by:

Discounted Value = (Savings or Expense)/(1+rate)^(time)

NPV = Sum of all Discounted Values.

The Goal Seek function is used to determine the constant amount to be saved each year i.e. 4,816.00

QUESTION 2:

Similarly, goal seek is used again to find out the fixed percentage: 5.16%

QUESTION 3:

The fixed amount to be saved if saving starts from 2034 year end is 10,125.69.

QUESTION 4:

In the case of saving a fixed amount each year, the financial burden on yourself is high during the initial years but is softened by the rise in salary over the subsequent years and saving becomes easy in the later years as the amount to be saved is fixed and the money available at your disposal increases,

Saving a percentage of your salary would have equal financial strain throughout the years as the portion of salary available at your disposal is fixed. Hence your spending pattern and saving pattern is fixed throughout your working time.

In case, you are thinking of starting to save late, then the more number of years you delay, the more you have to save from your salary, when you start saving. So it is best to start saving for retirement early.