Comprehensive Breakdown to Insurance Plans – FREE spreadsheet!


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:


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

Author: Miss Niao

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 @! :)

17 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.


    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.


  2. I shudder everytime I think of all the money I’ve dumped into ILPs.

    In one of the ILP plans I’ve “invested” in, there are two columns for Non-guaranteed returns: 5% and 9%. I assume that these are the projected returns on my “investment”. After more than 5 years of paying my premiums, the surrender value has remained close to the 5% rate ( a few dollars lower, actually). I can’t help but think that the higher rate of projected returns are placed there just to entice us gullible customers when it is likely that the more realistic returns are at the lower end of the scale.


    1. We should ask more questions to the agent to make sure that we are fully transparent with the prices we pay and the returns. I myself have also been a victim, so I know the pain. But as the saying goes, it’s better to know now than later. Think of all the potential losses that you might have occurred even more!


      1. I only have myself to blame for being too lazy to educate myself. If I had spent some time analysing all the ILPs I have “invested” in, I would’ve saved myself a whole lot of money.

        Take this plan for example. I’m paying $1800 per year for insuring myself for a measly $10000. If the “projected” return of 5% pans out, I will break even (meaning, getting my premium back) at year 15. So, for the sake of a $10000 insurance, I’m giving up on 15 years of interest! I might as well set aside $10000 of my savings and place it in the Singapore Savings Bond (SSB), while investing the $1800 premium that I’m paying every year.


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s