What is the return on your insurance policy?

Do you consider the return on your insurance policy when you buy one? Well, here is a scenario that many of you can relate to!

You have been approached by an insurance agent. The agent tells you that there is a great new money back plan. It’s simple – you pay Rs.1 lakh per year for 20 years. After that, the policy will pay you Rs.1.5 lakh per year for the next twenty years. Not just that, as a parting gift, when the payout is complete, it will give you a lump sum of Rs.40 lakh!

If this hasn’t already got you excited, he puts it in even better terms. You pay Rs.20 lakh and the policy pays you Rs.70 lakh, with all payments guaranteed, he says. Then the agent hands over a brochure with the details of the policy. Among other things, it has an illustration showing how the premiums and payments span over time. Here’s a graphical summary of the numbers.

But what are the returns in this policy? Without returns, you cannot say whether the policy is good to go for or if even a plain old FD would serve you better.

The equation you remember from school: Future value = PV * (1+r)^t (where PV = present value, r = rate of interest, t = time) looks too insufficient to accommodate all these numbers. 

All right, so you call your finance friend. They say – find XIRR! After some Googling, you got an idea about what to do. You have to enter these 41 cash flows into an Excel spreadsheet with dates and use the XIRR formula. So, what’s the problem with a bit of math? 

You open up your spreadsheet, input all those values – which took a good while – and bingo! You realise that the policy has less-than-impressive returns at just 5.09%. 

You called your finance friend to ensure you didn’t make a mistake in calculating, as the returns appear too low compared to the initial expectation based on the explanation of payments and credits. But your friend says that returns actually may be even lower, as you didn’t account for GST and because of the way a ‘policy year’ is calculated! 

As it turns out, the first year’s premium is subject to 4.5% GST and the remaining carry 2.25% GST. Two, when you pay a single yearly premium, it is at the beginning of the policy year. However, when the policy pays you, it is at the end of the policy year; effectively, this adds a year to all the payments to you! 

After adjusting for these, the policy returns turned out to be 4.9%. So, wouldn’t an FD be far better?

But can you do this every time your agent or relationship manager or friendly neighbour pitches these policies to you? There are policies where payments last for a hundred years! Is there a simpler way to figure out returns for an insurance policy that promises payouts?

There is. We have built a spreadsheet calculator for it!

The solution

In order to give you a better way to calculate the XIRR of policies, we have created the insurance policy return calculator. With this calculator, you can calculate XIRR for almost all kinds of policies out there. Here’s how to use it.

How it works

Basically, all cash flows in policies such endowment or money back can be put into one of the four categories below:

1. Lumpsum payments: One or a few premium payments. These can be the same or different amounts.
2. Recurring payments: Periodic premium payments of a fixed amount for a predefined duration. The payments can be monthly, quarterly, or yearly.
3. Lumpsum credits: One or a few payments to you by the policy. This can be at policy maturity or during the term of the policy. The payments can be of the same or different amounts.
4. Recurring credits: Periodic payments to you by the policy. This will be of a fixed amount, spanning for a period of time. The payments can be monthly, quarterly, or yearly.

A and B above are what you pay to the policy, C and D are what you get back from the policy. The tool will require details of all premium payments and credits that fall into any of these four categories. The image below gives you an idea.

You will need to enter details according to your policy. Therefore, to use the tool and get the XIRR, you have to assume a starting date. Then enter the rest of the dates and the amounts according to the policy details. 

Let’s see how to calculate XIRR for the policy we’ve seen earlier. What we know:

1. There are 20 premium payments of Rs.1 lakh
2. There are 20 credits of Rs.1.5 lakh after 20 years of premium payments
3. There is a single credit of Rs.40 lakh at the end of the period

Here’s how to put this information into the four categories in the tool:

1. Lumpsum payments: We will mark the first premium of Rs.1 lakh as a lumpsum payment. Even though the payment is Rs.1 lakh per year for the 20 years, as the first instalment has a separate GST of 4.5%, we will enter this one separately. For the purposes of the tool, we assume that the first premium payment happens on 1 Jan 2024 (you can pick the GST from the drop-down in the cell).

2. Recurring payments: The 19 premium payments starting from year 2 will be entered as a recurring payment. The second payment will thus be on the 1st of January 2025. Since it’s an annual payment, we’re picking the ‘yearly option. The GST applicable here will be 2.25% (you can pick the GST from the drop-down in the cell).

3. Lumpsum credits: The lumpsum credit of Rs.40 lakh belongs in this category. As this final payment happens after the policy year 40, enter the date as the 1st of January 2064.

4. Recurring credits: And finally, the 20 credits of Rs.1.5 lakh will be in this category. The first recurring credit from the policy is at policy year 21, or starts after completion of 20 policy years; hence, we have to enter the date as the 1st of January 2044. Since it’s an annual payment, we pick the ‘Yearly’ option.

Once you have entered all these details, the XIRR will be calculated. See the snapshot of the tool’s page with the inputs below.

Download the insurance policy return calculator spreadsheet here:

Note: Sometimes, policies will provide illustrations with premiums including GST. In such a case, do not mark GST again in the tool. See sample policy 3 for an example of this. A tip to identify if GST is included is to compare the first year’s premium against the remaining. If it is around 2.2% higher, the illustration includes GST.

Perpetual policies

Occasionally, we come across policies that promise to pay for life. For such policies, we won’t know the exact number of credits. There’s a way to work around this in the insurance return calculator. Let’s see how – with an example, of course!

Example policy: The policy promises you to pay income for life and guaranteed death benefits to your nominee. The details are:

• You have to pay a single premium of Rs.10 lakh.
• After 30 years, the policy will start paying you Rs.1.5 lakh per year for life.
• Upon your death, Rs.35 lakh will be paid to your nominee.

Here, we have all the details except for:

• Number of credits
• Date of payment of lumpsum

The workaround is to build different lifespan scenarios and to see what returns may be. Let’s say a 30-year-old is considering the policy. So let’s calculate the returns for a lifespan of 80 years. In this case:

• 30-year-old pays Rs.10 lakh single premium.
• Income starts at Age 60.
• Income is earned 20 times (age 60 to 80)
• Lumpsum benefit is paid to the nominee at the policyholder’s age of 80.

Assuming the start date is 1st January 2024, the calculation will be as follows:

Now, let’s recalculate for a lifespan of 100 years for the same 30-year-old:

• 30-year-old pays Rs.10 lakh single premium.
• Income starts at age 60.
• Income is earned 40 times (from age 60 to age 100).
• Lumpsum benefit is paid to the nominee at the policyholder’s age of 100.

Tool limitations

The insurance policy return tool is built to accommodate multiple variations in payment & credit terms to cover most policies. However, there may be some outlier policies. Presently, the tool can handle up to 5 lumpsum payments and credits. The recurring payments and credits are limited to 1200 (enough to calculate monthly payments and credits for up to one hundred years). However, if your policy has more payment dates than these, the insurance policy return tool will be unable to calculate XIRR for these.

Sample policies

Now let’s check returns of some real policies that are available in the market with the insurance policy return calculator, to give you a better idea on how to input data into the tool and what the returns are. The payment details are as given in the sample illustration we managed to find. Depending on factors like sum assured, age of policyholder, the return applicable to you may differ if you look at the same policy.

1. LIC’s Child Money Back

The sample illustration we had was for a 2 year old. This policy is for a duration of 24 years (starting first payment at year 2, final payment at year 25, total 24 payments), where you have to pay Rs.1,40,481 per year. The policy does not give any recurring credits. However, it gives 3 money backs after years 17, 19, and 21 of Rs.6 lakh each. The final maturity after year 24 will be Rs.50.55 lakh.

Once we enter the data, we get the result as 5.71%

2. LIC’s Jeevan Utsav

Here as well, the sample illustration we got was for a policyholder of 2 years old. The payments will have to be done until age 17; i.e, total 16 yearly payments. The premium is Rs.1,59,593. After age 20, i.e, 19 years after first premium payment, the policy will start paying you Rs.2,50,000. The illustration shows payments until age 100. The lumpsum payment of Rs.40,00,000 will be paid at age 100. Do note that this is a perpetual policy and the illustration assumes a lifespan of 100 years. (Also read: LIC’s Jeevan Utsav - does it really provide 10% returns? - PrimeInvestor)

Once we enter the data, we get the result as 5.35%

3. LIC’s Jeevan Tarun

Here as well, the sample illustration is for a 2 year old. The premium and benefits are given in a table - the image is shared below. In this case, however, the premium values are inclusive of GST as you can see in a snapshot of the illustration below.

As the illustration has given premiums inclusive of GST, we have to enter the details in the tool with no additional GST (GST = 0%)

Once we enter the data, we get the result as 6.08%

4. HDFC Life Guaranteed Income Insurance Plan

This illustration was for a 40 year old. Premium payment is for 15 years, at Rs.2,00,000 per year. The benefits were also for 15 years, at Rs.3,98,228 per year, starting from 16th year. At the end of the policy period, there will be a lumpsum credit of Rs.30,63,289.

Once we enter the data, we get the result as 6.38%

You can download the insurance policy return calculator and key in details of policies that have been pitched to you, too, here:

Conclusion - is the return on your insurance policy really good?

Most of the policies illustrate payments and benefits in a linear timeline. Some also sum up all the premiums and credits to show how your investments become X times! To understand things in a comparable form, it is always better to look at XIRR, which accounts for time value of money. We hope with this tool, you’ll be able to calculate any policy XIRR with minimal efforts when an insurance agent presents you a brochure with the policy benefits illustration.

Important: The Spreadsheet calculator uses a function that is not supported in some older versions of MS Excel. If you notice the tool is not recalculating based on new inputs, upload the tool to One Drive and open it in MS Excel Online. This spreadsheet is not supported on Google Sheets. We are exploring developing this spreadsheet into a calculator tool, as part of our Calculators.