File Name: Simulation – Retirement Funding with VBA Macros
Location: Modeling Toolkit | Risk Simulator | Retirement Funding with VBA Macros
Brief Description: Illustrates how to use Risk Simulator for running Monte Carlo simulation and capturing the output results, and for running VBA macros in your models together with Monte Carlo simulation
Requirements: Modeling Toolkit, Risk Simulator
This model illustrates a simplified retirement amortization computation allowing for inflation adjustments. That is, given how much you currently have and the expected rate of return on your investment vehicles, the expected inflation rates, your current salary, and the expected amount you wish to have starting at retirement, this model computes how much annual investment is required to sufficiently fund your retirement (Figure 143.1).
Figure 143.1: Retirement funding model (inflation adjusted)
The required inputs and computed outputs are summarized below:
Required Inputs
- Initial Savings Amount: How much you have currently
- Annual Required Rate of Return: The expected annualized rate of return on the investment vehicle
- Current Age: Your current age right now, corresponding to the initial savings
- Retirement Age: Your proposed age at retirement
- Average Terminal Age: The expected age at death (life expectancy)
- Annual Salary: Your current salary level
- Replacement Ratio: Percentage of your current salary required at retirement
- Inflation Rate: Annualized inflation rate expected
- Terminal Wealth Goal: Typically, $0 (complete amortization)
Computed Results
- Terminal Wealth: How much you have left at terminal age
- Annual Contribution: Required annual contribution to have sufficient funds for retirement
Monte Carlo Simulation
As returns on investment and inflation rates are both uncertain and vary, these two variables are defined as input assumptions. The output forecast is the required annual contribution. This model already has a simulation profile, assumptions, forecasts, and VBA macros created.
To run this model, simply:
- Go to the Payments Per Period worksheet
- Select Risk Simulator | New Profile and define assumptions on Rate of Return (E6) and Inflation (I8) and then click on the RUN icon or Risk Simulator | Run Simulation
Model Analysis
You can recreate the model’s simulation and VBA analysis by repeating the following steps:
- Click on Alt-F11 to get to the Excel VBA environment (Figure 143.2).
- Double click on Sheet 1 and you will see the VBA macro code used to compute the Annual Contribution. Notice that the codes are listed under the Private Sub called Worksheet Change. This setup means that every time a value changes in the worksheet (i.e., when a simulation trial occurs), the macro will be executed and the value of the annual contribution will be computed accordingly in each simulation trial.
- Exit the VBA environment and go back to the Payments Per Period worksheet.
- Create a New Simulation Profile and give it an appropriate name.
- Define Assumptions on the Interest Annual Required Rate of Return and Inflation Rate.
- Define Forecast on Annual Contribution.
- Run the simulation.
Figure 143.2: Visual Basic for Applications (VBA) code with simulation
