How many times have you come across a situation when you wanted to know the returns from your Policies , It can be Endowment Plans, Money-back plans, Pension plans or a ULIP plan . You might be some money going out of your pocket in some years and money might be coming in your pocket in some years, which would eventually translate to some return overall . In this video tutorial, we will see how you can use MS Excel and use a tool called IRR (Internal Rate of Return) to find out the returns from your policies.

When can you use IRR?

Actually, IRR is a tool which you can use in any kind of situation where you are paying some premium across some fixed time frame, like per year or per month or any period with equal gaps! , not random payments with unequal gaps.

For the sake of simplicity, I have taken the case of yearly payment in this article. In the above video, I have covered 4 types of situations, like See More Financial Calculators

• Endowment plans with maturity amount
• Moneyback plans with money coming back to you in between
• Pension Plans
• ULIP Plan

Important Points

1. There will be years when money goes out of our pocket, we have to put negative value. For example, if we pay a premium of 20,000, we will pay -20,000.
2. In years when we get some money, we have to put positive value, like if we get 20,000 in some year, we have put +20,000.
3. If we pay a premium of Rs 20,000 in some year and we also get 25,000, eventually, the money coming to us is Rs 5,000, so we put +5,000 for that year.

Bonus Quiz to test your understanding!

Ajay bought a pension plan with maturity tenure of 15 yrs , but his premium paying term was only 10 yrs . So he does not have to pay anything after 10th year .

He is paid the premium of Rs 40,000 each year for 3 yrs, but after that he missed paying premiums for 4th and 5th year. He revived his policy in 6th year and payed 6th year premium along with 4th & 5th year premium with 8% interest (8% interest on 80,000)  in the 6th year and thereafter He continued paying the premiums after that till 10th year . After the maturity period of 15 yrs, he has two options

Option A) Get 4,00,000 lump sum + pension of 25,000 for next 40 yrs , starting from 16th year

Option B) Take the lump sum of 10 lacs and Policy terminates

Question : Which option should Ajay choose ? which one is better than the other ?

Lets see who gives the right answer !

So now if someone tells you that you can invest Rs XXX for Z yrs and get amount Y for next ABC yrs you can find out how much IRR its turns out to be , if its claims to be a safe fund and IRR is more than 9-10% , you can clearly see that its a pure cheating ! .

Your Homework

Now go back and take out your ULIP’s , Insurance Plans and use this method to find out what is the return you are getting out of those policies , are you satisfied with it? if not , its time to rethink if you really want to continue those plans or not . Take Action !

So , go ahead and calcualte the IRR for your policies and ULIP’s and Share your examples and numbers with everyone on the comments sections ,  I will personally verify each one’s number and confirm if those are right or not . Happy IRR’ing !