Retirement Planning - A Quick Estimate
Retirement Planning – A Quick Estimate
How much do I need to retire?
This is one of the most difficult questions that we face, there are so many variables and factors that will change to consider that a definitive answer is hard to achieve.
However, this doesn’t mean that we shouldn’t try!
Running a few simple scenarios in excel can help provide some guidance and allows you to test a range of outcomes.
The Net Present Value (NPV) formula is a simple calculation that is built into excel and “discounts” the stream of required income at the decided rate of investment return to provide a value of the fund that you would need now to provide the level of income.
Look at the two tables below – to the left we see that to provide a consistent stream of £30,000 between age 65 and 90 you would require a fund of £536,305 assuming a return of 3%; at age 90 the full value of the fund would be used up.
On the right is an example that reflects the fact that income requirements are not linear and are likely to change; in the early phase we see a higher income with larger amounts drawn at certain ages e.g. 65, 70 and 75 before the amounts tail off later in life. To sustain this level of income at 3% a fund of £584,724 will be required.
|
Investment Return |
3.0% |
Investment Return |
3.0% |
|||
|
Net Present Value |
£ 536,305 |
Net Present Value |
£ 584,724 |
|||
|
Age |
Year |
Income Required |
Age |
Year |
Income Required |
|
|
65 |
2020 |
£30,000 |
65 |
2020 |
£45,000 |
|
|
66 |
2021 |
£30,000 |
66 |
2021 |
£35,000 |
|
|
67 |
2022 |
£30,000 |
67 |
2022 |
£35,000 |
|
|
68 |
2023 |
£30,000 |
68 |
2023 |
£35,000 |
|
|
69 |
2024 |
£30,000 |
69 |
2024 |
£35,000 |
|
|
70 |
2025 |
£30,000 |
70 |
2025 |
£40,000 |
|
|
71 |
2026 |
£30,000 |
71 |
2026 |
£32,000 |
|
|
72 |
2027 |
£30,000 |
72 |
2027 |
£32,000 |
|
|
73 |
2028 |
£30,000 |
73 |
2028 |
£32,000 |
|
|
74 |
2029 |
£30,000 |
74 |
2029 |
£32,000 |
|
|
75 |
2030 |
£30,000 |
75 |
2030 |
£45,000 |
|
|
76 |
2031 |
£30,000 |
76 |
2031 |
£32,000 |
|
|
77 |
2032 |
£30,000 |
77 |
2032 |
£32,000 |
|
|
78 |
2033 |
£30,000 |
78 |
2033 |
£32,000 |
|
|
79 |
2034 |
£30,000 |
79 |
2034 |
£32,000 |
|
|
80 |
2035 |
£30,000 |
80 |
2035 |
£30,000 |
|
|
81 |
2036 |
£30,000 |
81 |
2036 |
£30,000 |
|
|
82 |
2037 |
£30,000 |
82 |
2037 |
£30,000 |
|
|
83 |
2038 |
£30,000 |
83 |
2038 |
£30,000 |
|
|
84 |
2039 |
£30,000 |
84 |
2039 |
£30,000 |
|
|
85 |
2040 |
£30,000 |
85 |
2040 |
£25,000 |
|
|
86 |
2041 |
£30,000 |
86 |
2041 |
£25,000 |
|
|
87 |
2042 |
£30,000 |
87 |
2042 |
£25,000 |
|
|
88 |
2043 |
£30,000 |
88 |
2043 |
£25,000 |
|
|
89 |
2044 |
£30,000 |
89 |
2044 |
£25,000 |
|
|
90 |
2045 |
£30,000 |
90 |
2045 |
£25,000 |
|
This relatively simple approach can also be expanded to include not only additional information but also changing assumptions such as increasing the expenditure to reflect inflation.
Using this type of analysis provides interesting guidance as to what may happen, but always remember that it is guidance and not a forecast of what will happen.
Note
To use the NPV formula in excel you will need to type =NPV and then inside the brackets highlight the cell that holds the investment return and the cells that hold the stream of income.
The one in our example looks like this =NPV(C2,C6:C31)
This means the NPV calculation is applied to cell 2 (investment rate) and then looks at the cells C6 to C31 (the streams of income).