22.7 Insurance
Agency and branch interviews ask persistency (how much of the book is still in force) and loss ratio (claims divided by premium). New policies sold, alone, is how a weak book hides.
| KPI | What the number means | Who uses it | Action it drives |
|---|---|---|---|
| Persistency | (Policies in force minus lapsed) divided by policies in force. | Agency manager | Lapses this month are a call list, not a footnote under "new business". |
| Loss ratio | Claims divided by premium earned. | Underwriting, branch head | A high ratio on a small book stops that city's new business until the claims are read. |
| New policies | Count sold this month. | Only next to persistency | A city can "grow" while the old book leaves. |
Fictional sample rows. One month. Premium and claims are fictional rupees, not a filed result. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblInsurance (Table Design › Table Name).
| City | In force | Lapsed | Premium | Claims | New policies |
|---|---|---|---|---|---|
| Pune | 200 | 10 | ₹4,00,000 | ₹2,40,000 | 40 |
| Nashik | 150 | 6 | ₹3,00,000 | ₹1,50,000 | 20 |
| Kolhapur | 100 | 20 | ₹1,60,000 | ₹1,40,000 | 50 |
| Nagpur | 180 | 9 | ₹3,60,000 | ₹1,80,000 | 25 |
| Solapur | 80 | 4 | ₹1,60,000 | ₹80,000 | 10 |
| Sambhaji Nagar | 120 | 24 | ₹1,80,000 | ₹1,60,000 | 45 |
Excel formulas for the whole file (type them in empty cells below or beside the table):
| KPI | Excel formula | Result | Number format |
|---|---|---|---|
| Persistency | =(SUM(tblInsurance[In force])-SUM(tblInsurance[Lapsed]))/SUM(tblInsurance[In force]) |
91.2% | 0.0% |
| Loss ratio | =SUM(tblInsurance[Claims])/SUM(tblInsurance[Premium]) |
60.9% | 0.0% |
Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.
| New column | Formula in the first row |
|---|---|
| Persistency | =([@[In force]]-[@Lapsed])/[@[In force]] |
| Loss ratio | =[@Claims]/[@Premium] |
Expected result per row:
| City | Persistency | Loss ratio |
|---|---|---|
| Pune | 95.0% | 60.0% |
| Nashik | 96.0% | 50.0% |
| Kolhapur | 80.0% | 87.5% |
| Nagpur | 95.0% | 50.0% |
| Solapur | 95.0% | 50.0% |
| Sambhaji Nagar | 80.0% | 88.9% |
One city with SUMIFS. Persistency for Pune only:
=(SUMIFS(tblInsurance[In force],tblInsurance[City],"Pune")-SUMIFS(tblInsurance[Lapsed],tblInsurance[City],"Pune"))/SUMIFS(tblInsurance[In force],tblInsurance[City],"Pune")
Result for Pune: 95.0%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.
| Good KPI | Vanity metric | |
|---|---|---|
| From the same rows | Kolhapur loss ratio 87.5% and persistency 80.0% | Kolhapur 50 new policies |
| Why | Claims are almost the whole premium, and one in five policies lapsed. | The new-business card is the best in the file. It is the wrong celebration. |
What the manager does. The Nashik agency manager keeps the book that lapsed only 6 of 150. The Pune book is acceptable in this file (10 lapsed of 200 = 5.0% lapse, which is persistency 95.0%; loss ratio 2,40,000 ÷ 4,00,000 = 60.0%). The Kolhapur manager stops the new-policy contest and calls the 20 lapses. Sambhaji Nagar is the same pattern: 45 new policies, persistency (120 − 24) ÷ 120 = 80.0%, loss ratio 1,60,000 ÷ 1,80,000 = 88.9%.
Ravindra Bagale's Tip
Persistency is (in force − lapsed) ÷ in force. Write the formula in words above the cell before you type it, and the brackets will be right.
Ravindra Bagale's Tip – मराठी
Persistency म्हणजे (in force − lapsed) ÷ in force. Type करण्याआधी cell च्या वर formula शब्दांत लिहा, म्हणजे brackets बरोबर येतील.
Ravindra Bagale's Tip – हिंदी
Persistency यानी (in force − lapsed) ÷ in force. Type करने से पहले cell के ऊपर formula शब्दों में लिखें, तो brackets सही आएँगे.