Your employer is going to invest $75 per month in a 401k for you and promised...

Your employer is going to invest $75 per month in a 401k for you and promised to average a 5% return over the next 20 years. What would I type into Excel to estimate how much money should be in the 401k after 20 years?

Expert Solution

We first compute the equivalent number of months for 20 years here as: 20*12 = 240 months.

Therefore we need to compute the Future value of 240 installments here as:

After this we simply extend the formula for all the rows here and get the FV of each of the installment to get here:

Month	Installment	Future Value
1	75	198.997328
2	75	198.189878
3	75	197.385704
4	75	196.584793
5	75	195.787132
6	75	194.992708
7	75	194.201507
8	75	193.413516
9	75	192.628723
10	75	191.847114
11	75	191.068677
12	75	190.293398
13	75	189.521265
14	75	188.752265
15	75	187.986385
16	75	187.223613
17	75	186.463935
18	75	185.707341
19	75	184.953816
20	75	184.203349
21	75	183.455927
22	75	182.711537
23	75	181.970168
24	75	181.231807
25	75	180.496443
26	75	179.764061
27	75	179.034652
28	75	178.308202
29	75	177.5847
30	75	176.864134
31	75	176.146491
32	75	175.431761
33	75	174.71993
34	75	174.010988
35	75	173.304922
36	75	172.601721
37	75	171.901374
38	75	171.203868
39	75	170.509193
40	75	169.817336
41	75	169.128286
42	75	168.442032
43	75	167.758563
44	75	167.077867
45	75	166.399933
46	75	165.72475
47	75	165.052307
48	75	164.382592
49	75	163.715594
50	75	163.051303
51	75	162.389707
52	75	161.730796
53	75	161.074558
54	75	160.420983
55	75	159.77006
56	75	159.121778
57	75	158.476127
58	75	157.833096
59	75	157.192673
60	75	156.554849
61	75	155.919613
62	75	155.286955
63	75	154.656864
64	75	154.029329
65	75	153.404341
66	75	152.781889
67	75	152.161962
68	75	151.544551
69	75	150.929645
70	75	150.317234
71	75	149.707308
72	75	149.099856
73	75	148.49487
74	75	147.892338
75	75	147.292251
76	75	146.694599
77	75	146.099373
78	75	145.506561
79	75	144.916154
80	75	144.328144
81	75	143.742519
82	75	143.15927
83	75	142.578388
84	75	141.999863
85	75	141.423686
86	75	140.849846
87	75	140.278335
88	75	139.709142
89	75	139.14226
90	75	138.577677
91	75	138.015385
92	75	137.455375
93	75	136.897637
94	75	136.342162
95	75	135.788941
96	75	135.237965
97	75	134.689224
98	75	134.14271
99	75	133.598414
100	75	133.056326
101	75	132.516438
102	75	131.97874
103	75	131.443224
104	75	130.909881
105	75	130.378702
106	75	129.849678
107	75	129.322801
108	75	128.798062
109	75	128.275452
110	75	127.754962
111	75	127.236585
112	75	126.720311
113	75	126.206131
114	75	125.694038
115	75	125.184023
116	75	124.676077
117	75	124.170192
118	75	123.66636
119	75	123.164573
120	75	122.664821
121	75	122.167097
122	75	121.671393
123	75	121.1777
124	75	120.68601
125	75	120.196315
126	75	119.708608
127	75	119.222879
128	75	118.739121
129	75	118.257326
130	75	117.777486
131	75	117.299593
132	75	116.823639
133	75	116.349616
134	75	115.877517
135	75	115.407333
136	75	114.939057
137	75	114.472681
138	75	114.008198
139	75	113.545599
140	75	113.084877
141	75	112.626025
142	75	112.169034
143	75	111.713898
144	75	111.260609
145	75	110.809158
146	75	110.35954
147	75	109.911746
148	75	109.465769
149	75	109.021601
150	75	108.579236
151	75	108.138666
152	75	107.699883
153	75	107.262881
154	75	106.827652
155	75	106.394189
156	75	105.962484
157	75	105.532532
158	75	105.104324
159	75	104.677853
160	75	104.253113
161	75	103.830096
162	75	103.408796
163	75	102.989205
164	75	102.571317
165	75	102.155125
166	75	101.740621
167	75	101.327799
168	75	100.916652
169	75	100.507173
170	75	100.099356
171	75	99.6931935
172	75	99.2886791
173	75	98.8858061
174	75	98.4845678
175	75	98.0849575
176	75	97.6869687
177	75	97.2905948
178	75	96.8958292
179	75	96.5026654
180	75	96.1110969
181	75	95.7211172
182	75	95.3327199
183	75	94.9458985
184	75	94.5606468
185	75	94.1769582
186	75	93.7948264
187	75	93.4142452
188	75	93.0352083
189	75	92.6577093
190	75	92.2817421
191	75	91.9073004
192	75	91.534378
193	75	91.1629688
194	75	90.7930666
195	75	90.4246653
196	75	90.0577588
197	75	89.6923411
198	75	89.3284061
199	75	88.9659479
200	75	88.6049603
201	75	88.2454374
202	75	87.8873734
203	75	87.5307622
204	75	87.1755981
205	75	86.821875
206	75	86.4695872
207	75	86.1187288
208	75	85.7692941
209	75	85.4212773
210	75	85.0746725
211	75	84.7294741
212	75	84.3856765
213	75	84.0432738
214	75	83.7022604
215	75	83.3626307
216	75	83.0243791
217	75	82.6875
218	75	82.3519878
219	75	82.017837
220	75	81.685042
221	75	81.3535974
222	75	81.0234976
223	75	80.6947373
224	75	80.3673109
225	75	80.0412131
226	75	79.7164385
227	75	79.3929816
228	75	79.0708372
229	75	78.75
230	75	78.4304646
231	75	78.1122257
232	75	77.7952781
233	75	77.4796166
234	75	77.1652358
235	75	76.8521307
236	75	76.5402961
237	75	76.2297268
238	75	75.9204176
239	75	75.6123635
240	75	75.3055593

The future value finally is computed by adding the last column here to get:

FV =

30559.3337

Therefore at the end of 20 years, the account would have: $30,559

orchestra answered 3 years ago

(real and nominal rates) You have been depositing $250 per month in your 401k account for...

(real and nominal rates) You have been depositing $250 per month in your 401k account for exactly 10 years. The current value is $65,000. What is the annualized nominal return (effective annual rate) on your investment? If the inflation rate has averaged 6% over that time period, what is your annual real rate of return.

When changing jobs, the preferred way of handling your 401K account balance with your old employer...

When changing jobs, the preferred way of handling your 401K account balance with your old employer is to: a) Convert your account balance with your old employer to cash and use it to pay off your mortgage balance. b) Have the 401k service provider at your new place of employment arrange to have your old account balance placed in an account at your new employer’s 401K plan in what is known as a provider to provider rollover. c) Have your...

A 50 year old employee contributes 20000 per year to her 401k, and her employer matches...

A 50 year old employee contributes 20000 per year to her 401k, and her employer matches her contributions by 40%. The investment company has provided her with two options: Option 1 Option 2 Equity 60% 40% Bond 30% 40% Money Market Fund 10% 20% The employee wants to retire at 65. The average rates of return presented are 5% on equity, 3% on bonds, and 1% on money market funds. Calculate the total amount of money at the time of...

What investments should you have in your 401k? If you don't have a 401k (or 403b),...

What investments should you have in your 401k? If you don't have a 401k (or 403b), what are your options? Everyone has their own unique situation but the goal is the same: satisfying choices in retirement. Share your challenges and solutions.

You save $75 a month for 30 years. If you are able to earn 6%per...

You save $75 a month for 30 years. If you are able to earn 6% per year on your savings, how much will you have accumulated by the end of 30 years?Assume monthly compounding.

Assume that you are going to invest $140,000 in a two asset portfolio. You will invest...

Assume that you are going to invest $140,000 in a two asset portfolio. You will invest $100,000 in the fully diversified market portfolio and the remainder of your funds will be invested in the riskless security. Assume the market risk premium is 8% and the riskless return is 4%. Compute the expected return on this portfolio. Respond in percentage form without the percent sign and round to the second decimal place.

1. If your salary is $ 1,500 per month and you save $ 500 per month,...

1. If your salary is $ 1,500 per month and you save $ 500 per month, your savings rate is 2. How much money should you invest today at 8% interest to get it to grow to $ 15,000 years? 3. What annual interest rate will make $ 500 increase to $ 1,948 in 12 years? 10 years

Now that you are going to save 100 €/month, you decided to save those 100€ per...

Now that you are going to save 100 €/month, you decided to save those 100€ per month in a bank account that offers a 2% interest rate compounded monthly, till the day you retire (that is to say, in 20 years). Please answer the following questions: If you decide to do your deposits at the end of every month (similarly to ex.1, so your first deposit will be in one month from today) in a bank account that offers a...

You are promised to double your investment in 4 years. What rate of interest per year...

You are promised to double your investment in 4 years. What rate of interest per year are you being promised? Group of answer choices 18.92% Need more information 15.78% 24.75%

You are promised to triple your investment in 7 years. What rateof interest per year...

You are promised to triple your investment in 7 years. What rate of interest per year are you being promised?Group of answer choices18.23%Need more information16.99%14.38%

Question