Lecture Financial modeling - Topic 8: Dynamic portfolio simulation, monte carlo, personal financial planning models, @Risk

Download excel starting excel file: https://sites.goo gle.com/site/lou gattis/document s Financial Modeling Topic #8: Dynamic Portfolio Simulation, Monte Carlo, Personal Financial Planning Models, @Risk 1 Learning Objectives • Simulate portfolios with multiple periods, changing asset allocation, and contributions • Create a personal financial planning model • Use @Risk and Macros to run Monte Carlo Simulations • Use @Risk Goal Seek 2 Retirement Portfolio Estimate SavingsAssumptions Income $55,000 Correl SavingsRate 15% Stock CapitalMarket Assumptions Stock Bond Cash 1.00 0.30 - Mean Std Dev 7.50% 16.00% Results Expected FV(Portfolio) $2,051,298 • Assumptions Inflation Rate SalaryGrowth 2.50% Bond 2.50% Cash 0.30 1.00 - 0.10 0.10 3.50% 1.00 2.50% 8.00% Ret. Annuity $ 2.00% Real Annuity $ 111,532 41,538 – Capital Market Current Age 25 Covar Stock Bond Cash Simulated Initial Portfolio $1 Stock 0.0256 0.0038 - FV(Portfolio) YearsinRetire 30 Bond 0.0038 0.0064 0.0002 Ret. Annuity $ -Ageat Retire. 65 Cash - 0.0002 0.0004 Real Annuity $ -Note: Simulatedportfolio valueusesrandom walkmodel (S=S-1*(1+μ+Zσ)whereZ=normsinv(rand()),Year End Cont. Age Salary Contribut. Stock% Bond% Cash% μ σ Exp. PortfolioSim.Portfolio Assumptions: Vols, Means, and Correlations above 25 $ 55,000 $ 8,250 95% 5% 26 $ 56,375 $ 8,456 94% 6% BegBal=> 0% 7.30% 15.32% 0% 7.26% 15.19% $1 $8,251 $17,306 $1 $8,251 $18,202 – Asset Allocation 27 $ 57,784 28 $ 59,229 29 $ 60,710 30 $ 62,227 31 $ 63,783 32 $ 65,378 33 $ 67,012 34 $ 68,687 $ 8,668 93% 7% $ 8,884 92% 8% $ 9,106 91% 9% $ 9,334 90% 10% $ 9,567 89% 11% $ 9,807 88% 12% $10,052 87% 13% $10,303 86% 14% 0% 7.22% 0% 7.18% 0% 7.14% 0% 7.10% 0% 7.06% 0% 7.02% 0% 6.98% 0% 6.94% 15.06% $27,224 14.92% $38,063 14.79% $49,887 14.66% $62,763 14.53% $76,761 14.40% $91,957 14.27% $108,427 14.14% $126,255 $29,300 $49,724 $60,942 $81,164 $119,366 $111,277 $141,352 $162,160 • Stock% = 120 – Age, Remaining in Bonds, no cash (money market) 35 $ 70,405 40 $ 79,656 45 $ 90,124 $10,561 85% 15% $11,948 80% 20% $13,519 75% 25% 0% 6.90% 0% 6.70% 0% 6.50% 14.01% $145,527 13.37% $266,914 12.74% $440,207 $200,122 $345,999 $616,292 – Year-End Contributions 50 $ 101,967 55 $ 115,366 60 $ 130,526 $15,295 70% 30% $17,305 65% 35% $19,579 60% 40% 0% 6.30% 0% 6.10% 0% 5.90% 12.14% $682,157 11.55% $1,013,549 10.99% $1,459,769 $1,288,799 $1,664,415 $1,708,097 – Retirement Annuity 65 $ 147,679 70 $ 167,085 75 $ 189,041 80 $ 213,883 85 $ 241,988 $22,152 55% 45% $25,063 50% 50% $28,356 45% 55% $32,082 40% 60% $36,298 35% 65% 0% 5.70% 0% 5.50% 0% 5.30% 0% 5.10% 0% 4.90% 10.46% $2,051,298 9.96% $2,824,114 9.50% $3,819,918 9.08% $5,086,111 8.71% $6,675,467 $2,725,035 $3,550,749 $4,873,228 $7,932,956 ########## • n=30-year term, i=Bond Rate, pv=portfolio value Key Retirement Ratios • Wealth / Income – Calculation: Total Monetary Wealth at Retirement /Annual Income prior to retirement – Importance: Standardized measure of savings adjusting for income level – Research suggests that W/I ratio targets should be between 5 and 15 times income (increasing function of wealth) • Income Replacement – Calculation:Annual Retirement Income from Private Savings / Annual Income prior to SavingsAssumptions etirCapitalMarket AssumptionsPortfolio Estimate Income SavingsRate $55,000 Correl 15% Stock Stock Bond Cash 1.00 0.30 - Mean StdDev 7.50% 16.00% Expected FV(Portfolio) $2,051,298 13.9 Retirement Wealth/ Income InflationRate SalaryGrowth 2.50% Bond 2.50% Cash 0.30 1.00 - 0.10 0.10 3.50% 1.00 2.50% 8.00% Ret. Annuity 2.00% Real Annuity $ 111,532 76%IncomeReplacement $ 41,538 Current Age Initial Portfolio 25 Covar $1 Stock Stock Bond Cash 0.0256 0.0038 - Simulated FV(Portfolio) $2,046,133 Simulated 13.9 IncomeReplacement YearsinRetire Ageat Retire. 30 Bond 0.0038 0.0064 0.0002 65 Cash - 0.0002 0.0004 Ret. Annuity Real Annuity $ 111,251 75%Retirement Wealth/ Income $ 41,433 Note: Simulatedportfoliovalueusesrandomwalkmodel (S=S-1*(1+μ+Zσ)whereZ=normsinv(rand()),Year EndCont. Age Salary Contribut. Stock% Bond% Cash% μ σ Exp. PortfolioSim.Portfolio BegBal=> $1 $1 25 $ 55,000 26 $ 56,375 27 $ 57,784 28 $ 59,229 29 $ 60,710 30 $ 62,227 31 $ 63,783 32 $ 65,378 33 $ 67,012 34 $ 68,687 35 $ 70,405 40 $ 79,656 45 $ 90,124 $ 8,250 95% 5% $ 8,456 94% 6% $ 8,668 93% 7% $ 8,884 92% 8% $ 9,106 91% 9% $ 9,334 90% 10% $ 9,567 89% 11% $ 9,807 88% 12% $10,052 87% 13% $10,303 86% 14% $10,561 85% 15% $11,948 80% 20% $13,519 75% 25% 0% 7.30% 0% 7.26% 0% 7.22% 0% 7.18% 0% 7.14% 0% 7.10% 0% 7.06% 0% 7.02% 0% 6.98% 0% 6.94% 0% 6.90% 0% 6.70% 0% 6.50% 15.32% $8,251 15.19% $17,306 15.06% $27,224 14.92% $38,063 14.79% $49,887 14.66% $62,763 14.53% $76,761 14.40% $91,957 14.27% $108,427 14.14% $126,255 14.01% $145,527 13.37% $266,914 12.74% $440,207 $8,251 $17,495 $28,611 $40,980 $40,433 $55,613 $68,106 $97,059 $121,850 $160,468 $200,098 $290,236 $556,185 50 $ 101,967 $15,295 70% 30% 0% 6.30% 12.14% $682,157 $759,633 55 $ 115,366 60 $ 130,526 65 $ 147,679 $17,305 65% 35% $19,579 60% 40% $22,152 55% 45% 0% 6.10% 0% 5.90% 0% 5.70% 11.55% $1,013,549 10.99% $1,459,769 10.46% $2,051,298 $854,850 $1,451,251 $2,046,133 • Simulated portfolio values reflect last iteration only. Changes each time worksheet recalculates (f-9) or excel operation (paste, ... - tailieumienphi.vn