1. Retirement planning in real dollars
Retirement planning can be complex because it's hard to know what the future holds. This lesson will cover how to plan in real dollars or the actual amount of money that things cost.
2. Save that money!
We start by setting up our fixed values, which include the estimated annual return rate during retirement. This rate is the amount of interest you expect to make on your money during retirement. Next, you add in how much money you want to withdraw each year. The nest egg is the amount of money you need to save for retirement. This value is calculated by the withdrawal divided by the return rate.
3. Years and savings
The next consideration is the number of years you will be saving. Here is a table of years until retirement, where the larger numbers indicate more years to save. The columns represent the return rate during the years in which you are saving money. Including multiple return rates allows you to model different scenarios of interest rates. The values in the table are the amount of money you need to save each month to achieve your nest egg for retirement.
4. Use the pmt() function
We can use the pmt() function to calculate our yearly payment like we did before to save money for college. The rate is one plus the annual rate during your savings years raised to the power of 1/12th minus 1. We use 1/12th to indicate that we want to know how much we have to save each month to meet our goal. The present value is zero because we haven't started saving, while the future value is our nest egg as a negative number. Notice that we are using partial absolute references to keep the annual savings rate and years til retirement consistent when we copy it over to the rest of the cells. By examining the chart, you can see you will need to save less each month if you plan your retirement earlier because you will have more time to save money.
5. Graph the data
We can add a quick graph to help visualize the effect of years till retirement on the required monthly savings. To create the graph, highlight the savings table, click insert, then chart. We can change the automatic chart to a line graph by clicking on the chart type and clicking on line graph right underneath column graph. With this graph, it is easier to see that the amount to save monthly is nearly cut in half for every five additional years of investment time and that higher interest rates decrease the amount you need to save.
6. Let's practice!
Let's try your own retirement model!