Comprehensive Breakdown to Insurance Plans – FREE spreadsheet!

READ THIS BEFORE INVESTING WITH INSURANCE PLANS!

I am not sure if it’s coincidental or not, but recently I have been receiving quite a few requests from my readers and friends for evaluation of their insurance plans. Some of them were directed from the story of my ILP which was posted on HWZ, while others chanced upon my blog. Perhaps it was because of the spreadsheets that I have uploaded.

I have reviewed the spreadsheets but there isn’t one that can help to calculated exactly how much your returns would be with insurance plans. To my surprise, there are many people who don’t know how the actual calculations are being done with their plans – they only know what is the projected amount that they would get at the end of it. As long as the final returns reflect a higher (projected) amount than your final paid premiums, the normal consumer or investor would be contented enough. BF is one of the mentioned.

Well, this is definitely not ideal. Not knowing what is exactly done with your money can be disastrous, in my opinion. If you have no idea how disastrous it is, I redirect you back to my experience with my ILP so that you can get a better idea about it. And as the saying goes that ignorance is bliss, to me, it’s just another way of saying that money is not important to you, which on the contrary, contradicts why you even bought the plan in the first place.

So I’m here to debunk all these nonsense for you today. As long as you’re willing to keep an open mind about it.

Thanks to one of my readers who was very cooperative, I was able to perform an entire breakdown of how insurers calculate the plan returns. You know how you’re presented a table full of figures that tells you how much you’re guaranteed and not guaranteed when you buy their insurance plans? And how they are always dodgy about their answers when you ask them why you don’t get any returns in first year of your plan?

I don’t have 100% of the answers, but with the rest of this article, I believe about 80% of your doubts can be clarified. Since every plan differs from one another, and insurer to insurer, this will only provide you a general guideline. Also, I have never worked as an insurance agent before and the closest that I’ve gotten involved with insurance is as a client, so my information would be limited to word-of-mouth and stuff that you can find from the world wide web.

BTW, before you continue, if you need a crash course on what insurance plans are available on the market, go to my article on Buy Term, Invest the Rest. For this spreadsheet, only Whole Life plans (e.g. ILP, endowments) would be covered, since there will be a portion of your premiums that would be used for investing.

Here’s how the information would usually be presented to you:

insurancetable.PNG

Now, there are a few key ideas that you would have to know before hand.


1) There are two types of deductions done to your plan. 

First deduction

The first deduction comes directly from your premiums paid. Every year, when you pay your premiums, part of it gets deducted to pay for stuff like the following:

Insurance agent commissions (~50% from the first year of premiums, ~20% from the second year, etc.)

Insured sum in the event of Death, TPD, CI, etc or whatever your plan covers. This is the “Term”/insurance part of your plan. (Varies on coverage)

Miscellaneous fees (E.g. Administrative, Surrender fee, and other fees that nobody in the world would know why you’re charged for)

The total fees would then be deducted from your premiums, and determine your guaranteed sum every year. Take note that these fees are fixed, and provided by the insurer.

Normally, in the first year, you are not guaranteed anything because 100% of your premiums are used to pay for the above mentioned.

Second deduction

Once your yearly guaranteed sums are determined, the plan will calculate your projected returns from these figures. Every year, your guaranteed sum (NOTE: NOT YOUR TOTAL PREMIUMS PAID!) will be placed into a professionally managed fund and generate a projected return of 3~5%. This fund would be charging a yearly fee of 1-2% (because apparently, professionals are expensive) of your total invested capital.

Example: Your guaranteed sum is $10,000 and the fund has generated a 3% gain after one year. The fund charges 1% of invested capital as its yearly fees. So, instead of actually getting $300, your non-guaranteed sum will be only $200.

2) Returns are calculated using XIRR

Time-weighted returns would not be very useful in this case, since your performance would most likely be following the returns of the funds that your money has been placed into. Not only that, you have little control, or rather, limited access to the investment decisions. Therefore, I will use XIRR since it will show us the Compounded Annual Growth Rate (CAGR) of the entire plan while Dollar Cost Averaging with yearly injections of premiums.

There is also a part of the spreadsheet which you would be able to see your actual annual return rate after all deductions have been made. Be prepared to be in for a surprise when you see the actual numbers.

3) Have your plan details on hand 

Since your non-guaranteed returns are calculated from your guaranteed returns, you will have to input your guaranteed returns according to your plan. Because there are too many variables to determine the guaranteed returns (as explained in Point 1), I will be unable to provide an accurate estimate for this. I apologize for this and unless there is a fixed or generic way to calculate guaranteed returns for ALL insurance plans, you would have to do this manually.

4) Have fun!

Experiment with this spreadsheet as much as you can. The idea of this is so that you can vary the different parameters, and know how each one will influence your plan returns. You can also compare it to if you invested the money by yourself instead of using the plan. Also, I’ve made the spreadsheet calculate up to a 100 years!


How to get this spreadsheet

I would like to provide open access to this spreadsheet on my Google Drive. Unfortunately, Drive does not allow me to protect my cells since it will bypass any protection encryption from Microsoft Excel. Hence, in order for you to have this spreadsheet, you would have to contact me personally.

  1. Follow me on WordPress (What are you waiting for?)
  2. Contact me via my blog
  3. Leave your email so that I can contact you
  4. I will send you the spreadsheet via email.

If you have a solution to the above encryption issue, please also contact me so that I can provide open access. I have tried the IMPORTRANGE function in Google Sheets, but it will only copy the values of the cells, and that is not helpful at all.

I’m sure that if the people who sent me emails used this spreadsheet, it would be sufficient enough to achieve similar returns to their plan. However, if you do see anything amiss or would like me to alter it according to your needs, feel free to contact me and I’ll be glad to help.

Thanks for reading!

Miss Niao xoxo

Advertisements

Author: missniao

Hello! I blog about financial matters and things that average people can do to have a better retirement. I want to inspire people to take control of their money and have a better understanding about it. If you are interested to know more, follow me @ missniao.wordpress.com! :)

6 thoughts on “Comprehensive Breakdown to Insurance Plans – FREE spreadsheet!”

  1. Have you tried the “Protected Ranges” function on Google Sheets? That requires the spreadsheet to be hosted on google drive itself instead of just the excel file.

    You can have a setup sheet on the first sheet for users to enter the numbers and then a 2nd sheet that performs the calculations as viewed only.

    If they wish to, i think they can download or make a new copy to view the full spreadsheet privately but that removes the protection too. If you need to protect your formulas for copies, i suspect this method won’t work.

    Like

    1. Exactly, I can’t hide the formulae. I’ve tried the protect range on 1 sheet too which will work fine, but the user will still be able to see the formulae.
      So now, the real dilemma is whether I want to reveal my calculations or not. I am still pondering about this.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s