Investing

Actuarial help - Commuted value calculation

  • Last Updated:
  • Aug 11th, 2021 6:29 am
[OP]
Jr. Member
Jun 23, 2012
127 posts
17 upvotes
Calgary

Actuarial help - Commuted value calculation

hi all,

I need actuarial help in calculating the commuted value of my DB pension. My pension doesn't provide this unless I officially leave my position. I have the assumptions from my pension provider, including discount rate, cost of living adjustment, mortality assumptions, and other details of my service. Can someone kindly point me to a formula to do this myself? or help me with the calculation?

thanks in advance!
16 replies
Sr. Member
Dec 3, 2019
510 posts
464 upvotes
Ontario
It's a matter of being familiar with excel and knowing how to use TVM functions in excel. Too long to explain in words.

I would post on excel forums like Mr. Excel. Those places might help you find a template.

Out of curiosity what is the discount rate you were provided?
Deal Expert
User avatar
Dec 12, 2009
25234 posts
14967 upvotes
Toronto
I believe that each defined benefit pension plan is slightly unique and so there is no universal formula.
Koodo $40/6GB
Public Mobile $40/15GB, lot less with rewards
Fido $0.00/4GB+tablet
Tangerine Bank, EQ Bank, Simplii
[OP]
Jr. Member
Jun 23, 2012
127 posts
17 upvotes
Calgary
Thank you. I'm not looking for an exact number, a close approximate would be good enough. The discount rate provided to me was 4.4%, which is a high bar!
Sr. Member
Dec 3, 2019
510 posts
464 upvotes
Ontario
I used to use 5.5% but that was a number of years ago.
Rhinox87 wrote: Thank you. I'm not looking for an exact number, a close approximate would be good enough. The discount rate provided to me was 4.4%, which is a high bar!
Deal Fanatic
Aug 17, 2008
8537 posts
9355 upvotes
Mar 27th, 2020 6:00 pm
qman23 wrote: The codes V122542, V122544 and V122553 are the ones used by actuaries to determine pension commuted values.
I was expecting the drop in bond rates to increase the commuted value of the pension
But when the April numbers came out, the commuted value has decreased by over 7%.
Digging further, I found the long term real return had actually increased by 7 times, hence my confusion.

thanks for the link, I may be retiring early.
[OP]
Jr. Member
Jun 23, 2012
127 posts
17 upvotes
Calgary
Thanks. Can you please provide me a basic formula please? I can apply it myself.

I will check out Mr. Excel as well.
Deal Fanatic
Jan 19, 2017
7441 posts
4340 upvotes
Rhinox87 wrote: hi all,

I need actuarial help in calculating the commuted value of my DB pension. My pension doesn't provide this unless I officially leave my position. I have the assumptions from my pension provider, including discount rate, cost of living adjustment, mortality assumptions, and other details of my service. Can someone kindly point me to a formula to do this myself? or help me with the calculation?

thanks in advance!
Mr. Google shows this :https://pensionsolutionscanada.com/how- ... -value-is/
Sr. Member
Dec 26, 2019
726 posts
1255 upvotes
Don’t forget your mortality table
Deal Addict
User avatar
Feb 1, 2012
1971 posts
3285 upvotes
Thunder Bay, ON
You asked for it. :)
See this document starting on p.3044. https://www.cia-ica.ca/docs/default-sou ... 20120e.pdf

Pensions have different benefit calculations, based on length of service, typically x% per year of service to a maximum of 35 years, then reduced at age 65, the nominal age to begin CPP. On the other hand, CV calculation is specified in the Canadian Institute of Actuaries Standards of Practice. Employers have no discretion in the method used for calculating the CV beyond what is specified in the above document.

The discount rate is based on a mix of 7 year and 10 year Canada nominal bond rates for non-indexed pensions and on Canada real return bond rates for indexed pensions. There is a factor added to the bond rates to adjust for expected return of a balanced pension portfolio. That would be the basis for the 4.4% discount rate. See the actual formulas in the document for the nitty-gritty details.

Basically it takes the pension payment from age 65 to life expectancy (adjusted by a cola factor as you mentioned in your OP) and brings it to a present value required at age 65. Then it takes that lump sum amount at 65 and brings it to a lump sum required at the date of the CV calculation.
I solemnly swear, to never assume I have an inkling at which direction the market will head, and to never make any investments based on a timing strategy.
[OP]
Jr. Member
Jun 23, 2012
127 posts
17 upvotes
Calgary
Thank you Deepwater. Your explanation was very helpful. Since I just wanted to know the approximate numbers, I calculated PVn at retirement age for given lump sums based on discount rate and post-retirement COLA. I just assumed payout till age 95 for simplicity, instead of digging into mortality assumptions. Then I calculated PVt for today's present value based on discount rate and pre-retirement COLA.
Deal Expert
User avatar
Dec 12, 2009
25234 posts
14967 upvotes
Toronto
Rhinox87 wrote: Thank you. I'm not looking for an exact number, a close approximate would be good enough. The discount rate provided to me was 4.4%, which is a high bar!
If you are just trying to determine the discount rate, the formula is in section 3540 of this document.

https://www.cia-ica.ca/docs/default-sou ... 20120e.pdf

The fixed income rates can be found on this site.

https://www.ftserussell.com/spotlight/c ... ted-values

The discount rates are determined at the end of each month and remain applicable for the following month.
Koodo $40/6GB
Public Mobile $40/15GB, lot less with rewards
Fido $0.00/4GB+tablet
Tangerine Bank, EQ Bank, Simplii
Deal Addict
User avatar
Feb 1, 2012
1971 posts
3285 upvotes
Thunder Bay, ON
Rhinox87 wrote: Thank you Deepwater. Your explanation was very helpful. Since I just wanted to know the approximate numbers, I calculated PVn at retirement age for given lump sums based on discount rate and post-retirement COLA. I just assumed payout till age 95 for simplicity, instead of digging into mortality assumptions. Then I calculated PVt for today's present value based on discount rate and pre-retirement COLA.
The life expectancy should be based on average life expectancy for people your age, which is probably closer to 85 than 95, not how long you think you might live. You may be able to find actuarial tables with a web search.

If you take the pension, the pension sponsor bears most of the risk, including longevity, investment returns and inflation. If you take the commuted value, that risk transfers to you, in exchange for the possibility of getting higher investment returns and / or your estate benefiting if you die earlier than mortality tables predict. Pensions often have low costs and a long time horizon, so to get better returns requires you taking more risk. The consequences of that risk going against you are serious: running out of money in retirement when you have little ability to recover.

If two spouses both have an indexed, well funded public service pension then it might make sense for one of them to take the commuted and the other to take the pension. Otherwise I don't see a persuasive case for taking the commuted value.
I solemnly swear, to never assume I have an inkling at which direction the market will head, and to never make any investments based on a timing strategy.
[OP]
Jr. Member
Jun 23, 2012
127 posts
17 upvotes
Calgary
Deepwater wrote: The life expectancy should be based on average life expectancy for people your age, which is probably closer to 85 than 95, not how long you think you might live. You may be able to find actuarial tables with a web search.

If you take the pension, the pension sponsor bears most of the risk, including longevity, investment returns and inflation. If you take the commuted value, that risk transfers to you, in exchange for the possibility of getting higher investment returns and / or your estate benefiting if you die earlier than mortality tables predict. Pensions often have low costs and a long time horizon, so to get better returns requires you taking more risk. The consequences of that risk going against you are serious: running out of money in retirement when you have little ability to recover.

If two spouses both have an indexed, well funded public service pension then it might make sense for one of them to take the commuted and the other to take the pension. Otherwise I don't see a persuasive case for taking the commuted value.
Thanks, that makes sense. Both of us are fairly early in our careers, and the amount we're talking here isn't really that large, that it would make a big dent in retirement ultimately. Long time horizon is also the reason why we're leaning towards CV as we'll have control over it as pension rules may change in a few decades from now.
Deal Addict
Dec 4, 2011
2311 posts
2379 upvotes
Montréal
Deepwater wrote: The life expectancy should be based on average life expectancy for people your age, which is probably closer to 85 than 95, not how long you think you might live. You may be able to find actuarial tables with a web search.

If you take the pension, the pension sponsor bears most of the risk, including longevity, investment returns and inflation. If you take the commuted value, that risk transfers to you, in exchange for the possibility of getting higher investment returns and / or your estate benefiting if you die earlier than mortality tables predict. Pensions often have low costs and a long time horizon, so to get better returns requires you taking more risk. The consequences of that risk going against you are serious: running out of money in retirement when you have little ability to recover.

If two spouses both have an indexed, well funded public service pension then it might make sense for one of them to take the commuted and the other to take the pension. Otherwise I don't see a persuasive case for taking the commuted value.
Here are the Canadian tables for years of life remaining at a given age

https://www150.statcan.gc.ca/t1/tbl1/en ... 1310011401
Newbie
Sep 18, 2009
74 posts
21 upvotes
Rhinox87 wrote: hi all,

I need actuarial help in calculating the commuted value of my DB pension. My pension doesn't provide this unless I officially leave my position. I have the assumptions from my pension provider, including discount rate, cost of living adjustment, mortality assumptions, and other details of my service. Can someone kindly point me to a formula to do this myself? or help me with the calculation?

thanks in advance!
Same boat here my pension is making me hire actuary as they no longer provide......you live in Alberta? I have discount, cola life expectancy rates used all the helpful info provided. Did you come up with formula? If so can you please post would be super helpful.
[OP]
Jr. Member
Jun 23, 2012
127 posts
17 upvotes
Calgary
Hey. I'm unsure how accurate it is and am reluctant to share it here. As I mentioned, it's not a large amount for us. I simply calculated Present value at retirement age from annuity lump sums based on the longevity. From there, I calculated present value today. There are easily formulas that you can use for both those calculations. And yes, also in Alberta, haha.

Top

Thread Information

There is currently 1 user viewing this thread. (0 members and 1 guest)