Before you start

Persistency Rating Calculator

See your persistency rating this month, where it is heading over the next four, which policies are pulling it down, and what it takes to reach your target. Every figure is checked against the agency’s PR calculator workbook.

Pick the month you are calculating

Calculating for

Usually this month. The download dates below follow it.

Download these two reports for the rating

Then, in the calculator

Load both reports. Drop them in together; each is recognised on its own. Policies on the lapse report get their paid-to date automatically.
Mark any policy that is not in force. Lapsed, surrendered, in the grace period or a death claim — with its paid-to date. Everything else is treated as paid up.
Read your rating. This month’s persistency, the four-month forecast, the policies dragging it down, and the credits needed to reach your target.

I have downloaded both reports for

Report dates

For the rating

Back to the instructions

Step 1

Load your reports

Drop your Policies Issued Report and Anticipated Lapse Report here One at a time or both together, Excel or CSV. Each is recognised automatically. The files stay on your device — nothing is uploaded anywhere.

Step 2

Mark any policy that is not in force

The paid-to date decides which months still earn credit. Policies on the lapse report have it filled in already. Every other policy starts as in force and paid up to date. For one that has lapsed, been surrendered, or is behind on premiums, change its status and enter its paid-to date.

Policy	Issued	API	Status	Paid to last payment	Lapse or surrender	Actions

Status tags are remembered in this browser, so next month’s report picks them up by policy number.

Step 3

Where you stand

Study period ·

Step 4

What is pulling it down

Detractors:

Step 5

Catch-up plan

Pick the month you are working towards

If you recover this much

Earned-premium credits you expect to bring back

Persistency would be —

Reinstating a lapsed policy lifts the rating further than new business does: it adds credits to the top of the ratio without adding to the bottom.

How the rating is worked out

The study period is the twelve months ending two months before the calculation month — calculating for July 2026 studies June 2025 to May 2026. The two-month lag is the grace period: a premium is not counted late until it has been unpaid that long.

A policy earns credit for each month of the study period that falls in policy durations 2 through 13 — the second year of the first policy year, in other words. Each such month is worth APE ÷ 12. The denominator counts every month the policy was expected to pay; the numerator counts only the months actually covered by premiums received, stopping at the paid-to date or the month before surrender, whichever is earlier. Persistency is numerator ÷ denominator, truncated to two decimals — 84.9% shows as 84%, never 85%.

Every date counts by its month. Any day in April is April: a paid-to date of 1 April and one of 30 April give the same result, and so do issue, lapse and surrender dates. The day is kept only so you can see the date you entered.

The paid-to date is the date premiums are paid up to — the same figure as the Paid To Date on the lapse report and the paid-to month in the original workbook. A policy on the lapse report takes its paid-to date from there; a paid-to date you type on a row takes precedence, for when a payment came in after the report was run. A policy on your PIR that is not on the lapse report, or that you leave in force, is taken as having paid every premium due up to and including the calculation month.

A policy on the lapse report but missing from the PIR is added when it was issued recently enough to count — for a September 2026 rating, from 1 August 2024, the same rule as the original workbook’s instructions. Marking a lapse-report policy in force overrides the report entirely.

The annualised premium is the report’s API: modal premium × 12 for monthly, × 4 quarterly, × 2 semi-annual, × 1 annual, and 10% of a single premium. A surrender stops credit from the surrender month on. The lapse date is kept for your records; lapsing itself is already captured by the paid-to date.

A death claim sets the denominator equal to the numerator, so a claim never drags the rating down. A policy issued too recently to have reached duration 2 has a denominator of zero and does not count yet.

The four forecast months run the same calculation over a window shifted one month at a time. Months three and four ahead assume that a policy fully paid up today stays that way, and that anything sitting in the grace period today lapses.

These rules are transcribed from PR-Calculator-for-Agents_v1.xlsm and checked against it: 1,000 policies across four calculation dates were run through the workbook itself and matched value for value, alongside 60,000 randomised policies checked against a second independent implementation.