How to Calculate Insurance Returns using Excel (with pictures)

Most of us have had the experience of a smooth-talking insurance salesmen coming up to us and selling their respective insurance products. Now, there are many people who believe these salesmen and end up buying the product and yet do not know whether it was a good decision or not. But do you know how simple it is to calculate the returns on your insurance policy? It’s very simple. Knowing how will allow you to make a much more informed decision as to whether you should go for the product or look elsewhere and invest in something similar but with higher rate of return. It could be a different insurance plan of the same company or of another, or maybe a debt mutual fund or government securities or something else altogether.

Let’s use an example to see how we can calculate the returns on  a particular insurance plan:

Plan: LIC Jeevan Shiromani

Type: Endowment Plan

Sum Assured: Rs. 1 crore

Policy Tenure: 20 years

Premium Paying Term: 16 years

So, before we move on to the calculation, we need to understand what an endowment plan is. An endowment plan is basically a plan wherein you get both death benefit as well as maturity benefit. That is if you die you get money but if you don’t die you still get some money.

Below is an illustration of how much you have to pay each year and the amount you will get if you die in the said year.

Let’s understand what these columns mean. The first column is the year in which the policy is at. The ‘Risk Cover’ column tells the amount of money your nominee will receive in the event of your death during the said year. The ‘Payment’ column is the amount of premium you have to pay during the start of that year. The ‘Returns’ column tells you the amount of money the company will pay you if you have survived till that year. Important to note hear is that the amount given in this column is the amount guaranteed by the company and not the amount the salesman may tell you i.e. guaranteed + non – guaranteed bonus. It is important that you go through the insurance document very carefully as salesmen may try to fool you by giving inflated figures by way of non – guaranteed bonus, something which you may never receive at all. The ‘Cash Flow’ column is one that you need to make to calculate % returns. This column basically shows the amount of money that is going out of your pocket in -ve signs and what is coming into your pocket in +ve signs.

Now generally what people think is that if I survive, I get Rs. 22450000 back and I would have paid 11358801 over a period of 20 years so the return on investment should be

(22450000/11358801) * 100% = 198%

But then the money is put over a period of 20 years, so then to get an annualised rate of return just divide 198%/20 = 9.9% per annum. Simple isn’t it? But this is not the correct way to calculate returns. This is because you are not paying all that money in 1 go, which means that the number of years of compounding is different for every time you pay a premium. This is something that has to be taken into account to get a much better picture of the situation.

Another method which people employ is using the compound interest formula, i.e. CAGR

CAGR = [(Amount at end/ Amount at beginning)^(1/number of years)] – 1

So in the above case,

CAGR = [(22450000/11358801)^(1/20)] – 1

CAGR = 0.0347 or 3.47%

Although it takes time into consideration as opposed to the previous method, it does not consider the fact the payment is being made in equal installments over a period of time, and not at one go at the start of 20.

So, what is the right way to calculate such returns? One way is to use a discounted cash flow model where you bring the value of all payments and receipts to a single year assuming a rate of discounting. But doing that can get very complicated and sometimes even time consuming. So, what we do is we use just 1 formula on excel to get the rate of return. Let’s see how we do that:

Step 1: Arrange all payments and receipts in a single row, with payments carry -ve and receipts carry +ve sign (check image below)

 (using the example of the policy given before)

Step 2: Select a box below and type '=IRR' and select the values above

Step 3: Press Enter (the ‘[guess]’ value is optional and it means you can guess a number that your answer may be, it will however not have any impact on the answer that computer is going to give you)

There you have it. The real rate of return is 4.38% per annum. That means every year, the total money you have invested increases by 4.38%, which is a big difference from both the other calculations used before that.

This same method can be applied to other investment options as well, where a certain amount is promised to you at the end of a term but you are unsure of the rate of return. However, all payments and receipts have to be in a periodical interval, if they are not in periodical intervals then the ‘XIRR’ function can be used as long as the dates are also there alongside the sums of money.

‘IRR’ function can be applied in case of death as well, examples:

Disclaimer: I am not advocating the purchase or abstinence from purchase of an insurance policy through this blog. This is an informative blog showing the right way to calculate returns on insurance policies and other similar products.