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%
Posted by at

5 comments:

Anonymous said...

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.

November 22, 2009 12:39 PM
Anonymous said...

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

November 22, 2009 4:56 PM
Tan Kin Lian said...

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

November 23, 2009 8:44 AM
Anonymous said...

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.

November 23, 2009 1:06 PM
Anonymous said...

Mr Tan,

What is considered acceptable returns?

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

November 26, 2009 8:24 PM

Post a Comment

Subscribe to: Post Comments (Atom)

Blog Archive