not on facebook
ANNOUNCEMENT : ALL OF ROYAL MAIL'S EMPLOYMENT POLICIES (AGREEMENTS) AT A GLANCE (UPDATED APR 2019)... HERE

ANNOUNCEMENT : NEW CORONAVIRUS FORUM... HERE




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

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.......
You do not have the required permissions to view the files attached to this post.
Last edited by nodboy on 16 Oct 2020, 13:35, edited 4 times in total.

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

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...
You do not have the required permissions to view the files attached to this post.
Last edited by nodboy on 17 Oct 2020, 13:37, edited 4 times in total.

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

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.

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

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?

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

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.

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

13 Oct 2020, 21:16

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

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

13 Oct 2020, 22:04

Works for me downloaded auto opened in office

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

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.
Last edited by stephen500 on 14 Oct 2020, 08:31, edited 2 times in total.

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

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!

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

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
You do not have the required permissions to view the files attached to this post.
Last edited by stephen500 on 14 Oct 2020, 07:34, edited 1 time in total.

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

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.

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

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!

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

14 Oct 2020, 08:05

:thumbup :thumbup :thumbup

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.

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

14 Oct 2020, 08:12

RobertT wrote::thumbup :thumbup :thumbup

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)

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

14 Oct 2020, 08:21

stephen500 wrote:
RobertT wrote::thumbup :thumbup :thumbup

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. :thumbup

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

14 Oct 2020, 16:17

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.


Thank you for your valuable feedback and I agree and have made the alteration. Good spot!!!!!!

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

14 Oct 2020, 16:45

nodboy wrote:
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.


Thank you for your valuable feedback and I agree and have made the alteration. Good spot!!!!!!


Good job - for taking the time and making the effort

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

16 Oct 2020, 00:40

nice work, noticed a typo on annual.

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

16 Oct 2020, 13:45

mrcurve wrote:nice work, noticed a typo on annual.
Thank you, I welcome your feedback and the correction has been made!!!

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

16 Oct 2020, 18:40

Here is a quick Spreadsheet I have made too, just add the figures from your statements in the correct columns and any AVCs you may have.

Experiment with your planned retirement age by adding or changing it in the first column of the last table to get an idea of how changing this effects your final figures. This is all based roughly on my knowledge of how things stand for section C members but I would welcome any feedback or constructive advice.

I purposely ignored the lifetime allowance calculations because very few of us rank and file posties will get anywhere near the maximum allowances.


Edited the file download after further advice and feedback

RM Pensions and lump sum Calculator1.xlsx
You do not have the required permissions to view the files attached to this post.
Last edited by mr hil. on 17 Oct 2020, 19:15, edited 2 times in total.

Previous page Next page


Page 1 of 3   1, 2, 3