YOU CAN USE EXCEL AND OR ANY OTHER SOFTWARE AS RANDOM NUMBER GENERATOR FOR THE NUMERICAL SIMULATION Financial Planning Case Study HW.pdf Actions a) Recreate tables 7.4, 7.5, 7.6, 7.7,

PART 1

FYI – YOU CAN USE EXCEL AND OR ANY OTHER SOFTWARE AS RANDOM NUMBER GENERATOR FOR THE NUMERICAL SIMULATION

Financial Planning Case Study HW.pdf

Actions

a) Recreate tables 7.4, 7.5, 7.6, 7.7, and 7.8 in the spreadsheet below:

Financial Planning Assignment 2023.xlsx Download Financial Planning Assignment 2023.xlsx

I started 7.4 for you; you just need to complete it, then add all other tables to the right of table 7.4 on the “Main Page” tab. Use formulas to make calculations, i.e., do not hard code values into the cells.

b) SIMULATE THE PROBABILITY OF HENRY RUNNING OUT OF MONEY BY THE TIME HE IS 95

Assuming a normal distribution for each of the annual returns, and the assumptions provided in the spreadsheet in cells E3 and E4 on the Simulation Page tab, what is the probability that Henry will run out of money by the time he is 95? (i.e. ending balance after 30 years is negative). Create a new sheet by duplicating the Simulation Page” tab and call it Simulations. Simulate on this tab 1000 instances an calculate the probability of Henry running out of money by 95. Also, type in your answer on the Tab labelled “ANSWERS TO Parts b through g”.

c) Henry realizes that the probability of running out of money in strategy b) above is too high. He would prefer to have a strategy so that he has a 97% probability of not running out of money at age 95 – so only a 3% probability of a negative balance at age 95. So he decides maybe he should withdraw less than his original plan of $60,000 (+ 3% inflation increase every year) per year. How much should Henry withdraw in year 1 (and still increase this amount every year per the 3% inflation assumption) so that he only has at most a 3% chance of having a negative ending balance after 30 years? Note – round to the nearest $1,000 such that the probability of the ending balance equaling 0 is at most 3%.

PART 2

The second part of the homework will have you repeat VaR and Economic Capital, models. Use the return time series’ from last time (Amazon & Apple daily returns). Calculate for a portfolio that consists of USD 1,000,000.00 invested in 30% Apple and 70% Amazon the following:

Daily VaR [in USD]

Analytical Calc

Theoretical VaR for (50%, 80, 90, 95,99, 99.9, 99.999, 99.9999, 99.99999999)
Number of theoretical losses that we’d expect to exceed the theoretical VaR
Number of actual losses exceeding the theoretical VaR

Numerical Calc

Historical VaR for (50%, 80, 90, 95,99, 99.9, 99.999, 99.9999, 99.99999999)
Number of theoretical losses exceeding the historical VaR

Discuss the results with a couple of sentences

Economic Capital [in USD]

A theoretical firm has the following balance:

Assets: 1mn, 30% Apple & 70% Amazon
Liabilities: 1mn, X% Equity, 1-X% Liabilities

Calculate the economic capital

10-Day VaR, 99.99%

Identify and discuss your model’s assumptions and limitations