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
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
- Analytical Calc
- 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
- A theoretical firm has the following balance: