How do I calculate whether to take monthly pension payments or a lump sum and invest it myself?

Question

I have the choice of monthly pension payments or taking a lump sum. I need to calculate, based on a certain interest rate, how many years I could invest the lump sum and take monthly payments, until it runs out.

This is another way of saying that I wish to calculate how long I would have to live in order for it to be worthwhile to receive monthly pension payments rather than take a lump sum.

Is there a built-in function in Microsoft Excel for calculating this?


.

To clarify, I wish to assume no taxation in any event, and I can add in the taxation details at a later stage. What I am looking for is a formula for calculating the remaining capital (including accrued interest) after every monthly payment that I take from a one-time investment, until the balance reaches zero. To simplify matters, I can make do with just the amount of time until the money is depleted.

I would be pleased to receive a formula that I could use in Excel.

The IRR did not seem applicable.

The references to the Telepgraph article and the Make Your Money article were too vague to find. These sites are updated daily, and what was there one day is not the next, or at least not so as you can find it easily.

More details would be appreciated.

Answer 1

I don't have an answer, but just several comments.

The fellow has asked for a formula. Only one answerer has tried to supply one, and that appears not to be applicable.

Two opposite recommendations have been given, with no expressed doubts: take the lump sum, and don't even dream about taking the lump sum.

Can't anyone supply a method of calculation?

Here's an idea. Find a company that will sell you an annuity. Supply your age and the lump sum amount, and see what sort of annuity you can buy for that amount.

If you are being offered a monthly payment greater than that, you should take the monthly payment option.

Answer 2

You are not providing enough information to answer properly.

In general it is best to commute as much of your pension as you can and take a lump sum, because the lump sum is the only truly tax free money you'll get. If you took everything as a regular monthly pension payment you will be taxed on this.

Problem is you will have to be careful with the money you take as a lump sum as you'll have to live on it.

Most people have to buy an annuity with the money they leave to pay a pension. If everyone in your family lives to 105 and you expect to also. It might be worth looking at the pension option.

You need to take advice which is based on all the information.

Answer 3

the pension comes out of a fund which should pay out far greater than any interest from a bank,i am not sure you have understood about the lump sum payment as in the UK this is maximum 25% tax free and the rest remains in the fund which pays out a monthly premium for the rest of your life ...i think this 25% is the lump sum you are talking about....i retired from my private pension at 50 and been drawing for 2.5 years because it was not performing and although i would have got a lot more each month if i had kept it up till 65 at least the monthly pension payment is gone and i draw a nice little amount every three months

Answer 4

I don't have the formula, but I would suggest that someone provide it on the basis of no cost of living increments (since they would apply in all cases) and with ignoring taxation.

All this fellow wants to know is how to compare the expected return from depositing his lump sum in a bank (and drawing the expected pension payment each month), with the monthly payments he would get as a pension. And thereby to determine how long he would have to live to make it worth his while to leave the money in and draw a pension.

Hasn't anyone got the formula?

Answer 5

Here is a very simple alternative. Figure what a 6% return on the lump sum will bring you. You can have that guaranteed for as long as you live with some growth potential in addition.

Compare that to the monthly pension alternative and then decide.

If you would like further explanation, let me know at

[e-mail]

Answer 6

Invest some time and energy in GETTING IT RIGHT - because some decisions (Annuities) made now CAN NOT BE UNDONE, EVER !

You have exactly the right idea Re: spread-sheeting it ...

First you need to get hold of sufficient information to discover the facts - for sure you need the following :-

(Full Pension= without taking Lump Sum, Reduced Pension = after taking 25% Tax Free Lump Sum)

1) Transfer Value (if you want to think about moving the Pension into a SIPP and managing it your self either because you don't want to Retire now or because you want to avoid taking an Annuity now i.e. move into Draw-down) - see link below (NB DO NOT get ripped off by some 'Financial Consultant' - you can sort out your own SIPP without needing to 'donate' 5% of your Pension Fund to some moron from the City)


'Full Pension'
2a) What Annuity are they offering ? (and is this level or with RPI (or other) annual increase ?)

2b) What is the Open Market Option ? (this lets you judge if you would be better off getting an Annuity for another supplier === often YES (but you need to compare 'like for like' - eg. RPI annuity with RPI annuity).

NOTE - if you health is bad you might be able to get an Enhanced (Life Impaired) Annuity ...

'Reduced Pension' - you need the same 2 figures for Annuity after the lump sum
3a) & 3b)


NOTE - Pension is INCOME so will be Taxed if your total Income exceeds your annual personal Allowance (typically £5,200 / year) - so taking the Tax Free Lump Sum may reduce your Tax bill ...

Other things to consider - when you die the Annulity dies along with you (unles you die within the 'guaranteed' part of a guaranteed annuity - typically first 5 years, and unless your annuity includes a spouses pension (which will usually pay out at reduced rate - sometimes known as 'joint life') - whilst the 25% Tax Free lump sum is yours to do what you like with (eg. put into an ISA and use to generate extra (tax free) income in the future).

Spreadsheet your 'take home' Income.

w/o Lump Sum it's Full Pension Annuity minus Tax.

with Lump Sum payment, it's Reduced Pension Annuity - Tax PLUS however much of the Lump Sum you are going to 'consume' as a 'top up' each year

(as a 'ball park' you could assume 8% - this would be made up of approx 5% growth & 3% capital)

If you really want to Spreadsheet 'total retun' i.e. untill you die, you will need to check out Mortality Tables for your age - these are available off the web, but are out-iof-date (you can assume you will live longer that the predicted 'average').

To calculate NPV you will need to make assumptions about futur inflation (CPI / RPI).

Good Luck !!!

Answer 7

Simple..Excel has inbuilt function to calculate Internal Rate of Return (IRR). You calculate the IRR for the pension payments you are about to receive. Suppose you get an IRR of 10%p.a., then see that in case you select the option of taking lump sum payment, will you be able to invest that money which gives you 10% p.a. return. In case you are able to invest lump sum money at a return greater than 10% then its better to take lump sum amount

http://makemoneywork.typepad.com

Cheers
Ajay

Answer 8

Certain rules apply to each pension provider i.e. are you gettinh 25% tax free and then the rest as a pension? or do you qualify for the full lump sum i.e. under a certain amount and you are over a certain age? i would base it on how much it taxed of the lump sum then see if the same tax rules apply for the monthly/annual payments

Answer 9

Don't even think of a lump sum.

These plans are devised by crooks with their interests in mind not yours.

Answer 10

It may be a good idea to seek assistance by a Independant Financial Advisor.