Đang chuẩn bị liên kết để tải về tài liệu:
Lecture Financial modeling - Topic 8: Dynamic portfolio simulation, monte carlo, personal financial planning models, @Risk

In this chapter, students will be able to understand: 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. | Financial Modeling Topic #8: Dynamic Portfolio Simulation, Monte Carlo, Personal Financial Planning Models, @Risk L. Gattis 1 Download excel starting excel file: https://sites.google.com/site/lougattis/documents 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 Assumptions Capital Market Assumptions: Vols, Means, and Correlations above Asset Allocation Stock% = 120 – Age, Remaining in Bonds, no cash (money market) Year-End Contributions Retirement Annuity n=30-year term, i=Bond Rate, pv=portfolio value Real Value of 1st Annuity = Nominal Annuity / 1+i^(years to retirement) 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 Retirement Does not include social security or private pensions Importance: Measure of retirement transition income adjustment Research 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 income 5 Retirement Portfolio Estimate Simulated 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 / Income Macro to Run 5000 Iterations Sub simport5000() For i = 1 To 5000 Cells(9, 12) = 5000 - i Cells(11 + i, 12) = Cells(7, 10) Next i End Sub Simulation Results In-Class Exercise How would you change the macro to work even if automatic recalculation is turned off? @Risk Simulation To convert our Macro simulation with @Risk Launch @Risk Replace normsinv(rand()) with risknormal(0,1) Add three outputs from simulated results Simulated portfolio, W/I, Replacement Rate Run 10,000 iterations Browse results @Risk Simulation @Risk Goal Seek 11 @Risk Advanced Analysis Select Goal Seek Goal Cell: replacement Statistic: Mean Value: .7 Changing Cell: Savings Rate Savings = 14% @Risk Advanced Analysis Select Goal Seek Goal Cell: replacement Statistic: percentile(.10) Value: .4 Changing Cell: Savings Rate Savings = 16% Final Model – No @Risk 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 13