Author Topic: How To Evaluate The Pension Versus Lump Sum Decision  (Read 2885 times)

forummm

  • Walrus Stache
  • *******
  • Posts: 7357
  • Senior Mustachian
How To Evaluate The Pension Versus Lump Sum Decision
« on: July 22, 2015, 01:16:49 PM »
Since someone seems to ask this question every couple weeks, here's a good article:

How To Evaluate The Pension Versus Lump Sum Decision, And Strategies For Maximization

https://www.kitces.com/blog/how-to-evaluate-the-pension-versus-lump-sum-decision-and-strategies-for-maximization/

MDM

  • Walrus Stache
  • *******
  • Posts: 9247
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #1 on: July 22, 2015, 02:23:45 PM »
Good article indeed - thanks!

One can duplicate Kitces' calculations with the Excel RATE function.  E.g., see the 'Misc. calcs' tab in the case study spreadsheet, cells G6-G12. 

The numbers below reflect the first example ("...Jerry would have had to invest the lump sum at a 4.2% average annual growth rate...") in the article:
         Rate
PV    600000
PMT  -50000
i       4.2%
n      17
freq  1
type  0
FV     0

deborah

  • Walrus Stache
  • *******
  • Posts: 8284
  • Location: Australia or another awesome place
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #2 on: July 22, 2015, 04:13:04 PM »
It WAS a good article but it missed my situation. My pension includes inflation (so each year it rises according to CPI). Not only that, but it includes two forms of survivor benefits - the one specified (the survivor gets the same pension, but the pension is always lower than for no survivor) or the pension is lower for the survivor (65%) but it is the same as it would have been with no survivor.

It would be interesting to see those figures.

MDM

  • Walrus Stache
  • *******
  • Posts: 9247
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #3 on: July 22, 2015, 06:20:58 PM »
It WAS a good article but it missed my situation. My pension includes inflation (so each year it rises according to CPI). Not only that, but it includes two forms of survivor benefits - the one specified (the survivor gets the same pension, but the pension is always lower than for no survivor)
One step at a time.... :)

IIRC from other posts you know your way around a spreadsheet.  Copy the table below, then paste into cell A82.  Adjust the first 3 and fifth numbers to frame the question, then use Excel's What-If Analysis to make the last cell equal zero by adjusting the Annual interest rate.

This shows the article example, except with a 3%/yr increase to the annual pension payments.  Instead of 4.2%, one gets 6.9% as the hurdle rate.  Not surprisingly, a COLA pension is "worth more" than a flat pension.

Lump sum600000
Initial annual payment50000
Annual Inflation rate0.03
Annual Interest rate0.0691033288940955
Number of years17
type0
Lump sum FV=FV($B$85,$B$86,0,-B82,$B$87)
Payment FV=B83*IF(B84<>B85,((1+B84)^B86-(1+B85)^B86)/(B84-B85),B86*(1+B85)^B86/(1+B84))
Difference=B88-B89


Some background (see https://en.wikipedia.org/wiki/Time_value_of_money#Formula_table for variable descriptions):

For a lump sum, F=P*(1+i)^n
For a repeating, constant, payment, F=A * ((1+i)^n-1)/i

Equating those, A * ((1+i)^n-1)/i = P*(1+i)^n

Solving for A gets the mortgage payment formula, A=P * i * (1+i)^n/((1+i)^n-1)

Because the interest rate appears by itself and in the (1+i)^n term, there is no algebraic solution for i and one must use “guess and check”.  That is what the Excel RATE function does.


For an increasing repeating payment, F=D * ((1+g)^n-(1+i)^n)/(g-i)

Similarly, one can set D * ((1+g)^n-(1+i)^n)/(g-i) = P*(1+i)^n and use Excel's Goal Seek to do the numerical solution for the interest rate.
« Last Edit: July 22, 2015, 07:57:45 PM by MDM »

deborah

  • Walrus Stache
  • *******
  • Posts: 8284
  • Location: Australia or another awesome place
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #4 on: July 22, 2015, 07:01:03 PM »
Thanks very much for that. I assume it was <> rather than [] in the formula in B89?

MDM

  • Walrus Stache
  • *******
  • Posts: 9247
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #5 on: July 22, 2015, 07:14:01 PM »
Thanks very much for that. I assume it was <> rather than [] in the formula in B89?
Yes - side effect of table formatting - fixed in the original, thanks.

MDM

  • Walrus Stache
  • *******
  • Posts: 9247
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #6 on: July 22, 2015, 08:20:19 PM »
It WAS a good article but it missed my situation. My pension includes inflation (so each year it rises according to CPI). Not only that, but it includes two forms of survivor benefits - the one specified (the survivor gets the same pension, but the pension is always lower than for no survivor) or the pension is lower for the survivor (65%) but it is the same as it would have been with no survivor.
One step at a time.... :)
Ok, here's the whole thing.  Copy the table below, then paste into cell A82.  Adjust the first six numbers to frame the question, then use Excel's Goal Seek to make the last cell equal zero by adjusting the Annual interest rate.

Lump sum600000
Initial annual payment50000
Annual pension COLA0.03
Number of years at 100% payment17
Fraction of payment to survivor0.5
Number of years at reduced payment8
Annual Interest rate0.084520529408746<- Let Excel Goal Seek adjust this…
Lump sum FV=FV($B$88,$B$85+$B$87,0,-B82)
Payment FV=IF(B84<>B88,B83*((1+B84)^B85-(1+B88)^B85)/(B84-B88)*(1+B88)^B87+B83*B86*(1+B84)^B85*((1+B84)^B87-(1+B88)^B87)/(B84-B88),B83*B85*(1+B88)^B85/(1+B84)*(1+B88)^B87+B83*(1+B84)^B85*B86*B87*(1+B88)^B87/(1+B84))
Difference=B89-B90<- …to drive this to zero.

Calculations are similar to those described in http://forum.mrmoneymustache.com/investor-alley/how-to-evaluate-the-pension-versus-lump-sum-decision/msg740926/#msg740926

Additions for this post:
  - The Future Value of the 100% payments after those years then compounds for the years of reduced payments
  - Reduced payments start at the given fraction of the COLA-increased amount, then continue in COLA fashion.

With
  m = number of years at reduced payments
  z  = fraction of payment to survivor
the equation for g<>i is
F=D * ((1+g)^n-(1+i)^n)/(g-i)*(1+i)^m + D*z*(1+g)^n*((1+g)^m-(1+i)^m)/(g-i)

deborah

  • Walrus Stache
  • *******
  • Posts: 8284
  • Location: Australia or another awesome place
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #7 on: July 22, 2015, 09:14:29 PM »
Great - I knew that I was on a good wicket with my pension - the lump sum looks very small by comparison.

Zamboni

  • Handlebar Stache
  • *****
  • Posts: 2217
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #8 on: July 22, 2015, 09:34:02 PM »
Yes, I did a similar calculation when offered a lump sum from a previous employer a few months ago. While it seemed like a large dollar amount, the math showed that I was much, much better off not taking it (even if I die, my heirs get a 5 year payout. If I live, I end up way ahead by not taking the lump sum.) Their advertising for taking it now was so slick that I figured it was a bad deal just based upon that, but the math confirmed my suspicion.

MDM

  • Walrus Stache
  • *******
  • Posts: 9247
Re: How To Evaluate The Pension Versus Lump Sum Decision
« Reply #9 on: July 23, 2015, 12:22:37 AM »
Added the Excel snippet shown above to the 'Misc. calcs' tab in the case study spreadsheet.