Blog · Board and management reporting · Insurance
Build a life insurance operating review from premium transactions, policy cohorts, and issued-business exports. Compare agents and products with explicit denominators, spreadsheet formulas, and reconciliation checks.
A life insurance dashboard compares premium growth, policy persistency, and new-business mix by agent and product. Start with separate exports for money, cohort status, and issued sales, then connect their summaries using consistent agent and product definitions. That gives an agency manager an operating review without confusing premium volume with agency income.
Use net premium collections for the money measure in this example: receipts less refunds, by posting month. Compare September 2026 with September 2025. Premium is the policyholder's payment for insurance, which may follow different payment schedules, as the New York Department of Financial Services glossary explains. It is separate from commissions and fees earned by an agency.
For persistency, use an explicitly defined first-anniversary policy-count measure. Take policies issued in September 2025 and record whether each remained in force at its own first anniversary. Every anniversary in this cohort has occurred by September 30, 2026. This example retains all issued policies in the denominator and records every exit reason separately; another approved definition may treat certain exits differently.
For sales, count policies issued during September 2026 and their annualized scheduled premiums. Restrict this example to recurring-premium term and whole-life business. Do not annualize a single premium by multiplying it, or assume a flexible-premium product has the same payment pattern. The Texas Department of Insurance describes the flexibility of universal-life premiums, which is a reason to keep that business separately defined.
Use a premium transaction file with unique transaction ID, policy key, original writing agent, product, posting date, currency, and signed amount. Retain refunds as negative amounts. Resolve duplicates before summing anything.
Prepare a second file with one row per policy in the anniversary cohort: policy key, original agent, product, anniversary date, eligible indicator, retained indicator, and exit reason. A retained indicator can only be set when the eligible indicator is set. Preserve the anniversary observation rather than replacing it with today's status.
Keep issued-business records separate, with a unique policy key, issue date, original agent, product, and annualized scheduled premium. Aggregate each source by agent and product before combining summaries. Joining every receipt to every status-history row would repeat receipts. Keep policyholder names and unnecessary personal details out of the review file.
Agree the period basis using effective date versus posting date, and record the coverage of each export using the insurance dashboard freshness checks.
The following invented US agency records are already aggregated at the agreed grain. The premium columns are September collections; the cohort columns describe the earlier issue cohort. New sales describe September 2026, so they are not additions to the cohort denominator.
Put these columns in A through H, with the four detail rows in spreadsheet rows 2 through 5.
| Agent | Product | Prior collections (USD) | Current collections (USD) | Eligible policies | Retained policies | New policies | New annualized premium (USD) |
|---|---|---|---|---|---|---|---|
| A | Term | 120,000 | 132,000 | 100 | 90 | 20 | 24,000 |
| A | Whole life | 80,000 | 88,000 | 40 | 38 | 5 | 20,000 |
| B | Term | 100,000 | 110,000 | 80 | 72 | 15 | 18,000 |
| B | Whole life | 60,000 | 72,000 | 30 | 27 | 6 | 24,000 |
| Total | 360,000 | 402,000 | 250 | 227 | 46 | 86,000 |
In I2 calculate collection growth; in J2 calculate persistency. Fill down through row 5 and format the results as percentages.
=IF(C2=0,NA(),D2/C2-1)
=IF(E2=0,NA(),F2/E2)
The total calculations use the underlying amounts and counts:
=SUM(D2:D5)/SUM(C2:C5)-1
=SUM(F2:F5)/SUM(E2:E5)
Collections grew by (402,000 - 360,000) / 360,000 = 11.7%. Persistency is 227 / 250 = 90.8%. Percentages here are rounded to one decimal place after calculation.
Agent A's persistency is (90 + 38) / (100 + 40) = 91.4%; agent B's is (72 + 27) / (80 + 30) = 90.0%. These comparisons point to a question about cohort composition and exits. They do not establish that an agent caused the difference.
Combine the product rows from the table above. Calculate the share of issued policies separately from the share of new annualized premium.
| Product | New policies | Policy-count share | New annualized premium (USD) | Premium share |
|---|---|---|---|---|
| Term | 35 | 76.1% | 42,000 | 48.8% |
| Whole life | 11 | 23.9% | 44,000 | 51.2% |
| Total | 46 | 100.0% | 86,000 | 100.0% |
For term policies, the formulas referring to the original rollup are:
=SUMIF(B2:B5,"Term",G2:G5)/SUM(G2:G5)
=SUMIF(B2:B5,"Term",H2:H5)/SUM(H2:H5)
Term dominates the issued count, while whole life contributes the larger annualized premium share. Neither view identifies agency profitability. Use the monthly agency finance pack for commission and fee performance, and life insurance ratio analysis for the separate insurer-finance question.
The collections movement must reconcile: $360,000 + $42,000 = $402,000. Tie both endpoints to the signed premium export on the stated posting basis. Any excluded currency or transaction type needs its own reconciliation line.
The cohort must reconcile independently: 250 - 227 = 23 exited, so 227 retained + 23 exited = 250 eligible. Classify those exits before calling them lapses; an exit can have another cause. The issued-business source must also return 46 unique policies and $86,000 of annualized premium.
Have operations approve the cohort status treatment and finance approve the collections control. The manager can then investigate a product's exits or an agent's sales mix using an agreed measure, rather than trying to repair definitions during the meeting.
Bring your prepared premium, cohort, and issued-business summaries as a file or Google Sheet; Covirage maps the columns, and its tools compute and check the comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for insurance agencies and brokers for the supporting industry context.
No. Premium volume measures business placed or collected under the stated definition. Agency revenue comes from its commission and fee records. Keep separate totals and reconcile each to the appropriate source rather than treating policyholder premiums as brokerage income.
Use the defined issue cohort whose observation milestone has been reached. State how deaths, maturities, replacements, and reinstatements are treated. The numerator must contain only policies from that denominator, with status measured at the same milestone.
No. Policy-count mix gives each issued policy equal weight. Premium mix weights business by its defined premium measure. A product can represent a small share of issued policies but a large share of annualized premium, so label both views.