Part 1 Scenario:A13:C48
Today is your 21st birthday, you just started a new job and you are planning to save for
retirement. You plan to save a percent of your salary each year through age 64, quit your
job on your 65th birthday, then begin withdrawing that day and each year thereafter. Your
salary is expected to increase each year by the rates in column D on the Starter sheet
so the amount saved each year will be growing. Inflation will impact the rate you earn as
well as purchasing power so you will need to express some of the future amounts in real
(or today's) dollars. Once you retire the amount you withdraw each year needs to be able
to purchase the same amount as $90,000 would today. Assume all cash flows are at
year end. Ignore taxes. To simplify assume that your salary is paid annually in a lump
sum after working for one year.
Of course if you knew the rate you would earn each year on your account and the age at
which you would die you could easily calculate how much to save for any size
withdrawal. But in reality you do not know these annually compounded rates and must
estimate them. You may assume each year's rate is normally distributed with the
parameters listed on the Starter sheet and independent of (not dependent on) any other
year's rate. Also assume that the rates given are annually compounded rates.
Modify the Starter sheet to simulate 10,000 times the age to which you will be able to
continue the withdrawals, understanding that you may eventually run out of money! The
Starter sheet shows ages XXXXXXXXXXby which time you will likely be long gone from this
world. Cells with "XXXXX" must be replaced with formulas. Use empty cells on the
starter sheet in column L to help impute the approximate age (years and fraction of year)
you run out of money (ex XXXXXXXXXXAssume that this age cannot be larger than 121.
L111 should be replaced with a formula to show what year (and fraction) your money ran
out. Note that L111 should be less than or equal to 121. You will need to create a way to
determine to what age your money will last. Assume that your balance earns a rate of
return only if it is invested for the entire year. If there is only enough money for a partial
withdrawal in the last year that your money runs out then adjust age proportionately, i.e.
half a payment lasts half a year. Hint: Use cells in L53:L110 for intermediate calculations
using an IF function.
Read the section below describing the Fisher equation to help with inflation calculations. If
you use the Fisher equation be sure to use the exact formula and not the approximation.
Double check all of your calculations. One way to do this would be to use different formulas
in another workbook along with some common sense about what is reasonable.
Build the simulation table at the bottom of the Starter sheet. After completing the
simulation, show (in the purple fill range at the top right of the Starter sheet) a formula to
calculate the average, minimum and maximum age at which your money ran out based
on the 10,000 results. Show in O2:O5 the 5, 10, 50 and 75 percentile ages using the
PERCENTILE.INCfunction and 10,000 simulated ages. M2:M4 should have formulas
to show statistics for the 10,000 values simulated.
Now, rerun the simulation six times by changing C6 and show in the yellow-fill region in
L7:Q7 the 10%ile age for the six different percentages saved (L6:Q6). Replace each
"???" with VALUES (NOT formulas) with as many decimals as the simulation produces.
When finished with this step replace C6 with the original value of 15.0%.
Assume that your 10,000 simulated ages arerepresentative of what might happen in the
future. In M12:R29 below Part 1 on the Starter sheet carefully explain the meaning of the
10 percentile age shown in O3 when 15% of salary is saved. Target your explanation to
an English major with no understanding of Finance asking you how long their money
might last in retirement.
For this part you will need to use the model you built in Part 1, changing only the
formulas in column G (to reference new mean and standard deviations) and A115. You
will need to add a few formulas in the gray workspace (V43:AD76). Carefully label any
new entries in this workspace.
Assume now that you divide your portfolio into two pieces, the risky part (stocks, equity
funds, hedge funds, options, futures, etc.) and the riskless part (T-bills, guaranteed rate
investments, etc. having no risk). This strategy is consistent with the two-fund separation
result of the Markowitz model we discussed in class. If you have difficulty completing the
tasks below review the Ch 8 Edited workbook and associated lectures.
The weights on the risky portfolio part will be between 1% and 100%. Use the assumptions
in R36:R40 for the following. Run 24 simulations to determine the value of your
retirement portfolio in today's dollars (i.e., deflated) at age 65 immediately before the first
withdrawal. For each simulation change the balance of your portfolio using the weights in
the green fill table along with the different percentiles shown. Be sure to adjust the
portfolio expected return (simulated in column G) and standard deviation for each
simulation run. As you fill in the table "XXXXX"s with values (NOT formulas) from the
simulations the chart below the graph will automatically adjust to display the results.
Below the graph in the blue fill region explain how you would use it to determine the best
balance of the risky and riskless parts of your portfolio. What weights wouldyouuse to
form your portfolio andwhy? Answer using a technical writing style - precise, short on
adverbs and adjectives, and economical in the use of words.
Lastly, if you had the option to invest in any Vanguard fund as the risky part of your
retirement portfolio which fund or funds would you pick? Why? Answer in the region
provided on the Starter sheet. Vanguard funds can be seen at