The Pension Series (Part 13): The Master Pension Value Calculator

“Do You Expect Me to Talk?”

This post is something of a mash-up. Everyone loves a good mash-up, or at least that’s what Mrs. Grumpus tells me before she tragically dances and sings around the kitchen. She’s a great singer, but an awful dancer. As a result, her mash-ups are unique. Personally, I hear the word “mash” and think about my favorite part of the brewing process. Some may think of whiskey, but that’s distilling.

In any case, for this article, I combined the pension case study of a reader and the hard work of a ChooseFI listener. We’ll call the reader “Ms. Money Penny” and the ChooseFI listener “Q”.  Money Penny is the secretary in the older Bond films, always flirting with that “big man-slut” (a Mrs. Grumpus term) Bond, and wishing she could go on adventures with him. I always got the feeling that Money Penny was bored and that she lived vicariously through the stories Bond would tell her. Boring is exactly how my reader Ms. Money Penny described her finances to me in her email. In this case, boring is a good thing … make that a great thing. Sticking with the analogy, if I were Bond, I might want to live my financial life vicariously through her. Ms. Money Penny, as you’ll find out, is in a really good place.


In all the Bond films, old and new, “Q” is the guy with gadgets. Not just any gadgets, but THE gadget that always gets Bond out of whatever pinch he’s in. Q’s made it all, from a mini-helicopter with missiles to an exploding pen, and a mini-SCUBA canister Bond could carry in the pocket of his tuxedo. It’s convenient that Q always knows what to invent in advance of Bond’s dilemma. If we only had his prescience!

Well, apparently the real-life Q does. He sent me a pension value calculator recently with several methods for calculating pension values embedded as formulas in separate tabs — including one of my methods. Plus, he stuck his own formula in which calculates the lump sum offer required to employ a 4% Safe Withdrawal Rate (SWR) immediately upon retirement. Thus, all one has to do is enter in the pertinent data in the first tab, and “poof” out pops five different values for a pension depending on the method used. Again, if I were Bond, Q just solved a major dilemma of mine before I even knew it existed.

I’m sure you all see where this is going, right? I have a reader who sent me her pension information with a request to help her analyze her options. And, I have a ChooseFI listener who sent me pension value calculator for analyzing pension value and lump sum offers. Normally, I’d tell you to do the math at this point, but there’s no need. It’s all done for us and embedded into a spreadsheet. All we need to do is fill in the information and see what we get.

The Set Up

Ms. Money Penny supplied me the following facts about her situation:

I’m a 62-year-old single female with longevity in my family. I will have a lump sum pension option at age 66. There’s no COLA or health care. It’s a solid company and well funded. I can take a lump sum of $386,826 or annuitize $24,264 annually for life. No survivor benefit to worry about.

In truth, Ms. Money Penny has approximately eight choices of annuity for which she attached this picture:

pension value calculator
Ms. Money Penny’s overwhelming number of choices

She also provided a good amount of background information, which provides a lot of context:

Here’s more info on my situation, my projected retirement age is 66.2 (Social Security (SS) full retirement age). SS at that age will be $2265/month, or $3067/month if I delay until 70. I haven’t decided when to take it. As of today, I have $550,000 in Vanguard 403b and IRA: 25% bonds, 42% large cap, 13% Mid/Small cap, 19% International stock, 1% cash. I plan on fully funding 403b ($24,500) and Traditional IRA ($6,500) for four more years until retirement. I project a balance of about $750,000 in my retirement funds at my retirement age. When I retire I will sell my townhome in Colorado and retire to a condo in Florida. The plan is to not have a mortgage in retirement and that is very realistic.

Longevity is a risk factor for me.  My mother is still alive and will turn 96 next month. Maternal aunts lived to 90 and 101. When my mother dies, I’m looking at a small inheritance of $100,000. I’m earmarking that as my potential gap money between taking SS at 66 or postponing till 70. 

My health is great. I don’t drink or smoke so I’m looking at living to 95 as my financial planning goal. I purchased Long Term Care (LTC) insurance (currently $136/month with Transamerica) through a work group plan to augment my assets should I need assisted living. While I’m not a big fan of LTC, I am single with no dependents and the youngest of my siblings … so I’m trying to be responsible for myself as much as possible.

The other concern I have is Sequence of Return Risk (SRR) given the economic expansion we’ve enjoyed for a long time.  All good things gotta come to an end, and that end might correspond with my retirement date and the years beyond.

I’m looking to generate $60,000 year in retirement which is generous for me. I can get by on half of that if needed. I currently earn $80,000 but my take home is $40,000. From that take home I pay mortgage ($916) car payment ($300) fund the IRA and enjoy life.

Pre-Game Analysis

Let’s be honest, Ms. Money Penny is in a great position. Her hard work and clean living is about to pay off in four short years. There are a lot of great points in her story I’d love to delve further in-depth on, but for the sake of time, I’ll just cherry pick a few to highlight.

She pays herself first, which means she doesn’t count her savings into the 403b as take-home pay. Thus, she’s disciplined herself to live on less while saving more and doesn’t appear to suffer in the least from it. LTC insurance is an obvious and smart choice for a single person in their 60s with no kids or siblings to take care of them as they age. The fact that she’s able to lay out her situation so succinctly is a sign that she’s obviously planned meticulously for retirement as well — which I love to see. Most germane to this blog though is her intended use of the pension a tool to achieve Financial Independence (FI).

Honestly, in the world of pension valuations and lump sum decisions, this is a fairly simple and straightforward scenario. Ms. Money Penny’s savings and projected Social Security are so high, and living expenses so low, that almost any of the pension options would put her within reach of her $60K a year goal. Thus, I interpret her request for a pension case study more as a request to determine the most optimal pension choice for someone who views longevity and SRR as her two big issues. I’m confident the numbers will easily illuminate a path.

So why choose such a straightforward scenario for a pension analysis case study article? It’s not because she’s the only one who asked! In fact, quite the opposite. I chose her case because it fits so well into Q’s pension value calculator. Yes, choosing her pension case provided a great opportunity to knock out two articles in one. How’s that for efficiency? It’s also a great segue, so let’s take a look at that spreadsheet now.

Q’s Hard Work

First things first, download Q’s pension value calculator. That’ll save me from overwhelming this article with screenshots and descriptions of the functions. Make sure you run an anti-virus scan on that download. Remember, James Bond = “big man-slut”. You never know what kind of diseases he might’ve picked up.

Now that everything’s checked out, go ahead and enter your pension, lump sum, planned retirement age, and other pertinent details. Or you can simply follow along with Ms. Money Penny’s details which I left loaded in the sheet. Once that’s done, you should see the “Pension Values” on the first tab (yellow boxes) auto-populate. These numbers are the ultimate results of the calculations and formulas contained in tabs 2 through 5.

If you click on the Grumpus Maximus tab you should see three different sets of calculations based on different posts I’ve written. Originally this tab only contained the Total Dollar Value (TDV) formula and calculations from Part 4 of the Pension Series. Unlike James Bond though, I took it upon myself to improve on Q’s work. Thus, I added my “mathemagic” formula from Part 8 of the Pension Series (which was actually a guest post on Darrow Kirkpatrick’s website), and the alternate calculations I demonstrated in Part 12 of the Pension Series.

As a result, the Grumpus Maximus tab requires a few extra inputs if you want to make the two later calculations work optimally. The “mathemagic” Adjusted Los Dollar Value (ADLV) formula requires the total value of any Other Retirement Pension Benefits (ORPBs) your pension might provide — like healthcare. If you don’t have any, enter 0 and it can calculate without any input.

For the box based on my calculations from Part 12 of the Pension Series, the only additional input required is your desired Safe Withdrawal Rate (SWR). That part is necessary for some of the calculations to work, but not all. I recommend 3.5% SWR based on the work of Big ERN McCracken at Early Retirement Now, but it’s up to you ultimately.

None of the other tabs require input specific to that tab. They pull required data from the entries on the first tab. A user should fiddle around with the values on the first tab to see what they do to the various sums in the “Pension Values” box. Since not all the formulas use the same data, not all of them will change based off one entry. Therefore I highly recommend users take a look at all the underlying articles that Q footnoted if you want to know how the formulas were constructed.

Pros and Cons of the Master Pension Value Calculator

Q’s pension value calculator is a great one-stop shop for an overview of a pension and lump sum offer. The front tab alone offers a wide perspective of values depending on the different calculations and methods. A potential retiree’s personal values, circumstances, and questions about their pension, or lump sum offer, will probably drive them to favor certain valuations methods over others. Given the fact that there is no one universal method used for pension valuation, that should come as no surprise. Again, I would personally recommend diving into each tab to examine the calculations themselves. The footnotes provided by Q to the articles which describe their specific calculations help a lot here also.

Each method has its own strength and weakness, and it’s not until a user really starts toying around with the various inputs, that one gains an appreciation for what those are. For instance, I think Financial Samurai’s method for pension valuation is only useful if there is more than a small chance that a pension wouldn’t pay out fully in the future and/or the Reasonable Rate of Return is competitive with stock market returns. Otherwise, the results are skewed. Conversely, as I pointed out in the previous post, my Total Dollar Value (TDV) method is really meant to answer what a company owes a retiree in terms of total annuity payments in today’s dollar, which may or may not equate to a pension’s worth.

Q’s pension value calculator, unfortunately, can’t do it all though. Nor do I think it’s fair to expect it to tell us everything about a pension. Like I said above, it provides a great overview, but if a user wants a more in-depth look at the particulars of a pension, they should really run these calculations themselves using various scenarios. Only then, would they gain a true understanding of their pension’s particular issues.

One other item worth noting is that I had to modify Actuary on Fire’s formula for use in my version of Excel. I use an older version which only provides the NPV formula, not the XNPV formula he describes in his article. I looked up the difference, and it appears to be minimal … unless your pension payments occur in some sort of unequal timeline. That wasn’t the situation for Ms. Money Penny, so her values came out in line with expectations. If your pension is different, I would suggest rewriting the formula on that tab.

Back to the Analysis

Ms. Money Penny’s situation is a perfect example of the strengths and limitations of Q’s pension value calculator. Her pension offered several different forms of annuities from 5 to 20 year “Certain Annuities”, as well as 5 to 30 year “Fixed Monthly Installments”. Once I input Ms. Money Penny’s two main scenarios into the pension value calculator, and got a feel for what each of those could reasonably do for her; I ran a few of those other scenarios by hand with a compound interest/inflation calculator. It was simply easier to do it that way than try and fiddle with the various inputs on the spreadsheet. Plus, by running the numbers myself, it quickly became apparent that the two main scenarios (the life annuity and the lump sum) were the only truly competitive options.

Thus, if you haven’t modified the pension value calculator yet, and look at the Grumpus Maximus tab on the spreadsheet, you’ll see my TDV method showed that Ms. Money Penny’s life annuity was worth $420,761 in today’s dollars. Since her pension didn’t come with any healthcare or other benefits, the difference between the annuity and lump sum offer (what I call the ALDV) is a fairly straightforward $83,935. Considering the lump sum and the annuity both pay out at 66 years old when Ms. Money Penny plans to stop working, the difference between the two values doesn’t need to take into account what an invested lump sum could do in the intervening years (unlike the situation in Pension Series parts 11 and 12 for the ChooseFI listener Tess). So far then, the lifetime annuity is winning hands down as the more valuable option.

What I mean by “competitive” is that even with an SWR of 4% or 5%, the lump sum can’t reproduce the $24K that the annuity guarantees over the 34 years of Ms. Money Penny’s projected retirement lifespan. However, Ms. Money Penny has no dependents or need to pass on her wealth to the next generation, so that allows her to spend the lump sum down to zero by the end of her lifespan. That set of circumstances might allow the lump sum to provide $24K annually over the same time span IF it could achieve a high enough rate of return.

pension value calculator
Spend down chart for Ms. Money Penny at 7% real rate of return

How high? Looking at the third box under my tab of the pension value calculator we see that a 7% real rate of return (or a 9% pre-inflation rate or return) for the lump sum would provide 35 years of withdrawals at $24K. Now to some folks that might sound like a reasonable expectation considering they get the flexibility that comes with managing their own money.  However, is that a reasonable expectation considering the concerns Ms. Money Penny expressed over SRR and her longevity issue?


The simple answer to that question is “No”. When I sent Ms. Money Penny the results of my analysis, she told me that she would stick with the lifetime annuity. I wrote back and told her I understood completely. Several of the pension value tabs and calculations (including my own) showed that the lifetime annuity offered the best value among Ms. Money Penny’s options. Even if she took the lump sum, she’d still need to invest it aggressively in order to achieve the required rate of return to make it last 34 years. Reaching for a 7% real rate of return is a lot of risk for a 66-year-old retiree to take on when she doesn’t need to.

Ms. Money Penny’s company runs a strong pension program, she has SRR concerns, and she could potentially live to be 100 years or older. Not to mention the fact that any returns less than 7% (real) mean the lifetime annuity provides her more money. On the face of it, for a person in her position, any one of those reasons alone might provide good enough reason to stick with the lifetime annuity. Combined together they made an overwhelming argument to decide against the lump sum in her case.

With regards to Q’s pension value calculator, I’m a fan. Granted, it doesn’t cover every option for every pension combination that’s out there, but nothing could do that. That said, it’s a great tool and provides a quick initial appreciation of the variables involved in a pension annuity vs. lump sum decision. If a user values he ability to pass on generational wealth, there’s a tab that calculates the amount the pension lump sum would need to be in order to employ the 4% SWR immediately. If a pension doesn’t have an inflation-fighting COLA, then my tab would probably offer the best analysis method. So on and so forth … Again I would highly recommend a user fiddle around with all the different inputs to discern their effects on the value calculations. Otherwise, I highly endorse this product!

Alas, I’ve reached the end of my article long before my standard 3000-word average. Before I go though, I would like to thank Q for his work, and Ms. Money Penny for providing the scenario. It’s safe to say this article wouldn’t exist without both of their input. I’d also love to hear any feedback you readers might have on the spreadsheet or the scenario itself. I’d truly appreciate updates to the pension value calculator from people with way more spreadsheet skills than myself use, add to, and modify it. Thanks and good luck with your calculations.

Let me know what you think!