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 21-121 by 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. 77.6215). Assume 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.
|
|
|
Part 2
|
|
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 would
you
use 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
|
https://investor.vanguard.com/home
|