A spreadsheet to make RM pension illustrations easier and more visual to understand!!

nodboy · Post by **nodboy** » 13 Oct 2020, 12:52

pic for rmc.jpg

RM pensions are difficult to understand so I create a spreadsheet to make it more visual for me. This is for a section C member the figures in it are to show how it works only, people can put there own figures in to get a better picture of there pension. Feedback from any pension guru's on here would be great!!!!

Spreadsheet downloadable in the comments below.......

nodboy · Post by **nodboy** » 13 Oct 2020, 13:02

The spreadsheet in excel format for people to try out........

The spread sheet has now been updated with the feedback from Mr Hil (which I thank him for!!) with adjustment he has suggested and now flows through correctly.

Note to people using this.... the lump sum calculation is of all your pensions information (i.e. NRA60 and NRA65) and not what you would receive at 60.

The spreadsheet should only use as a guideline and not to base any financial decisions on...

mr hil. · Post by **mr hil.** » 13 Oct 2020, 19:32

Great document, only one error, the lump sum calculations are incorrect, cell Q11 should read

=L14-F18

because you need to subtract the pension supplement that is itemized below.

Jefferson Starfish · Post by **Jefferson Starfish** » 13 Oct 2020, 19:39

mr hil. wrote:Great document, only one error, the lump sum calculations are incorrect, cell Q11 should read
=L14-F18
because you need to subtract the pension supplement that is itemized below.

Does it not depend on whether you're leaving RM though?

mr hil. · Post by **mr hil.** » 13 Oct 2020, 19:47

Yes the overall totals would depend if you are leaving Royal Mail or not but the Q11 cell takes it's value from the total pensions figure at cell L14 which includes the pension supplement so this needs to be removed to be correct whether you are leaving Royal Mail or not.

heapsy · Post by **heapsy** » 13 Oct 2020, 21:16

What program do you use to read this? Didn't make any sense.

Post by **postie2007** » 13 Oct 2020, 22:04

Works for me downloaded auto opened in office

Post by **stephen500** » 14 Oct 2020, 07:14

Sorry for me this is confusing the lump sum figures on the right give the Pot value, not the actual possible lump sum of up to 25%.
The pension supplement can only be used for a lump sum, if the member is going to leave the business, if the member continues employment with Royal Mail, then the supplement is not paid and cannot be used towards a lump sum.
Also some where it would be good to indicate that for section C members 75% of their cash balance is taxable at their tax rate, normally 20%. (edit. This is true if the member chooses to use up his 25% tax free limit by reducing his pension by 25%, since he/she has used up all his tax free limit, then for the DBCBS only 25% can be tax free and 75% is taxable at his/her tax rate)
The member can of course (as Robert T points out) choose not to reduce their pension and not take a 25% tax free lump sum from their standard pension and just have the DBCBS all as a tax free amount. Although this could result in a much reduced lump sum, but at the same time it would mean the pension would not have to be reduced.
I personally think that reducing the pension is a good choice, as the member would have to live for over 20 years before they started losing out, through the reduced pension by taking the standard lump sum - end of edit)
Pension lump sum should be £49218 25% of the pot you show and for the pension supp.
Pen supp lump sum should be £6132.
Cash balance after tax £7225
AVC £5000 (this I am unsure about as I have had no dealings with Avcs)
Total lump sum £67575.

Post by **RobertT** » 14 Oct 2020, 07:22

stephen500 wrote:Sorry for me this is confusing the lump sum figures on the right give the Pot value, not the actual possible lump sum of up to 25%.
The pension supplement can only be used for a lump sum, if the member is going to leave the business, if the member continues employment with Royal Mail, then the supplement is not paid and cannot be used towards a lump sum.
Also some where it would be good to indicate that for section C members 75% of their cash balance is taxable at their tax rate, normally 20%.

That is the case for section F members, not section C!

Post by **stephen500** » 14 Oct 2020, 07:31

RobertT wrote:
stephen500 wrote:Sorry for me this is confusing the lump sum figures on the right give the Pot value, not the actual possible lump sum of up to 25%.
The pension supplement can only be used for a lump sum, if the member is going to leave the business, if the member continues employment with Royal Mail, then the supplement is not paid and cannot be used towards a lump sum.
Also some where it would be good to indicate that for section C members 75% of their cash balance is taxable at their tax rate, normally 20%.
That is the case for section F members, not section C!

I just helped a section C member fill out his pension forms and it stated on his forms (final quote) that for his DBCBS (Cash balance) 25% would be tax free and 75% would be taxed at his tax rate as an "Uncrystallised fund pension lump sum".

c section.jpg

Post by **RobertT** » 14 Oct 2020, 07:33

stephen500 wrote:
RobertT wrote:
stephen500 wrote:Sorry for me this is confusing the lump sum figures on the right give the Pot value, not the actual possible lump sum of up to 25%.
The pension supplement can only be used for a lump sum, if the member is going to leave the business, if the member continues employment with Royal Mail, then the supplement is not paid and cannot be used towards a lump sum.
Also some where it would be good to indicate that for section C members 75% of their cash balance is taxable at their tax rate, normally 20%.
That is the case for section F members, not section C!
I just helped a section C member fill out his pension forms and it stated on his forms (final quote) that for his DBCBS (Cash balance) 25% would be tax free and 75% would be taxed at his tax rate.
c section.jpg

That screenshot applies to the total benefits, not the DBCBS on it's own. See page 12 of the plan guide for info!

You may be getting mixed up with an excess amount of DBCBS? Which you know yourself will be treated as a UPFLS - first 25% tax free the remaining 75% taxable income.

Post by **stephen500** » 14 Oct 2020, 07:45

RobertT wrote:
stephen500 wrote:
RobertT wrote:
stephen500 wrote:Sorry for me this is confusing the lump sum figures on the right give the Pot value, not the actual possible lump sum of up to 25%.
The pension supplement can only be used for a lump sum, if the member is going to leave the business, if the member continues employment with Royal Mail, then the supplement is not paid and cannot be used towards a lump sum.
Also some where it would be good to indicate that for section C members 75% of their cash balance is taxable at their tax rate, normally 20%.
That is the case for section F members, not section C!
I just helped a section C member fill out his pension forms and it stated on his forms (final quote) that for his DBCBS (Cash balance) 25% would be tax free and 75% would be taxed at his tax rate.
c section.jpg
That screenshot applies to the total benefits, not the DBCBS on it's own. See page 12 of the plan guide for info!

You may be getting mixed up with an excess amount of DBCBS? Which you know yourself will be treated as a UPFLS - first 25% tax free the remaining 75% taxable income.

Page 12 says what you say. But why did the forms of the colleague I helped fill out the forms say that the DBCBS would be an "uncrystallised fund pension lump sum" and 25% would be tax free and 75% taxed at his tax rate (ah, come to think of it, that was for the option he chose) . This was for NRA65 only (taken NRA60) Ah I see why, the light bulb has just gone off! He converted 25% of his section C to the 25% lump sum available to him, leaving him to have the DBCBS as an "uncrystallised fund pension lump sum" meaning he could only take 25% of that tax free and 75% would be taxed. So for any section C member who converts 25% of his pension to take the lump sum (25%) that means as I just said that the DBCBS is over that 25% limit and as I said 25% of the DBCBS is tax free and 75% subject to tax! So the option he chose used up all his 25% tax free allowance converting 25% of his pension to his lump sum. So you were right, as was I IF I HAD STATED THIS CLEARLY, WHICH I HAD NOT, SORRY!

Post by **RobertT** » 14 Oct 2020, 08:05

It seems to me, and without knowing the full details, your friend might be better off overall by taking the DBCBS tax free, which most section C members will probably be able to do because they don't get a lump sum as standard, and in turn get a smaller lump sum and slightly higher pension from their main benefits.

That would certainly be the case with my section C benefits, but it as ever it will obviously depend on individual choices and circumstances, etc.

Post by **stephen500** » 14 Oct 2020, 08:12

RobertT wrote:

It seems to me, and without knowing the full details, your friend might be better off overall by taking the DBCBS tax free, which most section C members will probably be able to do because they don't get a lump sum as standard, and in turn get a smaller lump sum and slightly higher pension from their main benefits.

That would certainly be the case with my section C benefits, but it as ever it will obviously depend on individual choices and circumstances, etc.

Thanks.
But he has already signed off his choice.
He chose to give up 25% of his pension and get 25% tax free lump sum and took the DBCBS as UCLS.
I did say to him that he would lose 25% of his pension, but he decided that by taking his lump sum, that it would take him to survive for 20 years before he started losing out and so wanted the lump sum, plus the DBCBS. (ie he wanted as much cash as he could get)

Post by **RobertT** » 14 Oct 2020, 08:21

stephen500 wrote:
RobertT wrote:

It seems to me, and without knowing the full details, your friend might be better off overall by taking the DBCBS tax free, which most section C members will probably be able to do because they don't get a lump sum as standard, and in turn get a smaller lump sum and slightly higher pension from their main benefits.

That would certainly be the case with my section C benefits, but it as ever it will obviously depend on individual choices and circumstances, etc.
Thanks.
But he has already signed off his choice.
He chose to give up 25% of his pension and get 25% tax free lump sum and took the DBCBS as UCLS.
I did say to him that he would lose 25% of his pension, but he decided that by taking his lump sum, that it would take him to survive for 20 years before he started losing out and so wanted the lump sum, plus the DBCBS. (ie he wanted as much cash as he could get)

It was just an observation really. It's obviously entirely up to the individual what they do.

ROYALMAILCHAT