Insurance is a very popular investment product among traditional investors. Though insurance is primarily a risk mitigation tool, many people use insurance for the purpose of long term investment. These people buy endowment and money back plans sold by insurance companies. Why it is that insurance is used for investments? There are two reasons for this- 1) Insurance agents mis-sell insurance as they want to get maximum commission and 2) People buying insurance feel it is a good return option along with insurance cover. However, most of traditional insurance products (ULIP can be an exception, depending upon market performance) don't provide returns even close to other traditional investment products like PPF, Bank deposits etc.

How do you know what is the return offered by the insurance product that you have purchased? If you ask a person who has purchased an insurance product about the returns, he will not be able to give answer to this question. So how do you calculate returns offered by the insurance policy? Let me take an example. Suppose you have purchased the following endowment plan:

1) Policy Term 20 years

3) Premium amount 25000 per year (Assumed)

4) Sum Assured 5 lakhs

5) Maturity value (Sum Assured plus Bonus)

6) Bonus: 3.5 lakhs (Assumed)

To calculate return for this investment, you need to use excel. You can go to XIRR function in the excel and input values like this for purpose of calculation

Date Cash Outflow-Premium Paid Cash Inflow Net Cash
1-Jan-11 -25000 -25000
1-Jan-12 -25000 -25000
1-Jan-13 -25000 -25000
1-Jan-14 -25000 -25000
1-Jan-15 -25000 -25000
1-Jan-16 -25000 -25000
1-Jan-17 -25000 -25000
1-Jan-18 -25000 -25000
1-Jan-19 -25000 -25000
1-Jan-20 -25000 -25000
1-Jan-21 -25000 -25000
1-Jan-22 -25000 -25000
1-Jan-23 -25000 -25000
1-Jan-24 -25000 -25000
1-Jan-25 -25000 -25000
1-Jan-26 -25000 -25000
1-Jan-27 -25000 -25000
1-Jan-28 -25000 -25000
1-Jan-29 -25000 -25000
1-Jan-30 -25000 850000 825000

The first column in the excel input criteria is dates on which you pay premium. Since the premium is annual, it is paid for 20 years. The 2nd column reflects the annual premium paid per year for 20 years. Since this is cash outflow, premium paid is shown in minus (-).The cash outflow in 20th year is the amount received after twenty years which is equal to sum assured plus bonus (both are assumed values). The last column shows the net cash inflow, which is positive in the 20th year and is difference between cash outflow and inflow in 20th year. After getting net cash value, use XIRR function. Provide input criteria as net cash beginning to end value first followed by start date and end date. The result given by the formula gives you an idea about percentage return on the policy. In this particular case return would be 5.26% per annum. This can be used for monthly, quarterly and half annually premium also.

The same calculation can also be used for money back policy. The only difference between endowment plan and money back policy is that money back will have multiple cash inflow in place of single cash inflow in case of endowment plan.