Question
In: Statistics and Probability
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?
Solutions
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 2 years agoRelated Solutions
(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...
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%
in your two-stock portfolio, you invest $25 in stock a and $75 in stock B. if...
in your two-stock portfolio, you invest $25 in stock a
and $75 in stock B. if the beta of stock A is 1.31 and the beta of
stock b is 1.39 your portfolio wold be:
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- 1D List Practice Could you write the code to solve the following problem that uses 1D...
- How the concept of moral hazard can be applied to the 2008 financial crisis?
- Write a program that prints and calls two methods. 1. Compute and return the future value...
- Assume you have been selected to lead your team in the development of a quality management...
- java You are creating an array where each element is an (age, name) pair representing guests...
- Joni is a financial planner for ABC firm. She provided all of her clients with the...
- Suppose you plan to retire in 40 years. If you make 10 annual investments of $...
ADVERTISEMENT