POSTED BY February 12, 2011 COMMENTS (153)

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

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

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

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 !

Comments are closed.

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

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

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

In the same way as explained in the video

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

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

Manish

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

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

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

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

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

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

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)

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

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?

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?

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

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 https://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

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

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

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?

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 .

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.

In case of Montly investment you have to do this , calculate the IRR the usual way , you will get a percentage called p% .. Now this is assuming yearly investment , to find out what would be the monthly returns , do (1+p%)^12 – 1 . So lets say you invest 1000 per month for 12 months and at the end its 13000 . So as per IRR , you will get 1.23% return , but this is assuming 12 payments on yearly basis ., now if you calcualte (1+1.23%)^12 – 1 = (1.0123)^12 – 1 = 1.1573 -1 = .1573 = 15.73%

Manish

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?

Please ask to yourself some tough questions.

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

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

Hi Manish,

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

Thanks,

Veerendra

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

Hi Ashal,

Thanks for the reply.

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

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

Hi Ashal,

Thanks again for the reply.

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

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

Thanks

Ashal

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.

Thanks for advance help.

Regards,

Raghavebdra

Dear Raghu, Can you explain your best funds?

You may invest in HDFC Tax Saver only.

Thanks

Ashal

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 .

1) SBI magnum-emerging-businesses-fund

2) Birla Sun Life MNC Funds

3) HDFC top 200

4) Franklin India Bluechip (G)

5) Fidelity Equity Fund (G)

6) HDFC Equity fund

Thanks for your advance help

Regards

Raghavendra.

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

SHould it be ULIP only ? Else you can invest in HDFC Tax Saver mutual fund for tax saving .. I dont recommend ULIP

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.

No Ulip please.

Thanks

Ashal

Hi Manish,

Thank very much for your valuable information.

Regards

Welcome

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

Raghu

Even this plan is same like other endowment plan .. the returns should not be more than 4-6% over a long term .. And deginately you are not getting 12 lacs as promised .. just 6-7 lacs .

Also the returns are very much guaranteed !

Manish

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.

S Shetty

No, its not true .. the claim will be paid incase of Accident also . Try Aviva term plan or Kotak term plan

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

Crap policy . Its a assured return policy , the returns would turn out to be 4-5% in long run

Manish

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…

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?

Please advice.

Regards, Shankar

Shankar

DId you see wht is the effective IRR of the policy . You should make it paid up now

Manish

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

Darshan

Yeild is what they promise if you continue it till end , but IRR can be diffferent as per your case , like if you dont pay all the premiums , but make it paid up

Manish

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

U have to put -27038 in 5 cells and below that for 15 more year , you have to put 0 , at the 21st row put +436000 , then put IRR formula below

Make sure you are clear about the maturity value , because generally the figures from LIC agents are much more than reality

Manish

Thanks for the reply. I got the IRR as 7%. Before I knew about IRR I used to calculate Return on investment(ROI) for insurance policies. And for the above policy, I got the ROI as 11%. Since the premium paying term is only 5 yrs, so for a 2 lakhs policy I will end up paying 1,35,190/-. So, on that basis I make the calculation.

For insurance policies what should be taken into consideration – The IRR or the ROI. Which one will give a clear picture.

S Shetty

IRR is Return on investment only , but IRR is a generic term used for even non regular cash flows

Manish

AMAZING VIDEO OF HOW TO CALCULATE IRR FOR INSURANCE POLICY.NO INSURANCE COMPANY OR AGENT TELL ANY BODY ABOUT THIS FACTS,,,,Mr.MANISH U ARE REALLY DOING HARD WORK TO EDUCATE THE INVESTORS,,U ARE DOING REALLY GREAT WORK.KEEP IT UP,GOD BLESS U.

DC Agarwal

Thanks :). spread it 🙂

Manish

Hi Manish,

I had invested in HDFC unit linked wealth multiplier ULIP and I hv a query regarding calculating IRR of this policy.It is a 10yr policy, half yearly premium of 1,25000 , premium paying term 3yrs and policy term 10yrs, SA 12,50,000.A deduction towards statutory charges to the tune of Rs 9561.75 were deducted from the 1st premium.Thereafter mortality charges of Rs.4273.44 were deducted.So when i put the premium amt in the excel sheet , how do i account for these expenses while calculating the returns ? what according to you is the return of this policy?

Student

Better put the amount after deducting the Mortality charges only , put all the other figures ! … Because only mortality is giving you insurance benefit . Let me know what is the IRR now . Also policy premium term is 10 yr itlsef .. 3 yrs is lock in period and you can choose not to pay later .

Manish

Hi manishbhai

very nice video!

ab tak hame insurance policy ke calculations pata nahi the. aapne itane saralata se bataya ,aapaka dhanyawad.Ab hame koi agent bewkuf nahi bana sakta. bewkuf nahi banenge.

Dear Bapu, Please try to understand that the example given above of option A & B was just for illustration purpose & not the real life example. In case of NPS, it’s meant for saving for retirement & later on receiving pension from accumulated corpus.

Also after implementation of DTC, NPS ‘ll be a major instrument for retirement saving. Please take note of it.

To continue or not ‘ll be your personal call.

Thanks

Ashal

Dear Manish sir,

i am in confusion due to your example of pension option A and option B

in this answer option b is suitable

But i an investing in NPS 12000/ rs per year now i am 32 year old in nps no option b given only option a given in which 60% amount you will get at the age 60 and remaining in term of pension…

due to this i think, i will not get better IRR as you explained in above example ,so i thinking to stop investing amount now and invest in other option…

This will impact on my invested 12000 rs because i can’t get back due to lock in up to 60 ages

and penalty of non running of NPS account….

PL help me I have two option

A) Close the NPS account running as above term and forget Rs 12000 and relax in investing in mutual fund or other option….

B) My thinking or assumed calculation is wrong, I need to continue the investment it will gives better IRR ( I didn’t not calculated any possibility due to weak mathematics ….arts graduate)

Pl helps me to resolve the problem and better investment option

http://geekistry.blogspot.com/2011/02/calculating-irr-internal-rate-of-return.html — I just wrote this “calculating IRR made easy” and though it might be useful to others. Thus, sharing it here.

Mallik

thanks for sharing 🙂

Hi Manish,

When I am trying to calculate IRR for some data, I either keep getting #DIV/0! or #NUM!. What am I doing wrong?

Regards,

Arudra.

Arudra

Check the values once again , what are the numbers can you tell me ?

Manish

Manish,

I was calculating postal RD percentage. I took -1000 for 60 Months and gave 75000 on the 61st month. Now, when I calculate IRR i get #DIV/0!. Similarly, when I do few other calculations, I am getting #NUM! as the result.

Regards,

Arudra.

Arudra

Some issue is there ..Note that IRR is for yearly return ,so in your example, its like 60 yrs ! , better use XIRR, which also consider exact dates, you will get the answer

I am too facing the same error…Can any one help

Yesterday I had a chat with one of my friend and he is holding Gold bima money back policy from LIC. He is paying premium of 38000 every year for a sum assured 8000000 for 16yrs term. He will get money back of 120000 in every 4th year such as 4th, 8th and 12th and at the end of 16th year he will get 8000000 SA value.

When I did IRR calculation on this policy it is very good return of 12%. Then why people say money back policy is not good when it is giving such a good return. Or am I doing anything wrong in this calculation?

Jayaprakas

You need to go and wake up your friend , He will not get 80,00,000 at end , He will just get all premium paid . As per rules

On the survival of life assured to the end of the term plan, payment of total amount of premiums paid (excluding extra/optional rider premiums, if any) plus loyalty additions, if any, less the amount of survival benefits will be paid to the insured.

http://www.rediff.com/money/2005/oct/19perfin1.htm

That was a big blow manish to be frank. Even I didn’t read that line. I didn’t see his policy document, but he was fooled by his uncle only, that’s really sad. His uncle works in LIC and he told him to keep that policy as it gives good returns and at the end of the term he will get 8lacs. IRR for this policy is zero percentage. My god, how people buy these policies.

Let me inform him about this and see his reaction as well as his uncle reaction. 🙂

ok , note that we have not considered bonus info .

Manish

Even then it is worst. It will give 4% if bonus is considered which is around 96000 for his policy.

Manish,

Now that we did a mistake of taking Endowment policy instead of MF+Term, what are the options to exit? What are your ideas to exit the current situation and restart on the right path?

For ex, 13 yr premium paying year, policy valid for 25 yrs and guaranteed 70rs per annum for 25 years policy (LIC – New Jeevan Sree ), if one is near the end of premium paying term or already paid all premium, What is a ideal time to surrender without any loss or profit? what are the thump rules here?

Any post on this topic?

Jithu,

In the above Ex you didnt mentioned SA. But if we take minimum SA for that plan (which is 5,00,000) then accrued Bonus for the total term is (70*500000)/1000=35,000 (Per annum)*25=8,75,000 (Plus you may get loyality Bonus also). If the premium allready paid means till the end of 13 years then it is better to surrender it, as surrender value is also depends on number of premiums paid. Received amount you can invest in some other avenues where you will get good returns for the remaining period of 12 yrs (Policy yr is 25 and you are surrendering it around 13 years so left out time is 12 years). You will get good returns against what they are offering you on your policy.

Basavaraj

Thanks for the answer 🙂

manish

Jithu

What basavaraj has explained it correct. you can read this article for more : https://www.jagoinvestor.com/2009/10/what-to-get-rid-of-your-junk-insurance.html

Manish

Manish, a General input.

In some websites i have seen that when some one posts a comments, the person who posted gets a mail. Rather this facility can be enabled by the person posting. Is this facility there in JI. If this is there, please guide as to how i can do it.

I admit i am not very tech savy to explore the features in the website.

Keep up the good work.

srinivas

Srinivas

when you post a comment , you can see a check box just below the box for subscribing to that post . Looks like you missed looking at it 🙂

Manish

Thank you for the input. Will do that next time.

Informative post as usual.

I have some endowment policies whcih have completed about 75-80% tenure. They were taken way back in 1990’s.

Would like to know the surrender value and process for surrendering the same.

Learned friends please help.

regards

Srinivas,

You may visit the LIC branch where your policy documents exists (means servicing branch for your policy) with original Bond. If those policies completed 75 to 80% of tenure then defenitely you can go ahead for surrender.

They will calculate the surrender value based on SA, Bonus accured on that policy and tenure of the premium paid.

Thank you for the prompt response.

I need input on one thing. If there is no specific requirement, will it be good to continue or will it be good to terminate and transfer to any MF.

srinivas

Srinivas

Its there is no specific requirement , the main requirement is “Wealth creation” . So in anycase it would make sense to invest in Mutual funds

manish

Hi, In some ULIPs, Maximum NAV will be calculated at the end of the policy. Is it possible? will it give any good return..

T.S Ashok

Obviously , untill you reach to the end ,how do you know the “Maximum NAV”, what if it was maximum in the end week only 🙂 .

You can read more about highest NAV at : https://www.jagoinvestor.com/2010/03/how-do-highest-nav-guarantee-plans-work.html

Manish

Hi Manish,

For a LIC policy, I understand that bonus is a good amount, that gets accrued over the period of years. But I am not able to find any details on bonus accrual. Is it a % of investments? Will all policy (endowment/money-back) have a bonus at maturity? Can you throw any light on this?

Thanks.

Pearlie

It will be declared by LIC on the experioence they have with teh plan .. So no idea at this moment .

Hi Peralie,

Visit “http://www.licindia.com/index.htm”. Click on Login Image on the top right side. On the next page click Customer login. Register as new user. Add your policies to this login account.

It will take 2 weeks for the policy data to be updated. Once done you can see the accrued Bonus.

I have taken LIC Endowment policy in 2001 and I am able to see accrued bonus.

Hope this helps.

Regards

Atul

Hi Pearlie,

Bonus is calculated according to the LIC’s profit for that particular year. It is not calculated on the premium paid, but it is calculated on SA. That also they will calcualte for SA of Rs.1000/-. For example, if they declare that for particular Plan Bonus is Rs.50/- Per Rs. 1,000/- SA. Then if you have policy of 10,00,000 SA, you will get the Bonus of Rs.50,000/- (10,00,000*50/1,000). You will get all accrued Bonus with Loyality addition and Final Additional Bonus if any (which they will declare at the closure of policy) when it matures or for early claim else while the time of surrender.

All traditional profit sharing plans have bonus either it endowment or money back.

Bonus looks good when they declare every year and it shows accumulated, but that accumulated amount will not earn any return till your policy maturity. So, finally when you calculate your returns against what you invested in such policies will hover around 5% to 6%.

Hi,

I have a question regarding XIRR. Can we use this to calculate rate of return in case of chit funds where you pay monthly for 25 months. When I used the excel to calculate XIRR I got 0.080836805540432 which doesn’t look right.

Thanks

Shashi

why not 🙂

XIRR is cruel tool , which speaks truth only 🙂

Shashi,

As far as I know there are two types of chitfunds, one which decides the monthly payment based on the auction held for that month and the other which is fixed variable amounts for every month for the entire tenure. You need to calculate the agent commission as well in this. Sometime back I’ve calculated the returns from these chitfunds and it is never more than 8% (incl commission payment) in both scenarios. This is applicable for shriram, margadarshi chitfunds or any private individual organized chits. Never go for chits.

Jayaprakash

Also I would recommend “Friends circle” Chit Funds or popularly called as BC in smaller cities . One should look at them as more of unsecured , easy to get Loan … and not as investment product .

Manish

You need to multiply it with 100 to get percent.

If you try with PPF, you would get 0.08 as the answer.

But other things we are not multiplying with 100 right as per Video or examples in excel?

Anand/Shashi

Just make sure the format of the cell is “percentage” .

Manish

Manish/Anand,

Thanks, it worked.

Shashi

Manish,

Computing IRR the way you have done for endowment (or any other insurance/ULIP) policies is incorrect and misleading . You also have to include the ‘cost of insurance itself’ because you are getting coverage as well.

Taking the example provided by you –

7lakhs insurance, 35k pa – you compute the IRR as 4.89%.

Now a term insurance for 7lakhs (with accident rider) would cost you around 2.5% every year (give or take a couple of basis points), i.e around Rs 1800.

So your effective return on the endowment policy is approximately 4.89+2.5 = a respectable 7.4% . (Not great and not very advisable, but certainly not as bad as 4.89%).

The accurate way to do this would be to reduce your cash outflows (in the table) by Rs 1800 every year. (i.e instead of 35k, put 33.2k). And then compute IRR.

lpm

you can say its a bit incorrect , but not at all misleading .

Let me tell you why ? Term insurance for 7 lacs should not be Rs 1800 . The example taken was for a 30 yrs old male, for a person of that age you are getting term cover of 1 crore at Rs 10,000 per year . so Term insurance for Rs 7 lacs should be around Rs 1000 .

Now which means that 1,000 goes towards your insurance and rest 34,000 for investments .

Now if you assume 34k payments instead of 35k , calculate the IRR now ?

Its around 5.1% only . Not 7.4% or even close to it .

Thanks for putting up this point .

Manish

LIC policies give less return but do you know we can get loan on them with only 9% as interest.

Anitha

thi

Yes i know that , give an example showing how much and does it make it attractive after we consider this loan facility ? why ?

Manish

Manish,

The loan amount depends on the amount we have paid as premium. LIC policyholders can register the policy details at their website and it will show the amount we have paid, bonus accrued if any, and the loan amount we can get.

Ofcourse it will be a small amount for some people. But when we raise money for buying home,car or for marriage this amount with 9% as interest will come in handy. For getting the loan we have to submit our policy in the local lic office where we got it and it seems we will get the loan with minimum hassles in a day or two.

My point is those of us who have lic policy for many years need not despair. Atleast we can make use of this loan facility.

Anitha

Great, Its a nice point . LIC policy give this additional benefit 🙂 at no cost 🙂 .

Manish

Dear Manish,

I got huge shock after seeing this.. oh god.. i have money back policy and endowment policies which i took 6 years back. Shall i close all these..Anyway i have amulya jeevan from LIC also. I am thinking of closing the endowment and moneyback and putting those money in good mutual funds..

please suggest..

Regards,

T.S.Ashok, coimbatore..

T.S Ashok

I dont want you to get shocks , may be its immediate effect , but kindly get into action soon 🙂 . U should take decisions of whether you want to lesser your returns or increase it ?

Manish

Manish,

Please tell me in black and white. Shall i Surrender the money back & Pension(TATA AIG Maha life) policies or not..

I need your answer. Somebody are telling as paid up policy.. what does it mean.. should i pay all the premium in a single shot?

Please answer..

bhaiya ,

answer karo..

Making it paid up means stop paying the premiums , but dont close the policy , let it run .

However if you dont understand the policy , better close it and take money back , Look at taxation issues .

Ask on our forum : https://www.jagoinvestor.com/forum/

Hi Manish,

Can we use IRR for calculating the real intrest rate at which we pay home loan ? Because of floating rates, the interest rates keep changing every quarter (some time even month) that too EMI based. So how do we find IRR for home loans ?

Hi Jayant,

IRR is method to calculate interest rate when we have the cash flows of different amount like irregular amount of payout or inflow for every year. If the Payout or inflow of cash is constant through the period every year then with simple CAGR we can calculate the return. In your case as you told interest rate is floating and your payout towards your loan flactuate every month. So we can calculate the IRR as Manish showed in case of Moneyback Plans.

Is it right Manish?

Jayant

IRR is the tool which tells you the final IRR or returns you made or paid to someone . In your case , what will be the future values , you are not sure about it . You can approximate it , but finally you will come to know only when you have the numbers .

Manish

Hi Manish,

Once again nice post. But I have some suggestion while calculating IRR. In financial calculator while calculating cash flows to find out IRR we used to follow one rule. If the cash flow is begining of the year then it should be taken into same year and if the cash flow is at the end of year then we have to roll to next year. In IRR sheet which you showed if you counted the years from o to 19 years premium payment and received of final returns mentioning as 20th year then it may showed some clarity. Because when you entering the final returns you entered after ending the row of 20th year (Year column is blank for the final returns). It may confuse few people. Insurance premiums are allways at the begining of period of risk taken. We are not paying the premiums after completion of one year, it is allways at the beging of policy year. So if you made changes in this then viewers may get clear view.

It is just suggestion from me. What is your say??

Basavaraj

Yes , it makes sense … Can you explain in detail . Also how much difference will it make ?

Manish

Manish,

What you did is correct starting from the year 1 and ended premium payment at 20. After that you taken the return in next row where the year column is blank. For which I told that if you start with 0 and premium payment lasts at 19 and exactly at 20th year end if you showed them return it makes clarity. It is just suggestion. Their is allways some difference in values when you consider the payment in begin mode or end mode.

Basavaraj

Ok , thanks 🙂 .. Will do it next time now 🙂

Manish,

Sure…

Hi Manish, thanks for a nice article & video. Regarding the question – I assume in both option, the amount with Ins. co. is 10L Rs. at the maturity.

Option A Translates into a simple interest of 25K Rs. on the 6L Rs. that Insurance co. is keeping with itself to provide regular pension. Which translates into a simple interest rate of just 4.17%.

On the other hand, the same 6L Rs. Invested in a simple product like POMIS ‘ll give a far superior return of 8% yly & at the end of 6Y term a 5% bonus.

As per current Income Tax rules, Pension as well as POMIS income both are taxable hence both ‘ll be impacted same as per the applicable Tax slab of Ajay.

Hence in my view, accepting 10L Rs. lump sum in option B is better.

Thanks

Ashal

Ashal

The maturity is not 10 lacs in option 1 . anyways your answer is correct , option B is right

Manish

as per your video tutorial irr is 6.34% for option A & 8.77% for option B is it correct?

Mukesh

Yes .. its correct . your answer is right 🙂

Hi Manish

Nice and easy way to calculate IRR in excel. I had tough time in explaining my friend that Lic jeevan tarang will give real negative returns after accounting inflation. Had this tool known to me at that time it would been of great use. Thanks for sharing.

Regarding quiz – Option B is good i guess because option A gives 6% and option B gives 8.77%.

Regards

Jagadees

Jagadees

Did you know that IRR was mainly a tool in Excel only . anyways your answers are correct . thanks 🙂

Dear Manish

IRR for Option A is 5.1% and Option B is 9.78%. So, Option B is better than Option A.

I agree with Ritesh for the return calculation for maturity amount.

Best Wishes

Ramachandran.

Ramchandran .. Your answer is correct , but IRR numbers differ a bit from mine . What was the payment in 6th year total ?

Manish

Nice post! Unfortunately I don’t have any endowment plans anymore to use the tool. Raag Vam Datt has a paid ULIP evaluation service. Wonder if this would affect sales for him!

reg. last post on bima policy and its IRR analysis. I think same should be true of most insurance plans LIC or private.

Hey Pattu,

I am manish intend to celebrate “No more Endowment Week” so that each reader of jagoinvestor like you can proudly say I don’t have any endowment policy in my financial life…..

cheers

nandsih

Pattu

Good to hear that 🙂 .

Hi Manish,

Great article,

as per my calculation option A gives 6% and option B gives 9% return, So option B is better than A.

Another question Can You explain the difference between CAGR & IRR, and where to use them.

Thanks

Sadasiv

Sadasiv

Yes your answer is right , CAGR is annual return , IRR is just a way to calculate CAGR

Manish

Dear Manish,

I have been reading all your articles for last 4 months and it has been great experience for me. this is a very good article.

how much IRR should be good enough to buy any product?

and i want to buy TATA-AIG life MAHA LIFE GOLD for safe and life time income after 15 years. I have good equity portfolio in mutual fund so watching this product as safe and secure. is there any alternative of this product? should i go for it or not?

Viral

You should look out for 7-8% IRR for a completaly safe product and 11-12% IRR for equity related products and which are long term products

Manish

Hi Manish,

and what about TATA AIG MAHA LIFE GOLD?

should i buy this as i am getting life time income + life time insurance up to age 100, so basically it is a whole life plan.

If you consider this product for product review i will be more happy as your language is so easy and without too much of jargons that one can easily understand.

thanks.

Its a whole life plan , dont go for it

Manish

Can anyone of you please tell me as to how irr is calculated for option A ? i have got the irr for option B as 9%. Please reply

A really good blog article.

I can relate from my personal experience. Whenever I calculate the Rate of return for somebody’s policy (like pension plan or something) they refuse to believe me. They always tell me that rate of return I calculated is wrong. I call this “Confusion by numbers”. Thats how the agents sell plans. By throwing big numbers around.

One more thing, I was surprised to see that you covered only IRR and not XIRR. XIRR helps you compute rate of return for any arbitrary cash flow. Eg. If the ulip had top ups thrice a year, then you would need XIRR to calculate the exact rate of return.

Hi Manav,

People refuse to believe because they can see their mistake in front of their eyes…..But deep down they always know as the calculations are in front of them

Yes if the ULIP Top ups are made thrice a year for an accurate rate of return you need to use XIRR

Nandish

Manav

I will write about XIRR soon . Regarding your examples , what reason do u ask them for not beleiving your numbers or methods ?

Manish

hi manish

in the quiz for ajay the

1) best option will be to take option B) (of 10lacs) for it gives higher IRR then option A) (of 4 lacs and 25,000/- pension) and

2) if the money is invested at 8%

for option B) the money invested will give 80,000/- interest but for option A) it will be 57,000/- (25,000/- pension and interest on 4 lacs i.e. 32,000/-)

also in the end he will have 1o lacs with him in option B) 6 lacs higher then option A).

Ritesh

Thanks for your answer 🙂 .You are correct . Share your IRR numbers

Manish

Hi Manish,

Very nice video – IRR is a great tool that tells you what actually is happening with your money. Best way to compare 2 different investments.

Hemant

Yea thanks 🙂 . Will post a video on XIRR now .

Hi Manish,

Few of the excel sheets don’t show XIRR as a formula – in that case you have to add it.(also cover that part in video)

Your blog is also becoming a big resource for advisors 🙂

Hemant

Which tool ? In Microsoft Excel , there is XIRR tool . Just type =XIRR and choose the range . It should work

Manish

Na this is only possible in new excel sheet(I think from MS Office 2007)

In earlier version there is a process which you have to use to activate XIRR.

hmm.. may be , i didnt knew that

hi Manish ,

It was gr8 watching the video but there is one query that in all the examples you have given the maturity amount which is very difficult i suppose since the bonus will keep varying all the time.

Ritesh

Yes .. the best way would be to assume the BEST and WORST case and see what range IRR comes into , so you can get IRR of 7.7% with the best Bonus they can give and IRR of 4.5% if their is worst BONUS , in any case it helps you make a decision .

Manish

hi Manish ,

Thanks for the reply I never assumed it taking best and worst scenario.

Yes it will help a lot in making the decision.

keep up the good work !

ok great . Keep reading 🙂

Looks option A is better than B

pramod

what is the irr numbers u r getting ? Because most of other readers think its option B

MANISH