Part 1 FYI: You Can Use Excel Or Any Other Software As Ran

Part 1fyi You Can Use Excel And Or Any Other Software As Random Numb

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 and calculate the probability of Henry running out of money by 95. Also, type in your answer on the tab labeled "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). 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.) 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.) 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

Paper For Above instruction

The task addresses a comprehensive financial simulation and risk analysis, leveraging tools such as Excel and statistical models to evaluate retirement strategies and portfolio risks. The primary aim is to model Henry’s retirement withdrawal scenario, estimate the probability of running out of money, adjust withdrawal strategies for targeted risk levels, and analyze the Value at Risk (VaR) and Economic Capital for a stock portfolio based on Apple and Amazon daily returns. This paper systematically explores each component of the assignment, integrating financial theory with practical spreadsheet modeling techniques.

Recreating Financial Tables and Simulation Setup

The initial step involves recreating tables 7.4 through 7.8 from the referenced case study within Excel. This task requires understanding the data and mathematical relationships represented in these tables and translating them into formula-driven calculations in the spreadsheet. The tables likely include projections of balances, withdrawal schedules, or probability distributions, which are essential for the subsequent Monte Carlo simulation. After reproducing Table 7.4, the remaining tables are added adjacent to it on the "Main Page," ensuring the calculations are dynamic and update automatically when base data or assumptions change.

To simulate Henry’s retirement withdrawal and the probability of financial shortfall, the approach employs Excel’s random number generation capabilities or other software tools. The simulation process involves generating 1,000 possible outcomes of annual returns using a normal distribution based on the specified mean and standard deviation (cells E3 and E4 on the Simulation Page). Each simulated path accounts for annual withdrawals—initially $60,000 adjusted annually for inflation—then determines whether Henry’s balance turns negative by age 95.

Probability of Running Out of Money by Age 95

The core analysis involves calculating the probability that Henry's retirement fund depletes before or at age 95. Assuming normal distributions for annual returns, the simulation models the portfolio’s trajectory over time. By duplicating the "Simulation Page" into a "Simulations" sheet and running 1,000 iterations, the proportion of outcomes where the ending balance is below zero provides the estimated probability. This process illustrates the variability inherent in investment returns and the importance of asset allocation and withdrawal strategies in retirement planning.

Results from this simulation inform whether Henry’s initial withdrawal plan of $60,000 is sustainable. If the probability exceeds acceptable thresholds, adjustments are necessary. The implementation details emphasize the use of formulas and dynamic referencing in Excel to automate the calculations, rather than hardcoding values, thus enabling scenario analysis and sensitivity testing.

Adjusting Withdrawals to Meet Risk Tolerance

Henry seeks a withdrawal amount that ensures at most a 3% chance of being unable to sustain payments until age 95. To determine this, the simulation is iterated with varied initial withdrawal levels, decreasing systematically until the probability of ending with a negative balance drops below 3%. Rounding to the nearest thousand simplifies practical application and aligns expenses with uncertain investment outcomes. Techniques such as goal-seeking or solver tools in Excel can facilitate discovering the appropriate initial withdrawal amount that meets this risk criterion.

Analysis of VaR and Portfolio Risk

The second part of the project involves calculating Value at Risk (VaR) and Economic Capital for a USD 1,000,000 portfolio invested 30% in Apple and 70% in Amazon. Using historical daily return data, the analysis computes the daily VaR under both analytical and empirical (historical) approaches. The analytical VaR employs distributional assumptions (normality), while the historical VaR derives from actual return series, providing a comparative risk assessment.

Further, the calculation of the expected exceedances—number of losses exceeding the theoretical and historical VaRs—provides insights into the models' accuracy and robustness. The interpretation considers the implications of deviations between theoretical expectations and observed data, highlighting model limitations such as distributional assumptions, sample size, and market anomalies.

Economic Capital and Model Limitations

Finally, the exercise estimates the Economic Capital for a hypothetical firm with assets and liabilities comprising the same portfolio. Assuming asset values fluctuate according to historical return distributions, the 10-day VaR at the 99.99% confidence level estimates the potential maximum loss in extreme scenarios. The calculation considers the interplay between assets, liabilities, and equity, providing a quantitative measure of the financial health and resilience of the entity against tail risks.

Underlying the models are assumptions like normality of returns, static correlation structures, and stationary market conditions. Limitations include potential underestimation of tail risks, ignored systemic shocks, and the reliance on historical data which may not capture future market dynamics accurately. Recognizing these constraints is essential for prudent risk management and informed decision-making.

Conclusion

This comprehensive analysis demonstrates the integration of spreadsheet modeling and statistical risk assessment tools in personal and institutional finance. Recreating and extending these models offers valuable insights into retirement planning's probabilistic nature and portfolio risk management, emphasizing the importance of dynamic simulation, risk quantification, and critical evaluation of model assumptions. Such exercises are fundamental for financial professionals aiming to optimize strategies under uncertainty, harnessing data-driven insights to mitigate risks and improve outcomes.

References

• Damodaran, A. (2012). Investment Valuation: Tools and Techniques for Determining the Value of Any Asset. Wiley Finance.
• Hull, J. C. (2015). Risk Management and Financial Institutions. Wiley.
• Jorion, P. (2006). Value at Risk: The New Benchmark for Controlling Derivatives Risk. McGraw-Hill.
• McNeil, A. J., Frey, R., & Embrechts, P. (2015). Quantitative Risk Management: Concepts, Techniques, and Tools. Princeton University Press.
• Sharpe, W. F., & Alexander, G. J. (1990). Modern Portfolio Theory, Structure, and Applications. Wiley.
• Taleb, N. N. (2007). The Black Swan: The Impact of the Highly Improbable. Random House.
• CCAR (Comprehensive Capital Analysis and Review). (2021). Federal Reserve Bank. https://www.federalreserve.gov/supervisionreg/ccar.htm
• Excel Analysis ToolPak. (2020). Microsoft Support. https://support.microsoft.com/en-us/office/use-the-analysis-toolpak-to-perform-complex-statistical-analyses-6aadeb55-4feb-4cf4-8979-e116a4a54371
• VaR Methodologies: Quantitative Methods & Applications. (2014). CFA Research Foundation.
• RiskMetrics Group. (1997). JP Morgan. Technical Document on VaR Modeling.