Tuesday, December 16, 2014

Milevsky Probability of Portfolio Ruin Calculator

Thanks to Dirk Cotton for the shout-out in his December 5 post, Think Like a Bayesian Pig.   Another excellent post from Mr. Cotton, and the comments that follow his post are also very worthwhile reading. 

At the bottom of his post, Dirk provides a link to a 2005 paper by Moshe A. Milevsky and Chris Robinson, and in the comment section, he provides a link to the Milevsky Probabililty of Portfolio Ruin Calculator.  

I must admit that the work done by Milevsky and Robinson was unfamiliar to me.  So, thanks to Dirk for bringing it to my attention.   While the paper is somewhat technical, the Probability of Ruin Calculator associated with the paper is not all that complicated,  and it turns out to be a very powerful tool in my opinion.  I was so impressed with it that I am including it in the "Other Calculators/Tools" section of this website.  I believe that that the Milevsky tool can provide retirees and financial planners with another valid point of reference (in addition to the approach recommended in this website and other approaches) to be used in developing a spending budget, particularly if the retiree has no other fixed dollar sources of retirement income (like fixed dollar pensions/annuities) or significant bequest motives.   The remainder of this post will discuss the Milevsky/Robinson approach and how I think it can best be used for budgeting in retirement under certain circumstances.

Milevsky/Robinson Approach

As opposed to the deterministic approach for investment returns and expected payout periods baked into the simple spreadsheet provided on this website,  Milevsky and Robinson advocate an approach that successfully reflects the fact that both investment rates of return and the time until death are stochastic in nature (have certain probability distributions).   By making certain assumptions about the probability distributions,  they develop probabilities of sustainable real dollar spending rates for various combinations of life expectancy, expected real rates of return and portfolio risk (measured by the standard deviation of the distribution of returns).

When using Milevsky's Probability of Portfolio Ruin Calculator, the user should note that the expected portfolio rate of return (an arithmetic mean over the remaining lifetime) input is a real (after-inflation) percentage.  Thus, the expected rate of return comparable to the real rate recommended in this website is 2% (which is approximately the real rate implied by a 5% per annum nominal investment return and 3% per annum inflation).   Also note that the spreadsheet anticipates inputting a whole number of years remaining in retirement equal to the retiree's remaining life expectancy.   Inputting expected rate of return, portfolio standard deviation, withdrawal rate (a percentage of accumulated savings at the current age with such product increasing by inflation in subsequent years) and remaining life expectancy at the current age, the spreadsheet produces a probability of ruin (running out of money prior to death) and it's complement (not running out of money prior to death). 

Keeping in mind that since there is approximately a 50% probability of outliving one's life expectancy, any probability of ruin less than 50% means that the retiree is more likely than not to die before running out of money (assuming no future changes in spending occur).  A probability of ruin of 25% means that the retiree is about three times more likely (75%/25%) than not to die before running out of money (assuming no future change in spending occur).   Since many retirees are almost as worried about not spending enough as those who are worried about spending too much, it does not bother me to develop a spending budget based on a 25% probability of ruin.   As I have previously indicated in this website,  developing a spending budget for retirement is a "balancing act."   It is also important to note that the term "ruin" here may be a misnomer and may more appropriately be considered as a probability that real dollar spending may need to be reduced in the very later years of life as a result of living well beyond one's life expectancy.   This possibility may not be a disaster, but may actually be more consistent with studies that show declining spending needs at older ages.  

I compared results of the Milevsky's Probability of Portfolio Ruin Calculator (using assumptions described below) with the results of the simple spreadsheet in this website (Excluding Social Security V 2.0) using the recommended assumptions.   If I input a 2% real expected rate of return in Milevsky's spreadsheet, 5% standard deviation, male life expectancy based on the average of the 2010 Social Security and 2012 Individual Annuity Mortality Tables available on the Society of Actuaries  website (a link to which resides in the "Other Calculators/Tools" section of this website) and solve for the withdrawal rate that produces approximately a 25% probability of "ruin", I come very close to the withdrawal rates produced using the Excluding Social Security V 2.0 spreadsheet and the recommended assumptions at most ages.   There is some significant deviation at the very older ages, as I recommend using life expectancy if age plus life expectancy is greater than 95.  When I reach my late 80's I may switch to the Milevsky approach depending on how concerned I become at that point about outliving my savings.  Females, who have longer life expectancy, may also wish to look at the Milevsky approach when they reach their mid-80s.

Those of you out there who play with this spreadsheet may find it of interest that inputting a 4.4% withdrawal rate and 20-year life expectancy and an 8% withdrawal rate and 9 year life expectancy will produce about a 25% probability of ruin for each of the following combinations of expected real investment return/standard deviations:  2%/5%, 3%/11.5%, 4%/15.5%, 5%/19%, 6%/21.7% and 7%/24.5%.   This result shows that achieving higher real rates of return by taking on more risk may not increase your retirement budget.

How to best use the Milevsky Spreadsheet (In My Opinion)

As alluded to above, while the Milevsky spreadsheet is a powerful tool that provides retirees with another data point in planning for retirement, it does have some weaknesses.  It  does not, for example, coordinate the spending budget with fixed immediate or deferred annuities/pensions and it does not reflect bequest motives.

In his post, Dirk Cotton says that the Probability of Ruin Calculator is also an actuarial approach.  I'm going to mildly object to this claim.  I would say that it may be used as part of an actuarial approach, but by itself, it is more like an alternative Safe Withdrawal Rate generator, particularly if it is only used once at the retiree's initial retirement age.   I understand that Dirk is not actually suggesting this, but to be clear, to be considered an actuarial approach, it should involve an annual re-measurement (although not necessarily annual redetermination of the actual budget amount).  This is how I would use the Milevsky approach if I did not have other fixed dollar retirement income sources and bequest motives (and I wanted another data point to consider):  Step 1:  Calculate the withdrawal rate at initial retirement consistent with a 25% probability of ruin, a 2% expected investment return, a 5% standard deviation and a reasonable life expectancy for me (not necessarily based on the mortality of individuals who purchase life annuities).  Step 2:  Multiply the withdrawal rate determined in  Step 1 by my accumulated savings at initial retirement.  This is my spending budget for year 1.  Step 3:  In year two, increase the dollar amount determined in the previous year by the increase in inflation over the previous year.  Revisit the spreadsheet using an updated life expectancy (but generally the same other assumptions and probability of ruin used in the first year) and multiply the resulting withdrawal rate by an update of actual accumulated savings (i.e., current year's assets).  If the  spending budget for the previous year is within 10% of the product of the updated withdrawal percentage and updated assets,  just stay with the spending budget for the previous year increased with inflation.  If not, use the corridor value.  Step 4:  Repeat Step  3 each year.    Note that this basically the same approach (involving the same smoothing algorithm) recommended for the Actuarial Approach in this website.

Sunday, December 7, 2014

Demystifying the Spending Spreadsheets on This Website

From time to time, we get questions about our spending spreadsheets.  In an effort to provide retirees who visit this site with a higher level of comfort with the spreadsheets, this post will attempt to explain them in more detail.

We provide two simple Excel spreadsheets in this website for the purpose of helping you develop your annual spending budget in retirement as part of what we call “The Actuarial Approach.”  The spreadsheets are located in the “Articles/Spreadsheet” section.  The first one (Excluding Social Security V 2.0) generally applies if you are currently receiving Social Security benefits and develops a total spending amount (from accumulated savings and any immediate or deferred annuity or pension income for which you may be eligible).  To obtain your total spending budget for the year, you take the result from this spreadsheet (or a smoothed result based on our recommended smoothing algorithm or some other smoothing algorithm) and add income you expect to receive from other sources in the next year such as your Social Security benefit, other inflation-indexed annuity/pension benefits, expected earnings from employment, etc.

The second spreadsheet (Social Security Bridge) applies if you are not currently receiving Social Security benefits but would like to coordinate your current retirement spending with Social Security benefits you expect to receive in the future. 

The Total Spendable Amount shown in the Results section of the Input tab is the answer to the mathematic problem, "What total spendable amount (from accumulated savings and annuities) may be spent in the current year, to be increased each subsequent year by a constant percentage so that accumulated assets will exactly equal the amount desired to be left to heirs at the end of the expected payout period?"

Please be patient when downloading the Excel spreadsheets.  Sometimes it can take a while. If you are requested to provide a password, simply click "cancel" and the spreadsheet should appear.

If you are having trouble seeing all the cells in the spreadsheet once it has downloaded (or you want to see a year-by-year runout of your accumulated savings based on the input items), be sure to maximize the spreadsheet window (by clicking on the “maximize” box in the top right-hand corner of the spreadsheet).

The Excluding Social Security V 2.0 has three tabs in the spreadsheet.  The Social Security Bridge spreadsheet only has two tabs.   As noted above, if you can’t see the tabs at the bottom of the spreadsheets, you need to maximize the spreadsheet window.   After you do this, you should see the tabs labeled, “Input”, “Runout” and “Inflation-adjusted Runout.”  The Social Security Bridge spreadsheet does not have an inflation-adjusted runout tab.

The input sheet allows you to enter your data and assumptions used in the calculations.  Once you enter these items, results are shown in the bottom portion of the spreadsheet.

The Runout tab shows results in nominal (non-inflation-indexed) dollars.  The first year of the Runout spreadsheet shows the beginning of year balance of accumulated savings that you entered, the amount to be withdrawn (payment) from accumulated savings for that year, expected investment return (interest) on the beginning of year balance, any immediate life annuity income for the year, the total spendable amount (from accumulated savings and pension/annuity) for the year and the accumulated savings withdrawal as a percentage of the beginning of year balance.  Similar amounts are shown for each subsequent year based on the expected payout period you entered in the input sheet.  The run-out is based on the assumptions discussed at the top of the spreadsheet.   If you want, you can check to see that the end of year accumulated savings is equal to the beginning of year accumulated savings minus the amount withdrawn during the year plus the expected investment return for the year.  Note that the Runout spreadsheet is somewhat self-checking.  At the end of the expected payout period you entered, the end of year balance for that year should exactly match the amount you entered in the input spreadsheet for the “desired amount of savings to be left at death.” 

The inflation-adjusted Runout sheet (in Excluding Social Security V 2.0 only) adjusts the expected year by year results shown in the Runout sheet for inflation based on the assumption for inflation that you input as the last input item on the input sheet.  If you input the same rate for “annual desired increase in payments” and “expected annual rate of inflation”, the total inflation-adjusted spendable amount shown in Column L should remain the same throughout the expected payment period. 

Below are screen shots from the Excluding Social Security V 2.0 spreadsheet showing the input, and its two Runout sheets.  The data and assumptions used are shown in the Input sheet. 

As discussed above, the Runout sheet for this example shows an end of year accumulated savings balance of $10,000 at the end of the 30th year (the amount inputted for desired savings remaining at death), and the Inflation-Adjusted Runout sheet shows a total inflation adjusted spending amount that remains constant throughout the 30-year expected payment period of $40,259 per year. 

(click to enlarge)

(click to enlarge)

Inflation-adjusted Runout
(click to enlarge)

Wednesday, December 3, 2014

Revisiting Recommended Assumptions

As discussed in our posts of July 12, 2013 and October 11, 2013, and in the Journal of Personal Finance paper (originally written in February, 2014), we have been recommending the following assumptions for annual budget determinations under the Actuarial Approach: 
  • Expected annual rate of return on savings: 5% (a nominal interest rate) 
  • Annual desired increase in payments/inflation: 3%,
  • Expected payout period (in years): Until age 95 or life expectancy if longer.
In the absence of significant changes in interest rates, we recommend continued use of these assumptions for 2015 retiree budget determinations.

The remainder of this post will discuss the rationale for continuing these recommended assumptions.

Investment Return and Inflation Assumptions

In prior discussions, I have tied the expected future nominal expected rate of investment return on accumulated assets to the approximate interest rate "baked into" immediate annuity purchases.  For this purpose, I have used the immediate annuity purchase rates made available on the Income Solutions website.

According to this website, as of November 26, 2014, a premium of $100,000 could purchase a monthly immediate annuity of $571 for a 65 year old male and $544 for a 65 year old female.  Assuming a life expectancy at age 65 of 22.9 years for a 65-year old male and 24.9 years for a 65-year old female (based on the Society of Actuaries 2012 Individual Annuity Mortality Table with 1% per year mortality improvement, a link to which is available in the "Other Calculators/Tools" section of this website), I have determined that the interest rate inherent in these annuity purchase rates is about 4.3%.  This rate is slightly lower than the 4.6% rate I approximated last February using the same approach.  This change may be due to the use of more conservative mortality assumptions, declining interest rates or some combination of the two.

While I have no problem if a retiree wants to use an investment return assumption lower than 5% (particularly if the retiree is heavily invested in fixed income securities), I continue to believe that an annual 5% nominal return can be reasonably justified by retirees with relatively diversified investment portfolios.  I would caution, however, against assuming higher nominal (or real) investment returns based on increased investment in equities as those strategies carry more risk that should be reflected in the assumption.

Consistent with Wade Pfau's research, I believe budgeting should assume a real rate of return of about 2% per annum, so I am retaining the 3% per annum inflation assumption combined with the 5% investment return assumption as my recommended economic assumptions for 2015 budgeting.   


As discussed above, the Society of Actuaries has released several new mortality tables which show significant mortality improvement has taken place in recent years.  For example, life expectancy for a 65-year old male has increased by about a year under the new Individual Annuity Mortality Table (with 1% annual improvement).  Under this revised table, a 65-year old male has about a 24% probability of surviving until age 95, while this probability is about 33% for a 65-year old female.  Note, however, that this new table is based on mortality experience for individuals who buy immediate annuities from insurance companies and presumably have better than average health.  By comparison, life expectancies under the 2010 Social Security tables (with 1% mortality improvement), with experience based on essentially the U.S. population are 17.6 years for 65-year old males and 20.4 years for 65-year old females, respectively.  While some argument can be made for increasing the "live to 95" assumption by a year (at least for females), I continue to believe that this assumption is reasonable.  Of course, if you are already in your late 80s, you need to look at longer possible payment periods.

If you are the rare retiree who has a good idea when you are going to die, feel free to use your knowledge in your budgeting.  If you are like most of us, you should plan to live longer than your life expectancy, at least relatively early in your retirement.   The problem with doing this is that this increases the probability that you will die with more (unspent) assets than you desired.  To some extent, this is simply the cost of not buying an annuity.

The chart below shows projected budget amounts (in inflation-adjusted dollars) for a 65-year old retiree with $600,000 in accumulated assets under the Actuarial Approach (and recommended smoothing) using two different approaches for determining the remaining payout period.  The first approach uses the retiree's life expectancy (based on the SoA 2012 Individual Annuity Table) while the second approach uses the "live until 95" approach recommended in this website.  Investments are assumed to earn 5% per annum, inflation is assumed to be 3% per annum and the retiree is assumed to spend exactly the budget amount each year.  This chart illustrates the problem with using one's life expectancy each year and having the misfortune? of surviving. 

(click to enlarge)