The Pension Lump Sum vs Annuity Calculator Pack: Seven linked calculators, every formula checked by recalculation

Seven calculators for one election: take the pension as a lump sum or as monthly income. Sheet 1 works out the return the pension is paying you: in the worked example $2,400 a month in place of $400,000 is 5.26% a year if you live to 90. The others give the break-even age, the present value at a discount rate you choose, a COLA against a flat pension, the cost of a survivor option, how long the lump sum lasts paying the same income, and how interest rates move the lump sum. Every figure is an example for you to overwrite with the numbers in your plan's election packet; no tax is computed. This is not financial advice. Every formula was recalculated and checked against an independent implementation before this shipped.

What is in it

7 calculators, one per sheet. Overwrite the blue cells with your own numbers and everything else follows. Nothing is locked, there are no macros, and it opens in Excel, Numbers, LibreOffice and Google Sheets.

• Implied return of the annuity: What return is the pension paying you on the lump sum it replaces?
• Break-even age: How long must you collect the pension to match the lump sum?
• Present value of the annuity: What is the pension worth today, at a discount rate you choose?
• COLA vs no COLA: Is a lower pension that rises each year worth more than a flat one?
• Survivor option cost: What does a joint-and-survivor pension cost, and what does it buy?
• How long the lump sum lasts: Paying yourself the pension's income, when does the lump sum run out?
• Lump sum vs interest rates: How much does the lump sum's value move when interest rates change?

Every formula was checked

This pack contains 48 formulas. Before it was published, every one of them was recalculated by a spreadsheet engine and compared against an independent implementation of the same arithmetic written in Python. All 48 agreed to within a cent, and the workbook carries a Verification sheet saying so.

That is an unusual thing to be able to claim about a spreadsheet, and it is the main reason to buy this one rather than download a free template. A formula that looks right and is wrong is worse than no formula at all, because you will act on it.

What each calculator assumes

There is a sheet listing this, because a calculator that hides its assumptions is just a number generator:

• Level monthly payments starting now, paid at the end of each month, until the test age. Rates are nominal yearly (monthly rate x 12). No tax: both are taxed, and how depends on rollover choices this pack does not model.
• Pension paid at the end of each month with no COLA. The lump sum earns the same return every month. "Never" means the return on the lump sum alone pays at least the pension.
• One fixed end age instead of a mortality table; your plan uses a table and its own rates. Payments at the end of each month, no COLA (sheet 4 adds one).
• Yearly steps: the COLA pension rises once a year, and each year's 12 payments are discounted as one payment at year end. A fixed number of years stands in for a lifetime. A COLA capped or tied to inflation will differ from a fixed rate.
• Level payments, no COLA, paid at the end of each month. Some plans restore the single-life amount if the spouse dies first (a pop-up); not modeled. Waiving a survivor option can require your spouse's written consent; check your plan.
• The same return every month: real returns vary, and a bad early run shortens the life of the money (sequence risk), which a fixed rate cannot show. Withdrawals at the end of each month, no tax.
• One discount rate and a fixed end age stand in for the rates and mortality table your plan uses by law and plan rules; this shows direction and size, not your plan's figure. Ask the plan when its rates are next reset.

What this is not

Not financial, tax or legal advice, and not a recommendation to buy, sell, borrow or sign anything. These are calculators: they show what a choice costs, not which choice is right for you. Where a published rate or threshold is used it is the one current when the pack was built; those change, the arithmetic does not.

Honest about the format

One .xlsx workbook, 7 calculator sheets plus Start Here, Assumptions and Verification. Not a PDF, not a physical product, not a Notion template. Available immediately after purchase; nothing ships.

Refunds

If this isn't what you expected, email me within 30 days and I'll refund you in full. No need to explain. Because it's an instant download I can't take the file back, so I'd rather you just had your money back.