Investing a 100K Lump Sum Amount – Case Study Part #1 The Calculations


A few weeks ago, I wrote I was getting ready to invest 100K in the stock market. I didn’t win the lottery or inherit money from some far away uncle with no children. In fact, this money is coming because I decided to take a lump sum instead of keeping my pension from my former employer. In my latest article, I mention I was waiting to get my “pension booklet” with all the final details before taking my decision. Should I keep my pension or get the commuted value? This question is not to be taken lightly as it will influence a good part of my future. Now that I have received my booklet (yeah… that was fast!), I can run some calculations and make a sound decision.

Pension Vs Lump Sum – The Numbers

A week after writing my article, I received the documentation from HR. This was an envelope with 10 pages of information inside requiring a bunch of signatures. If I didn’t know how banks work already, I would have been intimidated. But fortunately, I used to analyse these types of documents on a daily basis for the past 13 years as a former financial planner. The documents stated the two options I have until October 7th:

Option #1 keep the pension

The first option is to keep my pension with my previous employer. The money invested will continue to be managed accordingly and I will receive a fixed pension at the age of 65. The annuity is in the amount of $1,228.17 starting in 2046 (darn, now you know my age!). The annuity option details go on for 3 pages, but here are the important details:

  • The annuity is not indexed (meaning I’m “stuck” with a fixed payment of $1,228.17 until I die)
  • If I die prior to 2046, the present value of my annuity will be paid as a lump sum to my wife (who is my designated beneficiary)
  • If I die while receiving my pension, my wife will receive 60% of my annuity
  • I have the option to receive a reduced annuity at the age of 55 instead of 65
  • I could wait and ask for a lump sum at a later date (as long as it is before 2036)

Option #2 Receive the commuted value

The second option is “easier” to understand as it doesn’t have many details. The actuarial value of my annuity is set at $108,362.19. This is the amount of my lump sum I should receive if I decide to decline option #1. This amount must be transferred into a Locked-In retirement account, also called LIRA in Canada.

The difference between cashing a cheque of $108K and depositing into a LIRA is that I can’t touch this money until I am 55. There are various exception to this rules, but overall, what is really important to understand is that the money has to be invested at least until I’m 55 and that it will be in a tax sheltered account. At the time of withdrawals, each amount withdrawn will be added to my revenue and get taxed accordingly.

A few more details about lump sum payments

Before I go into my calculations, it is very important to highlight that each pension plan is different and all employers may offer various options. My case is not a general one, but it is highly specific… to MY situation. However, many things you will learn during this series will help you in making your decision for YOUR situation (keep in mind that I’m Canadian too!).

Did you know that my lump sum amount seems high compared to the annuity because of the low interest rate environment? Yup, according to pension regulations, a good part of pension money must be invested in fixed income. Therefore, when interest rates are low, the employer must dip deeper in his pocket and put additional money into the pension fund. Low interest rate environments are good timing to take your lump sum instead of the annuity.

Did you know that you might pay taxes on your lump sum payment? Yup, depending on the situation, some lump sums are separated into two different amounts. The first one will go into a locked-in retirement account like mine and the second amount (usually smaller) will be payable in cash. The cash portion is taxable and will be added to your current revenues. This means this money will get hit by your marginal tax rate… not cool. One solution to avoid paying taxes on your lump sum payment is to put this money back into your retirement account (a regular RRSP).

The Future Value of your lump sum

You will be happy to know that you don’t need some fancy financial planning software to discover which the best option is. In fact, you only need some basic financial background and an Excel spreadsheet… forget about the basic financial background, just read the following part of this article with a good cup of coffee and an Excel spreadsheet open!

The first step is to define both options in term of numbers:

  • Option #1: receive a fixed annuity of $1,228.17/month or $14,738.04 starting in 2046
  • Option #2: receive $108,362.19 now

The idea is to determine what will happen with the lump sum payment and if I can generate a bigger annuity at retirement (on 2046).

The first step is to open your Excel spreadsheet and use the Future Value function. The future value will give you the “future value” (duh!) of an investment today at a specific rate of return. You can access the function by clicking on the little “fx” button on the top left of your spreadsheet:

Once you click, you will have a drop-down menu of many functions, simply select “Future Value”. This selection will generate a small pop-up screen:

You basically have to fill in the above mentioned fields according to your assumptions. I started my calculations assuming I can make a 6% investment return over the next 29 years (“Nper” refers to the number of period). As I intend to invest 100% of my money in dividend growth stocks, I am comfortable with a 6% annualized return. Over time, 100% equity portfolio should even shows 7%, even 8% return. In order to complete the formula, you simply add a “0” for Pmt (as it refers to future payments) and include the negative value of your present value (Pv).

As you can see, these calculations bring me to a future value of my investment of $587,148.38 in 2046. Keep in mind that this amount is far from being guaranteed. The 6% investment return I used for my calculations isn’t by any mean conservative, optimistic or pessimistic. This is just a number I use to get a base for comparison. I will get back to this number later on in this article.

At this point, my options are now:

  • Option #1: receive a fixed annuity of $1,228.17/month or $14,738.04 starting in 2046
  • Option #2: receive $587,148.38 in 2046

This is a tricky question isn’t? roughly $15K/year forever or almost 600K? let’s compare both!

Comparing the lump sum with the annuity

An easy solution would be to divide the annuity ($14,738.04 per year) by the future commuted value I could get at the same age ($587,148.38). By dividing $14,738.04 by $587,148.38 I get 2.50%. Therefore, at the age of 65, if I have invested the lump sum correctly for 29 years, I would need to generate 2.50% return from my investment to copy the annuity my employer offers me. 2.50% doesn’t seem like a lot of return. I could easily generate that from any dividend growth portfolios or even buy long term bonds and collect a 3% interest.

While the choice at this stage seems obvious, I know it may not always be the case. For this reason, I’m pushing the calculation a little bit further. The first thing I did was research for my life expectancy. The annuity option is giving me a fix payment no matter how many years I live. Therefore, if I’m about to use the lump sum amount to finance my retirement, I need to know for how many years I should need a “pension payment”. According to this calculator, if I’m 36 today, my life expectancy is 78:

Then, I did some more research and used life tables from StatsCan since I’m Canadian. Those life tables are super useful as they give you plenty of stats depending on which age and sex you are and in which province you live. After some thinking, I decided to use the age of 85. This is more than my life expectancy and it is still a reasonable age.

Now, it’s time to get back to my Excel spreadsheet and use the PMT function (stands for payment). This function will calculate how much my lump sum amount will generate monthly over a defined period (20 years in this case since I’ll be retiring at 65 and live until the age of 85.

Upon this calculation, the commuted value could generate an annuity of $4,152.85 per month for 20 years.

At this point, I’m now comparing the following two options:

  • Option #1: receive a fixed annuity of $1,228.17/month or $14,738.04 starting in 2046
  • Option #2: receive a fixed payment of $4,152.85/month or $49,834.20 starting in 2046

Please note I’m using the word “payment” and not “annuity” in the option #2. This is because I will still need to generate 6% investment return during the following 20 years after starting my withdrawals. The main advantage of the annuity is that the payment is known… and guaranteed!

The second option is more than three times the annuity of the first option. The disadvantage is that if I live until the age of 95 instead of 85, I will be left with nothing for the last 10 years. However, the money generated during the first 20 years is more than enough to cover for the last 10 years. Plus, if I die prior to 85, option #2 will continue providing the same large payment to my wife while option #1 will reduce the annuity to $736.90 per month.

Reverse calculations

When you are about to take a decision between keeping your pension or taking its commuted value, you cannot make enough calculation. Each scenario you explore will bring you closer to a fully rational decision. For this reason, I’ve decided to do some reverse calculations. Instead of comparing the two options as pension payments, I will now determine the future value of the annuity in 2046. This will enable me to compare the value of $587K vs what worth an annuity at retirement. This time, I’m using the function PV for Present Value. This function will calculate the present value of my annuity on the day I’m about to receive it (in 2046):

The value of my annuity in 2016 is $173,643.97. This is a lot less than what I expect to have if I manage my own money, right?

The final step of this calculation is to find out at which rate of return I must invest my lump sum amount of $108,362.19 today if I want to reach the amount of $173,643.97 at 65. By using the function RATE, for rate of return, I will find it out:

The result is a meager 2.38% return.

Then again, the result of my calculation brings me back to the very same conclusion:

Taking the lump sum over the pension is the most profitable option

Optional calculations

As this stage of my analysis, numbers speak for themselves. It’s almost impossible to fail if I take the lump sum today. Especially considering my investment background and past investing results (you can look at my investing performances here).

However, there are further calculations you can make if you want to be 100% with your decision. For example, if option #2 would have resulted in a payment of $1,500 per month, the decision between the pension and the lump sum would not have been clear. The first thing I would have done is to run 3 different scenarios of investing returns with 4%, 5% and 6%:

Rate Result
4% $337,943.90
5% $446,033.47
6% $587,148.38

In all cases, I’m getting numbers way larger than the $173K I calculated. But to be fair, I would also run the same calculation for the Present Value of the annuity value:

Rate Lump Sum Annuity Value
4% $337,943.90 $203,940.84
5% $446,033.47 $187,840.55
6% $587,148.38 $173,643.97

Then again, the difference, even if I invest at 4%, is substantial enough to make no hesitation in my decision.

Why both options are so different?

One may ask if I didn’t run my calculation properly. After all, there is a huge difference between the pension value and the lump sum potential. Is it because my employer is such a bad investor? Or because I’m a market genius? Both answers are “NO”.

As I’ve highlighted earlier in this article, we are currently living in a low interest rate environment and pension regulations favors lump sum valuation. Since interests are low, the employer must invest additional money in the account today. This is why my lump sum is worth a lot more than the guaranteed annuity. In the documents I received, pension calculations were using 2% growth for the first 10 years and then 3.4%. We are far away from the 6% I expect. Still, matching their investment return seems very easy.

How I will invest my 108K

I’ve just completed all the paperwork and now I am patiently waiting to receive my cheque. In the meantime, I’m building my watch list using my Rock Solid Ranking (a ranking of companies I follow with upside/downside potential according to the dividend discount model). The idea is not to get overwhelmed by the amount of money to invest, the number of stocks to follow, or the noise generated by the market. The most important step is to determine a solid investing strategy and STICK TO IT. For this reason, I will follow the Dividend Stocks Rock methodology as it has been proven to be quite successful over the past four years.


Leave a Reply