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...

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.

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 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

https://investor.vanguard.com/home

May 09, 2021

Submit New Assignment

Copy and Paste Your Assignment Here