A simple approach to retirement income planning

Two of the most important calculations in retirement planning are (1) how much you need to save up in order to be ready to retire and (2) how much you can afford to draw from your savings each year in retirement. There are many different calculators available online, and each has its own assumptions. There is a quick-and-dirty way to come up with a basic estimation, however, that is a good starting point and is entirely transparent and easy to understand.

Both Microsoft EXCEL and Google Sheets have a function called PMT which can be used to calculate the level payments that you can draw from an investment account, given an expected rate of return and period over which the money will be withdrawn. So, for example, you can calculate how much annual retirement income you could draw each year, given assumptions about your life expectancy and rate of return. The reasoning and logic of applying the PMT functionality is explained in detail in a white paper titled The Only Spending Rule Article You’ll Ever Need by M. Barton Waring and Larry Siegel. Wade Pfau, a well-known expert on retirement planning, also endorses the use of the PMT function for estimating sustainable retirement income and his discussion is worth reading.

Using the PMT function

The PMT function has the following form:

=PMT(R,Y,B,F,1)

R = assumed rate of return on investment portfolio

Y = number of years in retirement or estimated remaining life span if you are already retired

B = Balance of retirement savings at retirement or current balance if you are already retired

F = Target final value at death

The final element, set equal to 1 above, indicates whether you will draw your income at the beginning (1) or the end (0) of the year. To be conservative, I use 1.

Use the assumed real rate of return for R, so that the annual income generated by the PMT calculation is constant in inflation-adjusted terms. I typically assume 3% real rate of return as the baseline. While this may seem low–and it is far lower than returns for a range of diversified portfolios over the last several decades–we are currently in an market conditions with high valuations for stocks and low bond yields, both of which suppress potential future returns. Research Affiliates useful online tool estimates that 3%-4% real return is the high end of what can be expected from an optimally-diversified portfolio over the next ten years.

The number of years in retirement is an unknown, of course, so it pays to plan for the possibility of living a long time. I suggest using this free online calculator for life expectancy rather than using the actuarial tables. When I use this calculator, I get a life expectancy of 97, which indicates that I better plan for a retirement that is considerably longer than average for Americans.

The balance of retirement savings, B, is simply the expected dollar value of retirement savings at the start or retirement or the current value if you are already retired. There are some permutations worth considering here. I use the sum total to financial assets, ignoring other assets such as real estate. There are cases in which retirement planning may include the assumption that real estate or other assets will be sold to fund retirement income, of course.

The target final value of retirement assets at death, F, is the amount that one plans to leave for heirs or philanthropy.

Sample retirement income cases

Case 1

Let’s look at some test cases. If you are planning for a 30-year retirement and you have saved up $1,000,000 for retirement. Using 3% real return, the PMT equation is:

=PMT(0.03,30,1000000,0,1)

The generated by this equation is $49,533.26. This means that it would be reasonable to expect that you can draw constant inflation-adjusted income of this amount in each year of retirement.This is assuming that you retire this year.

Many people are familiar with the so-called ‘4% rule’ which suggests that a someone anticipating a 30-year retirement can plan to draw an income equal to 4% of their portfolio value in the first year of retirement and then increase this draw with inflation. The 4% rule was derived to provide a high degree of confidence that a retiree will not deplete his or her portfolio, given historical 30-year sequences of returns. The 4% rule indicates that the investor in this case should draw $40,000 per year rather than $49,000+. To get an income of $40,000 per year from the PMT equation for a 30-year retirement, you need to assume annualized real return of 1.33%.

The major limitation to the PMT equation is that, in real life, there is uncertainty as to the future rate of return and returns from stock/bond portfolios vary substantially from year to year. Variability in return from year to year reduces the sustainable income draws, so the value used for portfolio return should be conservative to offset this uncertainty.

Case 2

Many people are in the situation of weighing the trade-offs of delaying retirement. In the example in case 1, If you are planning to let your current savings grow for another 2 years, but not contribute any more to your savings, the PMT equation is:

=PMT(0.03,30,1000000*(1+0.03)^2,0,1)

The “0.03” in this equation is the assumed real return for the 5-year period. The “^2” is the compounding effect of growth over two years. The answer, in this case, is $52,549.84, so delaying retirement for two years (without contributing more during these two years) will provide an extra $3,000 per year (an increase of 6% in real annual income).

Actually, delaying retirement is even more powerful when you consider that you will expect to have fewer years of living off of your savings because you are retiring 2 years later in this case (so we use 28 rather than 30 for the duration of retirement income draws):

=PMT(0.03,28,1000000*(1+0.03)^2,0,1)

The PMT estimates $54,892 for annual retirement income for this case, a 10.8% increase in real annual income. The combination of allowing your savings to grow for more years and shortening the duration of retirement is very powerful.

Using the PMT during retirement

During retirement, the PMT equation helps to estimate how to adjust income draws due to market fluctuations. If your portfolio value declined, the PMT declines and vice versa. At the start of each year, you use the PMT equation to determine how much you can draw during that year. If the market is up, you will be able to draw more. If the market is down, your estimated income draw will be lower.

Using the PMT to vary retirement income from year to year means that you can never entirely run out of money. The PMT calculation estimates what you can draw each year for however many number of years you are budgeting for, but that value may get very small if the returns on your investments are lower than expected for an extended period of time. Even so, any planning rule that provides a consistent mechanism for reducing income draws after a year of bad performance is helpful.

One of the challenges in applying the PMT to calculate retirement income that varies from year to year is that the retirees need to have the capacity to live on less in a bad year than the average that they expect. In other words, if you can’t reduce your consumption in response to a fall in your investment portfolio, you will need to be a lot more conservative in the rate of return you assume for the PMT. This corresponds to lower-risk investments, thereby mitigating the potential for s significant reduction in the PMT-calculated income.

Summary

Used appropriately, the PMT function in EXCEL and Google Sheets is a simple way to estimate retirement income that a given level of savings can support. The PMT is limited by (1) the uncertainty in expected return, (2) uncertainty on how long you will live in retirement, and (3) lack of accounting for investment risk. To mitigate these issues, the prudent approach is to reduce the expected rate of return and increase the length of retirement to provide a margin of safety.

Spending some time with the PMT rule is especially helpful in getting a sense of the best-case outcomes. If the income you calculate using 3% real return and your estimated lifespan in retirement is far lower than what you need, its time for a serious re-evaluation. It is also useful to perform stress tests. Some estimates suggest that a balanced portfolio that is 60% allocated to stocks and 40% to bonds (a common benchmark for a moderate portfolio) has an expected real return of only 1%-2%. What would your PMT-calculated retirement income look like in this type of low-return future?

All retirement planning tools are highly limited, like crude hand-drawn maps. Even so, it is better to have a rough map and to recognize its limitations than not to have the map at all. There are many more sophisticated tools that are used for financial planning that account for investment risk and other important variables. The PMT provides a useful starting point and touchstone for interpreting the results from other models. As such, I find the PMT to be a useful calculation for financial planning.