Sunday, November 22, 2009

Yield on a Life Insurance Policy

Microsoft Excel has a function to calculate the yield on a life assurance policy. You can get this function using Insert > Function > Financial > Rate.

Policy taken for 18 years
Monthly premium of $50
Surrender value at end of 18 years is $12,800
What is the yield?

You have to enter the following values in the RATE function:
NPER - number of periods: 18
PMT - payment: $50 X 12 = $600
PV - present value: 0
FV - future value: -12,700 (show as negative as the money is being received)
TYPE: payment type: 0.5 (for monthly payment)

This returns a value of 1.68%
at

5 comments:

  1. AnonymousNovember 22, 2009 12:39 PM

    I think it is more objective to say for calculation of anything that you purchase.

    Example: I bought XXX to keep for 10 years, I pay instalments for $500/month and I sold off at $150,000. What is the yield?


    Regards

    An Insurance agent.

    ReplyDelete
  2. AnonymousNovember 22, 2009 4:56 PM

    Mr. Tan, It is Insert, then Formula, then Financial than Rate.

    ReplyDelete
  3. Tan Kin LianNovember 23, 2009 8:44 AM

    Hi 4:56 PM
    I use Excel 2004, not Excel 2007

    ReplyDelete
  4. AnonymousNovember 23, 2009 1:06 PM

    Thanks Mr Tan, for showing your readers a simple way to calculate returns. There are also built-in formulas for Future value, Present value, principal and/or interest payments, mortgage etc. These are also available in Open Office spreadsheet (freely downloadable).

    Singapore's insurance Benefit Illustrations do not mandate spelling out the projected returns less costs/fees in a Big Bold Percentage Figure. This is bad as it allows agents to focus on the printed projected figures of 5.25%pa and 9%pa (for ILPs). For those not in the know, it ain't so -- TANSTAAFL.

    One of the things I used to do when I was an insurance consultant was to show customers (esp. the younger ones) how to use Excel / Open Office to compute the returns on their own. E.g. You can calculate the Expense Ratios from the difference between the projected returns to You (the customer) versus the plucked from the air 5.25% or 9% projected returns to the Par or Investment Funds.

    I got irritated whenever I heard colleagues / agents harping on the figures shown as being 5.25%pa returns for endowments / wholelife or 9%pa returns for ILPs.

    ReplyDelete
  5. AnonymousNovember 26, 2009 8:24 PM

    Mr Tan,

    What is considered acceptable returns?

    After calculation, my policy has a yield of 1.91%.

    ReplyDelete