1. Retirement planning in nominal dollars 2
Great job! You now have set up half of a nominal dollar model. Let's learn how to finish the calculations for that model.
2. Add annual withdrawal
For the first year, your annual withdrawal should be equal to the initial balance in our fixed variables using equal B9 in this example. You can use a relative reference because we are only using this variable once.
3. Annual withdrawal with inflation
For the rest of the years, we will need to include inflation in our withdrawal amount. The formula for these cells is initial withdrawal times one plus inflation raised to the power of the year minus one. Be sure to include the parentheses in your formula, along with an absolute reference with both dollar signs for inflation and initial withdrawal.
4. How much money did you make?
Next, we will calculate the return on our fixed income and stocks. In the income after tax column, enter equals click on fixed income year beginning for that row, then return on fixed income times one minus the ordinary tax rate. Be sure to include absolute references for your return and tax rate.
5. Making money part 2
For the return on stocks, type equals, click on stock holding beginning times click on return on stocks. Again, return on stocks rates should be absolute references so you can use this formula in later cells.
6. Add everything together
For our last calculations, you will simply add together different previously calculated cells so you can examine the total holdings across years.
In year-end stock holding, type equals, click on stock holding beginning, type plus, and click on stock return in that row. For year-end fixed income, type equals, click on fixed income year beginning, type plus, and then click on income after tax. Last, calculate the total year-end balance by typing equals, then click on year-end stock, type plus, click on year-end fixed income.
7. Copy over formulas to finish
To complete the model, copy the formulas into the cells below it to create a table of retirement money estimates for 15 years.
8. Finalized model
Here's the finalized model. The tax basis of stock has been included for you, which is the previous year tax basis times the ratio of this year's stock year beginning divided by the stock year-end for the previous year.
9. Adjust model parameters
In examining the estimated values from our model, we can see that our stock holding turns negative around year fourteen. We can make adjustments to our initial parameters to determine what might be necessary to ensure a positive balance on income and stocks throughout the years.
10. Increase initial balance
While you can adjust estimated inflation and taxes, the likely way to improve your model is to increase the initial balance until the values become positive. Let's try adding twenty thousand dollars to the initial balance. Because we used referencing in our model, we can see the updates, but the estimated stock holdings is still negative in year eleven. When we update the balance by another twenty thousand dollars, we now see that the estimated values are positive throughout the fifteen-year security horizon. This adjustment is a good way to plan what you need to do now to ensure money through retirement.
11. Estimate your retirement!
Let's go over to the exercises and finish our retirement model.