Do you have a traditional defined benefit pension from either a current or previous employer? Oooh lucky you! You are one of a shrinking group.

It’s still relatively common in the public sector, but definitely a fast disappearing benefit in the corporate world. Gone are the days that my father enjoyed from retiring on a pension earned through 40 years with the same employer.

With your own personal finances it can be useful to put a value on a pension, and I certainly include my pensions in my net worth calculation.

## Confusion

But across the blogosphere there seems to be considerable confusion about how to give your pension a value, and I can tell you that there are few things that upset an actuary more than wrongly calculating the value of a pension. (Some of those things might include aggressively optimistic assumptions, unfoundedly authoritative assertions about the future, lack of humility in financial forecasts etc etc).

We’re going to look at a method that uses no math and just a spreadsheet with a simple function. *What could be easier? (And more accurate)*

## What is a pension?

A pension is a guaranteed set of payments. Usually you receive a monthly check, and this lasts until you die. You may also have an option that a surviving spouse will receive a pension on your death. Another word for a pension is an annuity – same thing.

Your own pension plan will have rules about when you can start to receive the first payment. For many plans that is age 65, but you may have the option to take it earlier. You will have to dig through the plan documents or ask you HR department the particular rules that apply to you.

Some pension plans will give you the option of receiving a single lump sum instead of a pension, and some plans offer a lump sum in addition to a pension.

Most pensions are fixed in value, but some pensions will increase in year in line with price inflation (or some other measure). Usually corporate pensions are fixed, and pensions from public plans have some kind of annual increase (also called a COLA – Cost of Living Adjustment).

## How to value?

There is some uncertainty inherent in a pension – mainly how long you will live! So the first thing we need to assume is how long the pension payments will last. I would go to the Society of Actuaries longevity calculator, input your details, and then look at the maximum age at which you might live with a 10% chance of surviving. That should be well into your 90’s. See below for my estimate, which is age 100.

This is an important step and one you should not guess at. My experience is that people massively underestimate their life expectancy. It’s no good comparing yourself to a relative in a previous generation since life expectancy is improving each generation.

You then need a statement from your plan that gives your expected benefits at retirement. I’m going to assume here that my annual pension is $30,000 per year starting at age 65.

I’m also going to ignore any potential spouse pension here, let’s keep things simple, but a spouse pension on death is a valuable benefit.

The basic principle is we are going to find the present value of this stream of payments. In other words this is the capital value now that if invested today, and earns the discount rate, will be sufficient to meet all future payments.

*Want to get regular updates for new posts? Want to be dripfed actuarial goodness in your inbox? Sign up to the email list through the menu. Also follow me on Twitter @actuaryonfire Don’t bother looking for a Facebook page, I haven’t figured that out yet.*

# Using the spreadsheet

There is a nifty function in your favorite spreadsheet called ** XNPV**. This will take a series of payments on different specified dates and calculate the present value.

The first thing is to specify when the payments occur.

The first date is today and I’m age 45. I have then put all the subsequent dates from when the pension starts at age 65. Except I have actually put the date 6 months after my birthday. My 65th birthday is 3/31/2037 and six months later is 9/30/2037.

This is an actuary trick to put in the mid-point of a year’s worth of pension payments. While I am 65 I will get 12 monthly payments. Instead of having the hassle of putting 12 different monthly payments in my spreadsheet I am going to put a single payment at the mid-point, which is six months after my birthday.

It’s not totally accurate, but they put a team on the moon with no less sophistication in calculation.

Also note that I’ve just done the spreadsheet going out to age 70 so you can see everything. In reality I would have done it going out to age 100.

I now need to put in the pension amounts.

My pension is $30k a year, so you can see $30k every year. And remember payments start 6 months after my 65th birthday.

## Using XNPV

We’re going to use a nifty function called XNPV. This just takes all these payments at these dates and finds the present value at the first date. In other words we are finding the capital amount required today in order to fund all these future payments.

Above is the detail of how to use this. Above I’ve shown an interest rate of 2% and then the other requirements are the stream of dates and stream of payments.

## Discount rate

Your pension is a promise from your current/previous employer and it has a value. That value stands irrespective of your own particular investment strategy. Whether you are a risky or conservative investor the value of this pension should not change. So don’t discount it by your own expected return. I don’t care if you believe that you will earn 8% a year on your portfolio, that’s not relevant.

The pension promise is essentially like a bond that your employer has issued. Instead of receiving regular coupon payments you are receiving pension payments. The promise is secured with assets in the fund, funding legislation, the strength of the employer and ultimately the Government ‘lifeboat’ fund – The Pension Benefit Guaranty Corp. These all contribute to the value and so a high quality bond yield would be appropriate. Currently long duration high quality bonds are yielding around 4%. So let’s use 4% as our discount rate.

## Results

For a 4% discount rate we get the answer $74,857. Note that this is for pension payments only up to age 70. It would be a lot more if we extended it to age 100.

Now if your pension has a COLA then you can either increase the $30k a year by your assumed annual increase rate, or a nice trick is to simply decrease your discount rate.

Let’s assume that your pension is increasing with inflation at 2% per year. We will now decrease our 4% discount rate by 2%, to give a net discount of 2%.

It’s increased the value to $115,516. That’s a big increase and reflects the additional value of an inflation linked pension – lucky you!

## Wrap-up

So whadidwe do there?

- Found our life expectancy with a groovy actuary tool
- Dug out our pension details; when it starts and how much
- Got our favorite spreadsheet and put in the dates and amounts. Remembering to use 6 months after our birthday
- Used XNPV to cook up the goods!

*So what did you think? It’s a bit of a dry topic but I wanted to address it since there seems to be quite a lot of confusion around on this subject. Let me know what else you want to hear about and let me know if you implemented this method. Good luck!*

**Technical notes:** is this how an actuary *really* does it? Nooooo! It’s a lot more complicated. At each year we need to figure out the probability of certain events happening, e.g. dying, a spouse payment coming into payment, spouse dying etc etc. There are lots of contingent events going on and the present value becomes a huuuuge probabilistic expected value calculation. In addition we use a full yield curve and not a spot discount rate. But this will give you an excellent working value.

Interesting! And I was using the commuted value all along. I will have to give this a try. Thanks for sharing!

Great! Let me know if you have any problems.

Oh hey this is super relevant. I am one of the lucky few to have a pension still. Mine vested this year, so I can take it with me or leave it sit and get it when I’m old. What do you think? Should I take the lump sum (valued at ~$9k) or leave it sit and hope the employer still has it in 35 years?

Oohh nice work! If you’re still employed then you have to leave it there, you don’t have a choice. If you have left that employer then you can take it with you.

First thing is I would contact HR and ask them “Is the plan a cash balance plan? If it is tell me the interest crediting rate.” if it’s a cash balance plan then the lump sum will get credited with “interest” from now until retirement. For most plans that interest rate is crappy, for others it can be quite rich. So you need to check that out first.

LMK how you get on…

Thanks for visiting

This is very useful, I always had some problem evaluating the value of my pension, especially when negotiating salary for a new job. I approximated the yearly contributions my company were doing on my behalf and added that to the salary but this is much better.

That’s a good point – the contributions that your employer make could be quite arbitrary and not really reflect the value of the benefit. There are a lot of really arcane rules about contributions, and you then couple that with legislative meddling from Congress and you get a total pile of crap that does not add up.

If I was going for a new job and they were offering a DB plan I would put a pretty high value on that. But I am quite risk averse so I like it when other people have to take investment risk on my behalf and I get the benefit!

Love the XIRR function! And love that you don’t get hung up too much on calculating it exactly given how much changing discount rate assumption moved the needle. False precision is a killer.

This exact same analysis works for social security benefits too. Cheers!

Oh yes XIRR the crazy cousin of XNPV!

I love it too. You’re right the discount rate makes such a big impact and something that people argue over so much. The had been blood spilt between actuaries on that topic.

Thanks for stopping by and let me know if you do some good PV posts!

Thanks. Will do. I have a few in draft form but haven’t finished just yet.

I don’t have a pension but I always wondered how to evaluate a lottery prize say 2M over 25 yrs v lump

Gasem – an uncharacteristically short comment! 😉

I haven’t had much chance to think about your diversification issues. The trouble is I don’t think there is a short reply and needs some considered thought.

As always, I appreciate your input.

You are an expert, so I am very interested in your reasoned opinion as opposed to the typical boilerplate.

I wish I had found this a couple of months ago. We just had an option to cash out a pension and did our own math. We took a similar approach to what you laid out above but…the way we laid it out was a little more complicated. I’m glad you set up this resource for those that have the same decision in the future.

Deciding whether to take a cash out is perhaps the most common question and I should address it in another post. Thanks for visiting Jason.

Such a simple way to reasonably value a pension. I love making quick Excel tables using NPV or XIRR! It is how I have made a number of investment planning decisions over the years.

As interest rates have fallen over the past decade it has been interesting to see how companies with large unfunded pension balances got hit by higher funding requirements due to the lower discount rate. It has put a lot of companies that have legacy pensions in dire straits. But hopefully all those pensioners continue to get their money!

RtR – you’re right these are great functions and really easy to use.

I wouldn’t worry unduly about corporate pension plans. The typical plan is around 85% funded and there is a backstop Govt fund (PBGC). But if you are a high earner you won’t be fully covered.

However public pensions are a different matter…