In the first part of this blog series, we helped you understand how to calculate the returns of an endowment insurance plan using Microsoft Excel or Google Sheets. You can refer to Part 1 here. Now, let’s continue to understand how to calculate the returns of a money-back insurance plan. Just like endowment plans, money-back plans are immensely popular in India. Hence, understanding how to evaluate them will help you make the right decision.
Money-Back Insurance Plan Returns Calculation
Understanding Money-Back Plans
A money-back plan provides periodic payouts during the policy term as a percentage of the sum assured. These payouts are in addition to the maturity amount received at the end of the term.
Key Components of a Money-Back Plan
- Premiums Paid: Regular payments made towards the policy.
- Survival Benefits: Periodic payouts during the policy term.
- Sum Assured: The guaranteed amount payable on maturity.
- Bonus: Additional amounts declared by the insurance company.
- Calculating Endowment Plan Returns Using Excel or Google Sheets
Suppose you pay an annual premium of Rs. 25,000 for 20 years, with survival benefits of Rs. 1,00,000 received every 5 years, the sum assured is Rs. 2,50,000, and the bonuses accrued are Rs. 1,25,000.
- Step 1: List the annual premium payments in cells A2 to A21 (each cell will have 25,000 in negative because these are the payments made by you).
- Step 2: Add the survival benefits in the respective years. For example, in cells B6, B11, B16, and B21, add Rs. 1,00,000 each.
- Step 3: In cells C2 to C21, enter the sum formula to add respective columns A and B so that you have the net value in this column. For example, C2 = Sum(A2, B2).
- Step 3: In cell C22, enter the maturity amount (sum assured + bonus = 3,75,000).
- Step 4: Use the IRR function to calculate the annualised returns:
- Formula:
IRR(C2:C22)
- Formula:
Years Payments Receipts Net Year 1 -25,000 -25,000 Year 2 -25,000 -25,000 Year 3 -25,000 -25,000 Year 4 -25,000 -25,000 Year 5 -25,000 1,00,000 75,000 Year 6 -25,000 -25,000 Year 7 -25,000 -25,000 Year 8 -25,000 -25,000 Year 9 -25,000 -25,000 Year 10 -25,000 1,00,000 75,000 Year 11 -25,000 -25,000 Year 12 -25,000 -25,000 Year 13 -25,000 -25,000 Year 14 -25,000 -25,000 Year 15 -25,000 1,00,000 75,000 Year 16 -25,000 -25,000 Year 17 -25,000 -25,000 Year 18 -25,000 -25,000 Year 19 -25,000 -25,000 Year 20 -25,000 1,00,000 75,000 Year 21 3,75,000 Annualised Returns (%) 7.71%
Determining the returns on money-back insurance plans involves understanding the plan’s components and performing basic arithmetic operations to calculate the total returns. For a more accurate measure of annualized returns, the Internal Rate of Return (IRR) method is used. It’s advisable to consult with a financial advisor to fully grasp the nuances and tailor the calculations to your specific policy details.
3 Comments
Leave A Comment Cancel reply
Join Our Mailing List
Once Weekly Webinar
Free Webinar Once Per Week
Our free webinar runs once per week and is available to anybody who wants to know more about getting started on the road to financial freedom.
For calculating returns on endowment plans, see the part 1 https://wealthwisher.in/understanding-insurance-cum-investment-plans-a-comprehensive-guide-to-calculating-returns-and-factoring-in-inflation-part-2/
Excellent explanation using simple words and method.
Very Nicely explained