Tài chính doanh nghiệp - Topic 8: Dynamic portfolio simulation, monte carlo, personal financial planning models, @risk

Financial ModelingTopic #8: Dynamic Portfolio Simulation, Monte Carlo, Personal Financial Planning Models, @RiskL. Gattis1Download excel starting excel file:https://sites.google.com/site/lougattis/documentsLearning ObjectivesSimulate portfolios with multiple periods, changing asset allocation, and contributionsCreate a personal financial planning modelUse @Risk and Macros to run Monte Carlo SimulationsUse @Risk Goal Seek2Retirement Portfolio EstimateAssumptionsCapital Market Assumptions: Vols, Means, and Correlations aboveAsset AllocationStock% = 120 – Age, Remaining in Bonds, no cash (money market)Year-End ContributionsRetirement Annuityn=30-year term, i=Bond Rate, pv=portfolio valueReal Value of 1st Annuity = Nominal Annuity / 1+i^(years to retirement) Key Retirement RatiosWealth / IncomeCalculation: Total Monetary Wealth at Retirement / Annual Income prior to retirementImportance: Standardized measure of savings adjusting for income levelResearch suggests that W/I ratio targets should be between 5 and 15 times income (increasing function of wealth)Income ReplacementCalculation: Annual Retirement Income from Private Savings / Annual Income prior to RetirementDoes not include social security or private pensionsImportance: Measure of retirement transition income adjustmentResearch suggests that income replacement should be between 30% and 70% for middle income individuals. Why less than 100%?Social security and private pension replacement (higher for low income)Expense reduction (e.g., commuting and other job related expenses, higher for low income)No longer saving (10-15%). Spend 100% of retirement income5Retirement Portfolio EstimateSimulated portfolio values reflect last iteration only. Changes each time worksheet recalculates (f-9) or excel operation (paste, enter)Every 5 years shown (other rows hidden for display)Key Retirement Planning Ratios: (1) Income replacement (2) Wealth / IncomeMacro to Run 5000 IterationsSub simport5000()For i = 1 To 5000 Cells(9, 12) = 5000 - i Cells(11 + i, 12) = Cells(7, 10)Next iEnd SubSimulation ResultsIn-Class ExerciseHow would you change the macro to work even if automatic recalculation is turned off?@Risk SimulationTo convert our Macro simulation with @RiskLaunch @RiskReplace normsinv(rand()) with risknormal(0,1)Add three outputs from simulated resultsSimulated portfolio, W/I, Replacement RateRun 10,000 iterationsBrowse results@Risk Simulation@Risk Goal Seek11@Risk Advanced AnalysisSelect Goal SeekGoal Cell: replacement Statistic: MeanValue: .7Changing Cell: Savings RateSavings = 14%@Risk Advanced AnalysisSelect Goal SeekGoal Cell: replacement Statistic: percentile(.10)Value: .4Changing Cell: Savings RateSavings = 16%Final Model – No @RiskLearning ObjectivesSimulate portfolios with multiple periods, changing asset allocation, and contributionsCreate a personal financial planning modelUse @Risk and Macros to run Monte Carlo SimulationsUse @Risk Goal Seek13