W. A. Barrett
April 11, 2005
The insurance model found in this spreadsheet uses mortality statistics found in this spreadsheet. You can play with it to estimate the minimum premium that you need to pay for a term insurance policy on one life.
As configured, this model assumes that you take out a term policy with a $100,000 face value at age 20. You agree to make the annual premium payment ($219.48) starting at age 20, and continuing until your 60th birthday. The insurance company agrees to pay your survivor the face value at the end of any year that you die.
The idea is that in any given year, the probability of your death is quite small, and that the company can therefore collect its premium from its pool of customers holding the identical policy. The premium is adjusted so that at the end of the 60th year, the company has exactly 0 assets (column G), but also no policy liabilities. It so happens that the company’s assets never dip below 0, and thus the company is always in the black.
The investment earnings percentage represents the average earnings that the insurance company can expect to earn per year on its total assets. This is worked into the Earnings column H by multiplying the assets this year (G) by this percentage.
The yearly assets (column G) is the previous year’s asset plus the previous year’s earnings plus the premium collected this year (column E) minus the expected payout to the policyholder’s deaths (column F).
The premium collected this year is typically less than the policy’s premium in this model, because some of the polyholders have died. For any given year Y, this is equal to the face premium times the Survivors for year Y, divided by the Survivors for the year in which the policy was started (year 20).
The Payout for year Y is equal to the policy face value times the expected fraction of survivors for that year times the fraction of holders expected to die that year (Drate, column D).
To obtain the even-bet premium P for this policy, it’s necessary to adjust P (cell C7) in the spreadsheet so that the Assets at age 60 is exactly 0. This can be done in Excel by choosing Tools/Goal Seek, then filling in the cells appropriately.
An even-bet premium is such that the insurance company neither gains nor loses on its policy over the lifetime of the policy, assuming that its policyholders follow the US statistics for death rates. Don’t expect to buy a policy this cheaply – the company expects to earn a profit, pay its agents a commission, and protect itself against a bad collection of policyholders. Insurance companies are required to show adequate reserves against its policies by most states. Notice that the assets come to more than ten times the premium toward the middle of the policy’s life. It would be tempting for a dishonest insurance company executive to just abscond with the company reserves, leaving its policyholders high and dry. In fact, reputable companies have formed an insurance consortium by which its members agree to cover all claims from a company that happens to go bankrupt.
Different insurance strategies require some work on the spreadsheet. Notice that the row for age 20 has different formulas for ages 21 and up – you need to copy these properly for the system to work if you choose a different starting age.