---
tags: excel
---

# ULIP- Unit-Linked Insurance Plan

This is a product which is intended to give both return as well as the protection of the life insurance. If you lack the financial discipline to invest on an yearly basis, you could have it as option.But better to look at the return after many charges hidden inside this scheme

Advantages:
Income tax benefit: If the sum assured is 10 times the annual premium and the total premium for an year is less 2,50,000 then you will become eligible tax deduction upto 1,50,000 under section 80C

Insurance Cover- You get either the sum assured or an amount based on the premium paid as a death benefit

Charges
Fund Management Charge: 1-2%
Insurance Charge: Insurance provider removes units from the fund as the charge for providing the death benefit till the fund value is lover than the sum assured

Disadvantage
Lockin period: 5years (more than many other TAX saving instrucments)
Insurance Cover: You need to have very high premium to get a decent insurance cover
Return: Very low return and as your age increases the insurance charge also increases (Check for IRR instead of absolute return to understand this)

Scenenrio based calculation
You are investing 100,000 a year for 10 Years for initial NAV value of 10, after 15 years, you are getting 20,000,00 and NAV value is 20. Sounds amazing isnt it? The insurance agenet will show you that on 10th year, you would have paid 10lakh and after 5 years of that, you get double the money.
So Absolute Return: (Current NAV-Initial NAV) x 100 / Initial NAV= (20-10)*100/10= 100%

But this absolute return is not taking into consideration of time period your full amount was sitting there. To calculate that reurn over the years, we need to calculate Compuounded annual growth rate (CAGR)
CAGR= {[(Current NAV value/ Initial NAV value) ^ (1/ number of years)]-1} *100
={[(20/ 10) ^ (1/ 5)]-1} *100= 14.87%
This also sounds good. But you didnt invest 10 Lakh at the end of 10 year and got 20 Lakh. You invested 1lakh each for 10 years. So we need to consider the performance of the money you kept on investing each year too. For calculating that, you need Internal Rate of Return (IRR). In excel the formula to calculate IRR is =xirr(values,dates). So this is also known as XIRR

For using the XIRR, we will enter all the money that goes out from your pocket for investment in negative and the money comes to you at the end of 15 year as positive. For calculation purpose, all the dates of investment and return are cosidered as start of the year

So by this calculation, you are getting only 6.6% return of your money. Is this justifiable for getting an insurance cover? If you want to invest in a tax saving market linked scheme, why not cosider an ELSS fund which gives around 12% conservatively over this period of time? And you can get 1Crore insurance for around 10,000Rs which doesnt change as you age.
Note:
Situation of every individual is different and this ULIP may be best suited for your situation. Please do your own investigation
12% reurn on ELSS fund is based on past returns and is not an indicator for the future return