Wednesday, November 18, 2015

How to calculate the yield for a limited payment policy

Hi Mr. Tan
I know how to use the "Rate" function in Excel to calculate the yield for an insurance policy where the premium is payable throughout the term.
A popular policy that is sold today have premium payable for 5 years, but the maturity benefit is payable after 10 or 15 years.
How can the yield be calculated for this type of policy?

You have to use the IRR function in Excel. It allows you to indicate the cash flow for each year over a certain period. In the above example, you are paying a premium for 5 years, so this has to be entered in 5 rows. There is a gap of 10 years for which there is no premium payment, so you enter 0 for the next 10 rows. After 15 years (i.e the beginning of year 16), you have a cash payment back to you.

See this article for an explanation and example of how to use the IRR function.

No comments:

Blog Archive