Insurance Pricing Help: Actuary Model in Excel

One of the biggest sellers for a business is if they offer one-time insurance on a given product or service. The hard part is figuring out how much you should charge each customer. The following Excel actuary model does just that.

$45.00 USD

 

The template will be immediately available to download after purchase. Check out more financial models and templates.

First, let's look at the mechanics of this. Let's say you offer a given service and if there is a certain issue, then you will fully cover the cost to fix the issue only if the customer purchases an insurance package from you. Normally, these things will cover up to x amount of years (1-3 normally).

With that in mind, you now have to figure out how much to charge each person. The main drivers to pay attention to, and that makeup the main inputs of this Excel template, are the costs to satisfy the claim and the % chance that a claim happens in the given time-frame.

All of the math/mechanic are built into this financial model for you so once you enter the main drivers, it will spit out a number that tells you how much to charge each person.

There is a final piece that I have added to make this a bit more functional. The user will have a goal seek cell. This cell will allow an input of a desired return on expected costs/risk. Based on that number, the $ amount you have to charge for each product/service's insurance package will show.

A table was added so you can put in all kinds of % return values and see how much you would charge in those cases. This is going to run off the main assumption you enter in the building assumptions tab.

You may also be interested in this cohort analysis tool that gives you a framework to analyze historical customer spending and retention patterns.