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 type 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**

- There will be years when money goes out of our pocket , we have to put negative value . For example if we pay premium of 20,000 , we will pay -20,000 .
- 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 .
- If we pay 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 !

joydeep choudhury

Dear Sir,

Can you describe in details how to calculate IRR manually? Also please describe the differences between IRR and XIRR …Thank u very much Sir….

Manish Chauhan

Read this http://www.jagoinvestor.com/2009/08/what-is-irr-and-xirr-and-how-to.html

Pradeep

I have paid Rs.25000 for 5 years for a 10 year policy and the 6th instalment is due. But I feel the investment is not really growing even as compared to a bank FD. The present value of the fund is Rs. 153930. How to calculate both the overall and annualized return on this same. Please help. Thanks

Manish Chauhan

In the same way as explained in the video

Castial

Hi Manish,

Today i meet with a insurance agent.He told me about the plan hdfc life sampoorn samridhi . He told me that if i invest 50,000 per year. After 10 years i am going to get 950000.( Sum Assured(500000) + Reversionary Bonus(150000) any Terminal Bonus(150000) + Enhanced Terminal Bonus(150000).

Is it remotely possible.

Please let me know.

Regards

Castial

Manish Chauhan

Yes, its possible , the amount is close to 2 times what you put , thats very normal , however make sure its written in policy !

Manish

Bala

Hi Manish.

I have two money back policies of yearly premiums 33000(for 20 years) and 44000(for 15 years) and sum assured of 5 lakhs for both premiums.I paid 33000 for 2 years and 44000 for 1 year.Now i am thinking its waste of money to invest around 75k in LIC and i started these 2 LIC’s because of my relatives who are LIC agents.Now i want to stop one of these LIC policies and i think i have to stop the second one(44000 one).Is this a good decision? or is there any way around?

And it wont effect the first LIC policy right?

Manish Chauhan

Bala

You can stop it , but you know that it will mean forgetting everything paid till now !

Bala

Is this a good decision or not? Is there any other alternative other than this?

Manish Chauhan

It cant be decided by me . If you surrender now , you will loose the money right now , but your future premiums will be used in a bettter way ! .

Bala

I need help from you in deciding that.How can I invest this money in a better way

Manish Chauhan

If I were at your place , i would have surrendered , because i focus more on cleaning the clutter and make a fresh start .

Hema

its not mentioned there, it seems a blank space there against maturity sum ( as i already mentioned only two sums are written against 3 rows ( maturity, death and accident)

Manish Chauhan

Better discuss it on our forum now – http://www.jagoinvestor.com/forum

Hema

Now that you mentioned it, I went back and checked the papers. It has three rows – maturity SA, death SA and accident SA. There is 7,50,000 written twice, which is sort of hanging between all three rows very conviniently. Obviously my hubby( Its his policy and I was not involved at all during the time he joined) was convinced tht maturity sum is 7,50,000 – he was made to believe so! We have added premium for accident, so now I think maturity SA might not be 7,50,000. How do we confirm this?

Hema

Hi,

When i searched in google, i could see the LIC maturity sum table for saral in few websites and it showed me around 6.38L as returns for 3k per month for 10 years. Now will this be correct and in that case, it gives 10% IRR?

Manish Chauhan

The right figure will be in your policy document , look at that

Manish Chauhan

Look at this example . http://in.answers.yahoo.com/question/index?qid=20090514233035AAvYJBe

Here the guy is paying 1500 per year for 10 yrs and the sum assured at the end is close to 1.63 lacs , as you are paying 3k , it would be 3.3 lacs . This is something which also goes with the standard return LIC policies given like 5-7% . May be you want to scan the documents to me at manish @ jagoinvestor . com and i will have a look.

Also see http://www.jagoinvestor.com/forum/regarding-jeevan-saral/1627/ , you will get some idea . Note that agents give illustrations with 6% and 10% returns and I suspect that the number which you are looking at is given by agent with assumption of 10% return, which is not guaranteed.

Catch your insurance agent , where is he ? get clarity from him on this.

IS the docuemnt you are looking at is sent by LIC after you took the policy or was it a sheet given by agent ?

Manish

Hema

Hi,

I am getting a IRR of around 13% for my jeevan saral using excel method. SA of 7.5 lakhs in 10 years at monthly prem of 3062. Am I wrong anywhere?

I have no idea of bonus, so took maturity amount as SA itself.

Regards

Hema

Manish Chauhan

I dont think your SA is 7.5 lacs, the death benefit is 250 times of monthly premium , so that way your death SA is 7.5 lacs, but how about the maturity sum assured, it must be different , I agree that given the numbers the IRR is turning out to be 13% . Check your maturity value after 10th year ..

Ravi Datt

Hi Manish

First Question:

I have read a lot of your article where you have mentioned so many times that ULIP are not able to give good returns and you don’t recommend them. But as per your video it shows a decent IRR of 23.32% for 12 years term. Somewhere in the comment you have replied to Mr. Viral that 7-8% IRR is good for completaly safe product and 11-12% IRR for equity. Now ULIP is give more than both the cases. Can you explain how a ULIP is bad investment option?

Manish Chauhan

Ravi

That must be just an example to explain the concept of IRR , and not the authentic numbers ! . ULIP’s btw can have good returns too , all you need to know is how to handle them .