Throughout the years, there has been much discussion regarding an optimal retirement withdrawal rate. A withdrawal rate is a function of how much money you withdraw from your retirement fund each year. I’m sure many of you have heard of the oh-so-popular 4% withdrawal rate standard. Some experts have suggested a lower rate given low bond yields, while others have suggested a variable rate that gives retirees more flexibility depending on market conditions. Many assert that investors are better off choosing a first-year retirement withdrawal percentage and then growing the withdrawal amount each year to keep pace with inflation. However, this can be tricky. What if you choose the wrong initial rate? If you choose a rate that is too high, you face possible shortfall risk (the risk of running out of money within your lifetime). If you choose a rate that is too low, you might not be taking full advantage of your retirement savings.

Instead of focusing on a specific withdrawal rate to use throughout retirement, you could calculate how much you can afford to withdraw annually in retirement based on the amount of money you want to have left over for your estate, how many years you estimate you will be in retirement and estimated inflation and rate of return. CI’s Retirement Withdrawal Calculator does just this.

Would you want to maintain your current lifestyle in retirement? Would you be able to? Or would you want to, or have to, live off of less per year? These types of questions should constantly be asked in order to properly plan for living in retirement. Selecting a withdrawal rate doesn’t need to be set in stone, but the planning is important. There will always be uncertainties within the stock, fund and bond markets; modifications to your retirement withdrawal plan are often necessary based on current market environments.

Typically the annual inflation rate can be estimated by using the consumer price index (CPI) percentage. CPI measures changes in the price level of a market basket of consumer goods and services purchased by households. CPI information is published by the Bureau of Labor Statistics. An inflation measure is important because this is the percentage amount that your buying power will decrease by each year. As your purchasing power declines, it will take more money to buy the same amount of goods. Retirement plans are typically long-term, so adjusting for inflation is vital.

An estimated rate of return is also included in our calculator. Each year you will have a “nest egg” earning a rate of return because some of the fund will remain invested in the market. Throughout retirement, you will withdraw from this amount. Choosing a rate of return is a function of asset allocation and long-term return averages. AAII provides long-term averages regarding stocks, fixed income and the overall market in the Asset Allocation Models section of our website. This section also shows sample asset allocation models that provide an investor profile for aggressive, moderate and conservative individuals. For each broad asset allocation scenario we provide portfolio returns, characteristics such as growth, income and risk as well as time horizons. Table 1 reproduces the returns and standard deviations for popular benchmarks, as shown in the Asset Allocation Model section of AAII.com.

There are several sites online that give annual inflation percentages as well as averages to assist you with estimating inflation. USinflationcalculator.com has an area that provides inflation rates for each month of each year dating back to 1914 as well as an average. To estimate the average amount you could earn in your retirement account, either look at historical return information or use a benchmark to estimate an average return going forward. Morningstar.com keeps track of several index performance figures as well as funds and ETFs. You can also use the U.S. Treasury Dept. website to view bond yields over time. Some individuals use the Social Security Administration actuarial life tables to estimate number of years in retirement.

Former AAII Journal editor Maria Crawford Scott wrote an article in the July 2012 issue titled “Finding the Right Withdrawal Rate: One Key to Portfolio Sustainability.” In it, she mentioned that investors living off their retirement savings typically have two goals: having enough savings to provide living needs throughout an entire lifetime, no matter the market conditions, and having savings that provide enough financial resources to do the things they want to do during retirement. The article addresses choosing an appropriate systematic withdrawal approach based on realistic rates of withdrawal. The keyword is realistic. With this spreadsheet, we wanted to provide investors with a basic approach to calculating a realistic withdrawal rate year to year in retirement.

If you input values that yield an annual withdrawal amount far below what you want to live off of in retirement, you need to rethink your strategy. Perhaps the desired rate of return you earn leading up to retirement should be more aggressive. Maybe you need to adjust your asset allocation while in retirement. Some investors may need to consider retiring later than expected in order to grow retirement funds for additional time before beginning to withdraw.

Keep in mind that this spreadsheet does not account for taxes or investment expenses. A “required” minimum withdrawal rate was not factored into the spreadsheet either. Bankrate.com provides individuals with required minimum distribution calculations. This spreadsheet is intended for educational and estimation purposes only.

The Spreadsheet

Input Tab

The first tab of the spreadsheet, which you can download by clicking here, is titled “input.” Everything highlighted in yellow requires manual input. Input positive values only. The first step is to estimate how much you will have when you retire. This is calculated in the box toward the bottom of the sheet titled “Funds at Retirement.” If you have a current retirement fund amount, input that amount into the “current retirement fund amount” cell. If you do not have a current retirement fund, simply input zero. If you contribute annually to your retirement account, input that amount into the “annual retirement contribution” cell. If you do not contribute, just enter zero. Estimate the annual percentage rate your fund increases by and input that amount into the “expected annual % return on funds” cell. This estimated rate of return needs to be realistic, otherwise it will overstate the amount you have in your retirement fund. Over the last 25 years, the S&P 500 index has averaged an annual gain of roughly 6.9% (including dividends and accounting for inflation). The estimated amount that you will have when you retire will be calculated for you. The calculation used in cell D18 is:

=ABS(FV(D17,D15,D16,D14,0))

What we did here was a future value calculation. The formula calls for a rate, which in this case is your expected return on the portfolio in cell D17. The next value is the number of years into the future you will grow the original amount, represented in cell D15. The third value in the formula is the annual contribution amount, represented in cell D16. The fourth value is the current amount you have in your retirement portfolio, also known as the present value, which is input in cell D14. The zero at the end of the formula is telling Excel that we want this to be calculated with end-of-period payments or contributions. The alternative in this formula would be changing the zero to a one, which would mean beginning of period contributions.

The letters “ABS” in the beginning of the formula signal the use of the absolute value function. Without taking the absolute value of the number returned by the future value formula, the result would appear as negative. This is because when you input the values into the highlighted boxes, you input all positive values. The future value function typically calls for a negative beginning value (present value) that signals an initial investment outlay amount. Most financial calculators and spreadsheets follow the cash flow sign convention. It is a way of keeping the direction of the cash flows straight. Cash inflows are entered as positive values and cash outflows are entered as negative values: A $100 investment today would be cash out of your pocket (a negative value) and in a certain amount of years after earning a rate of return on your investment you would receive a positive cash inflow. Conversely, if you borrowed $100 today (displayed as a positive value because you’re receiving funds), you would have to pay back the loan in the future, which would be displayed as a negative value. The absolute value function is just letting Excel know that if a negative value is returned, it should display the positive value so that it is easier for users to understand.

You will also have to input the current year, years until retirement, number of years in retirement, average annual rate of return that can be earned, average annual inflation, and money you want left over. Remember this is a lot of estimating, but you can change the inputs at any time to see the impact on your retirement portfolio.

If you would like to have any money left over when you die, that amount is entered in cell D10 in the input tab. Maybe you want to leave money to your children, grandchildren or other family members. On the input page to the right, you will see a figure called the real rate of return in cell G7. The real rate of return calculation is:

=((1+$D$7)/(1+$D$8))-1

Essentially, this formula adds one to the average annual interest rate that you estimated to earn once in retirement (rate as a decimal) and then divides by the sum of one plus the estimated inflation rate (as a decimal), then subtracts one. The real rate of return represents the actual rate of return you would earn after adjusting for inflation.

Schedule Tab

Once you have input everything on the input tab that is highlighted in yellow, the withdrawal schedule will be calculated for you on the next tab. The first year of retirement in cell B3 is calculated using this formula:

=((input!$G$3)+(input!$D$5))

The formula is adding two of the figures entered on the input tab: the current year figure and the number of years until retirement. You may notice the reference to the other tab is displayed as “input!” This lets Excel know to pull figures from another tab. The dollar signs that are used around the cell references make sure that the formula does not change if accidentally dragged to other cells. There are three kinds of cell references: absolute, relative and mixed. An absolute cell reference keeps the formula the same even if you copy, move or drag the formula. We have used an absolute cell reference in the formula above. A relative cell reference would be displayed as “G3.” If you move or copy the formula the reference changes by the same number of rows and columns as it was moved. A mixed cell reference would look like “G$3”. This means that the column reference would adjust if moved, but not the row reference.

Each year after the first year has a different formula in the cell. In cell B4, you will see the formula:

=IF(input!$D$6<$A4, 0, ($B3)+1)

This formula is essentially saying that if the value on the input page in cell D6 is less than the contents of cell A4, the formula should return a zero. If the value on the input page in cell D6 is greater than the contents of cell A4, then the formula should return a value that adds one the contents of cell B3. The IF function formula is:

IF(logical_test, [value_if_true], [value_if_false])

The IF function comes in handy here because not all users will input the same value for number of years in retirement. The spreadsheet should not keep calculating 50 years into retirement if you only input a value of 30.

The first year of retirement shows the beginning balance that was calculated on the input tab. This is the estimated amount you will have going into retirement. In cell C3 on the schedule tab you will see that it is set to equal the amount calculated on the input tab in cell D9 and represents how much money you will have when you retire.

The first year withdrawal amount is calculated in cell D3 using the formula:

=PMT((input!$G$7), (input!$D$6), $C$3, ((input!$D$10)*(-1)), 1)

This is a formula to calculate a particular payment. The formula requires:

=PMT(rate, number of periods, present value, future value, type)

The rate refers to the real rate of return located in cell G7 on the input tab. The number of periods refers to the number of periods you input as your estimated number of years in retirement (cell D6 on the input tab). The present value amount is the beginning-of-period amount that can be found in cell C3 on the schedule tab.

The future value figure is the amount that you want to have left over when you die, which is located on the input tab in cell D10. This figure is multiplied by negative one because of the concept explained above regarding the present value formula. We call it the cash flow sign convention. In this case the present value, or initial investment amount, is a positive value, so the calculation is assuming that it is a cash inflow (a loan). Since the present value (initial retirement balance) is positive, the future value figure would have to be a negative value. I multiplied the desired remainder, or future value, by negative one in order for the function to return the proper ending value. There is one thing to keep in mind. For example, if you entered that you would like to have $100,000 left over in your retirement account, the ending value at the end of retirement will be much higher than $100,000. This is because of inflation. If you calculate (1+estimated inflation as a decimal) with an exponent value of your estimated years in retirement multiplied by your desired ending value, it should match the ending value displayed on the schedule page.

Continuing with the payment (PMT) calculation above, “type” refers to whether contributions or withdrawals will be taken in the beginning or end of the year. As mentioned above, one means beginning-of-period withdrawal, zero means end-of-period withdrawal. This calculator assumes you will be taking beginning-of-period withdrawals.

This is where our calculator differs from many other retirement calculators or models. Typically, the recommendation is to pick an initial withdrawal amount in your first year of retirement and then grow that amount each year based on inflation. The goal here is instead to show you what you are ABLE to withdraw based on the inputs. This way you can tell if your current retirement account will be sufficient enough to support you through retirement.

The next column (E) addresses the earnings in the retirement account. The formula is:

=((($C3)+($D3))*(input!$D$7))

This formula adds the beginning balance amount to the withdrawal amount. Since the withdrawal amount is displayed as a negative, it is correct to add the two figures together. Then the difference is multiplied by the expected average rate of return in retirement that you inserted on the input tab in cell D7.

The remaining balance is displayed in column F. You will see this formula in cell F3:

=(($C3+$D3)+$E3)

Here we adds the beginning balance to the withdrawal amount. Again, since the withdrawal amount is negative, the formula is essentially subtracting the withdrawal amount from the beginning balance. Then the formula adds the amount calculated as earnings for the year (column E).

The last column on the spreadsheet displays the withdrawal percentage. The formula you will see in column G3 is:

=IF($A3<=(input!$D$6), ABS(($D3/$C3)),0)

The figure is calculated using the IF function again. The formula essentially states: If the contents in cell A3 is less than or equal to the value on the input tab in cell D6, calculate the absolute value of the withdrawal amount for the year divided by the beginning balance, otherwise input the value zero. We used this formula because, again, not all investors will estimate the same number of years in retirement. We want the withdrawal rate to continue calculating if the value in column A is less than the number of years the user expects to be in retirement for.

The formulas in rows after row 3 are different, since row 3 is the initial row. In row 4, the year is calculated (cell B4) using the formula:

=IF(input!$D$6<$A4, 0, ($B3)+1)

Essentially, if the value on the input tab in cell D6 is less than the value in cell A4, the formula will return the value zero, otherwise it will add one to the value in cell B3. As long as the investor expects to remain in retirement for a number of years that is greater than the numerical value in column A, the year will be calculated.

The beginning balance in row 4 (cell C4) is calculated using the formula:

=IF(($B4=0), 0, F3)

If the value in cell B4 (the year column) equals zero, then the beginning balance should also display a zero; if not, then the beginning balance should equal the ending balance of the previous year, the contents of cell F3 in this example. Again, this formula is input simply so a beginning balance is not calculated in a year that an investor doesn’t expect to be alive.

The withdrawal amount in cell D4 contains the formula:

=($D3)*(1+(input!$D$8))

The withdrawal amount is calculated by taking the previous year’s withdrawal amount and multiplying that by the estimated inflation rate.

The earnings, remaining amount and withdrawal amounts are all calculated using the formulas as described above throughout the whole worksheet.

Chart Tab

The third tab of the retirement withdrawal spreadsheet provides a chart displaying your remaining equity throughout retirement. You will see your remaining equity value on the y-axis. On the x-axis is each year in retirement ranging out to 50 years. If you input an expected time in retirement of 30 years, the last 20 years will show the remaining equity at $0.00. This was provided simply as a graphic approach to viewing your remaining equity value in retirement over time.

Conclusion

Overall, this spreadsheet is designed to assist you in estimating your ability to withdrawal money from your retirement fund, based on a specific set of inputs. Planning for retirement is an essential part of investing.

Those currently in retirement do not need to use the part of the spreadsheet that estimates what you will have when you retire. In cell D18, next to the box that says “Funds available at retirement,” simply type in your retirement fund balance. In cell D15, next to the box asking you how many years are remaining till your retirement, simply type in 0. By doing this, you can view the withdrawal schedule year to year without estimating your initial retirement balance.

Remember this is simply a tool to assist in retirement withdrawal estimation. We hope it gets you closer to the twin goals of having enough savings to provide living needs throughout your entire lifetime, no matter the market conditions, and having savings that provide enough financial resources to do the things you want to do during retirement.