## Calculate returns from your Insurance Policies [Video]

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 ! .

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 !

Subscribe
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
joydeep choudhury
9 years ago

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….

9 years ago

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

Castial
10 years ago

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.

Regards
Castial

Bala
11 years ago

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?

Bala
11 years ago

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

Bala
11 years ago

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

Hema
12 years ago

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)

Hema
12 years ago

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
12 years ago

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?

Hema
12 years ago

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

Ravi Datt
12 years ago

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?

syam
12 years ago

dear sir
your video on calculating irr is very good.had i seen this video before 5 years i would have made some wise decisions. thanks for educating us. can u please tell me how to calculate irr for monthly investments(in sip and chits) how it differentiate between month and year.

ashalanshu
12 years ago

Dear Raghu, You want to make wealth for yourself or want to award the fund manager for top quality returns.

Please do n’t look for the return in isolation. Please check the fund’s performance over the past 5-7-10Y period. Please check how much risk it’s taking to generate that return & are you ok with that kind of risk?

For a Yes – No type answer for your direct query, you may invest in any of the fund mentioned by you as you already have info that these funds ‘ll give you what you want to get?

Thanks

Ashal

Veerendra
12 years ago

Hi Manish,

Does this IRR method can be used to calculate returns from real estate investment?

I did one comparison study here. Please let me know whether this is right or wrong. Also add your valuable suggestions for calculating IRR.

Mr.A bought a flat for 40Lacs down payment. He sold the flat after 20 yeas for 4Crores. IRR in this case is coming 12.20%

Mr.B has bought a similar flat for 40Lacs at the same time of Mr.A. He payed 10Lacs down payment. And he paid 32500 rupees every year towards housing loan EMI of the flat. In total his cash outflow is 75Lacs. IRR in this case is coming 12.78%

Mr.A Mr.B
-4000000 -1325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
0 -325000
40000000 40000000
12.20% 12.78%

Could you please explain why and how in the above cases.

Thanks,
Veerendra

Veerendra
12 years ago

Hi Manish,

Sorry for bad formatting of a table in the above comment. However I hope the message is getting conveyed.

Thanks,
Veerendra

ashalanshu
12 years ago

Dear Veerendra, the difference in the IRR is coming from the fact that Mr A paid 40L up front where as Mr. B paid onkly 10L Rs. upfront & remaining amount over the period of home loan. So even after paying the interest on 30L loan amount, Mr B is able to get better returns.

Thanks

Ashal

Veerendra
12 years ago

Hi Ashal,

I do part per-payment of my home loan thinking that, I am saving a lot of money on interest. Now I need to carefully calculate before per-paying any further amount.

I have considered 8-8.25 interest rate while calculating the yearly EMI in above example. So I need to consider the change in floating interest rates (which will affect the loan term) and calculate and then come to a conclusion.

Thanks,
Veerendra

ashalanshu
12 years ago

Dear Veerendra, if you are able to earn better return than the home loan rate, you should opt to invest. If you are not able to earn better rate, prepayment is a good choice.

Thanks

Ashal

Veerendra
12 years ago

Hi Ashal,

So you mean to say, if my home loan interest rate is 10.5% and I am able to earn 12% returns in any other investment, then I should not do a per-payment.

Where as, if I am not getting more than 10.5% returns I should go for per-payment.

What about if you are not sure about the rate of return in any other investment. Where as you know exactly how much percent interest you are saving.

Thanks,
Veerendra

ashalanshu
12 years ago

Dear Veerendra, If you are not sure of your earnings from the investment, prepaying is a better choice.

Thanks

Ashal

Raghu
12 years ago

Hi Manesh,

I am planning to invest Equity related mutual funds through SIP .
Could you please suggest me the best three mutual funds in market.
i am expecting max returns and can afford long term .

Please guide me for the same.

Regards,
Raghavebdra

ashalanshu
12 years ago

Dear Raghu, Can you explain your best funds?

You may invest in HDFC Tax Saver only.

Thanks

Ashal

Raghu
12 years ago

Hi Ashal,

Could please suggest me the top 2 best funds from the below list. as i got the information below funds are performing well .
2) Birla Sun Life MNC Funds
3) HDFC top 200
4) Franklin India Bluechip (G)
5) Fidelity Equity Fund (G)
6) HDFC Equity fund

Regards
Raghavendra.

Harini
12 years ago

Hi Manish,

First up all I would like to congratulate the way are replying for all quarries . you are providing the valuable information to all the Investor’s. Right now i also here to take you advise.

i am planning to invest HDFC Ulip plan’s can you suggest me the best plan available in HDFC(due some reason i have to invest in HDFC only ). i am ready to take some risk regarding my investment . at the same time the plan which i am going to invest come’s under tax saving(80 c).

Please guide me regarding the same…

Thank you very much ..

Regards,
Harini

ashalanshu
12 years ago

Dear Harini, From your query it seems, you are being forced to purchase a HDFC policy either some one a close relative or friend or even your boss to which you can’t deny out rightly. For the given situation, my take ‘ll be to go for a term plan.

Thanks

Ashal

Raghu
12 years ago

Hi Manish,

Thank very much for your valuable information.

Regards

Raghu
12 years ago

Hi Manish,
This is Raghu..
Recently i purchased HDFC life Sampoorn Samridhi Insurance Plan.. 20,000 Premium for 20 years term..

At the time of purchasing the agent was told me that i am going to get 12 to 13 lac’s at the time of maturity(Including all the bonus they offered for this plan. ) .

But after going through the details and calculations i guess i won’t get that much of amount at the time maturity.

Please let me know the returns point of how best this police is…

i am looking for some fixed returns .. keep this point ..please let me know weather i should continue this police or not

Regards
Raghu

S Shetty
12 years ago

Hi

I am looking out for a term insurance plan of 20 lakhs for my husband (age 43). My friend has suggested Aegon religare or ICICI. Is it true that term insurance plan pays us only if the death is natural and It does not cover death due to accident. Can you suggest any good term insurance plan, which does not have any such clauses.

Raghu
12 years ago

Hi Manish,
This is Raghu..
Recently i purchased HDFC life Sampoorn Samridhi Insurance Plan.. 20,000 Premium for 20 years term..
Please let me know how this police will perform.. as i am expecting some good returns.
Regards
Raghu

Raghu
12 years ago

please suggest me weather i continue or with draw in between..and suggest me some good policy .. i am ok with long term one also…

Shankar Ramasundaram
12 years ago

Dear Manish,

I have Birla Sunlife Flexi-Save Plus (20 Year Term) insurance since Jul 2005. (Annual Premium – Rs. 5076 and SA is Rs. 1 Lac). I have paid till Jul 2011 (7 Years paid).

Should I continue for 20 Years or surrend it now?

Regards, Shankar

Darshan
12 years ago

Nice article manish just one thing is IRR same as Yield of the Policy. can we called IRR as yield.

S Shetty
12 years ago

Hi manish

Your video on IRR was terrific. I never knew about such calculation before. I am planning to take limited endowment policy form LIC. The premium paying term will be 5 years and term 20 years. The sum assured is 2 lakhs, and the yearly premium is 27038. The maturity amount will be 4,36,000/-(as per the agent) How to calculate the IRR of such policies.
Will it be a wise thing to take such a policy. Before taking the policy I need your advice. If you have any other suggestion please let me know. Thanks in advance.

S Shetty