How to find every term conversion in your book, in a spreadsheet

Q: Which fields does a book export need to find term conversions?

Six: a policy identifier, the issue date, the product type or name, the level term length in years, the face amount, and the insured's date of birth. Carrier and status help. If term length is missing it can usually be read out of the product name, since most carriers put it there.

Q: How do you calculate a term conversion deadline in a spreadsheet?

Compute up to three candidate dates and take the earliest: issue date plus the level term length, issue date plus the number of convertible years if the contract caps it, and the date the insured reaches the attained-age cap. Whichever comes first is the deadline, because the earliest limit binds.

Q: How far ahead should you work a conversion list?

Eighteen months is a reasonable window. It is long enough that underwriting, illustration and a client decision all fit before the option closes, and short enough that the list stays small enough to actually call.

Q: Can you do this without buying software?

Yes. Every step here is a sort, a filter and a date subtraction in whatever spreadsheet you already have. The reason most agencies have not done it is not the tooling.

How do you find term conversion opportunities in a book of business?

Export the book, keep six columns, and compute up to three candidate dates per term policy: issue date plus level term length, issue date plus any convertible-years cap, and the date the insured reaches the attained-age cap. The earliest of those is the deadline, because the earliest limit binds. Filter to deadlines inside the next eighteen months and sort by face amount. That is the list.

Nobody publishes this, so here it is. It is a sort, a filter and some date arithmetic — there is no trick in it, and the reason most agencies have not done it is not that the method is hard.

You need an export of your book first. Once you have a file, this takes an afternoon.

Step 1 — keep six columns

Delete everything else. A book export is usually eighty columns wide and it makes the job look harder than it is.

Column	Why
`policy_id`	So a row can be traced back.
`issue_date`	Every deadline is measured from it.
`product`	Identifies term, and usually carries the term length.
`term_years`	The level period. Often missing — see below.
`face_amount`	Sorts the list by what it is worth.
`dob`	Needed for the attained-age limit.

If term_years is missing, read it out of the product name. Most carriers put it there: "Term 20", "TL-20", "20 Year Level Term". Pull the first one- or two-digit number out of the product string and sanity-check the result against the values you would expect — 10, 15, 20, 25, 30.

Step 2 — keep only in-force term

Filter status to in-force. A lapsed policy has no option to exercise.
Filter product to rows containing TERM, TL, LT or the word Level. Case-insensitive, and check what survives — naming is inconsistent between carriers and this filter is where mistakes hide.
Drop rows with no issue_date. They cannot be dated, and they are worth a separate look later.

Step 3 — compute three candidate dates

This is the whole of it. Each row gets up to three dates, and the earliest one wins, because a conversion right ends at the first limit it meets.

End of level term — issue_date + term_years.
Convertible-years cap — issue_date + convertible_years, where the contract only allows conversion during the first few policy years. Frequently five or ten on a twenty-year product, which is why this one closes so much earlier than people assume.
Attained-age cap — the date the insured reaches the age named in the contract, often 65 or 70. That is dob + age_cap. Some contracts round back to the policy anniversary on or before it, which pulls the date earlier again.

Then deadline = MIN(date1, date2, date3), ignoring the ones you do not have. The deadline calculator does exactly this if you want to check your formula against a few rows.

The mistake worth avoiding. Using only the end of the level term. It is the date everyone reaches for and it is the binding one least often. A twenty-year policy written on a 52-year-old with an attained-age cap of 65 has thirteen years of conversion right, not twenty — and a convertible-years cap can cut it to five. Work from the level term alone and you will file a live opportunity as having seven years left when it has one.

Step 4 — build the call list

Add months_left = deadline − today.
Filter to 0 < months_left <= 18. Eighteen months fits underwriting, an illustration and a client decision, and keeps the list callable.
Sort by face_amount descending. Conversion economics scale with it.
Look at months_left <= 0 separately — those are already gone, and counting them is the most honest measure of what not doing this has cost.

What a spreadsheet cannot tell you

You now have a list. It is not yet a call list, and the gap is the part this page cannot close.

Convertibility is a contract feature, and the limits are per carrier, per product, per issue year. Some products are convertible for the full level term. Some stop at five policy years. Some stop at attained age 65, others 70, others at the earlier of several. Some riders block conversion outright. Some carriers allow conversion only into a named subset of their permanent portfolio.

None of that is published in one place. It comes from the contracts and from asking carriers, and it is what takes a list of four hundred matching rows down to the forty where the option is real. Working the list without it means calling clients about a right they may not have, which is worse than not calling.

So: run the four steps. They are genuinely the method and they will show you the shape of what is sitting in your book within an afternoon. Then, before anyone picks up a phone, confirm convertibility for each carrier and product against the contract.

If that second part is where it stalls — which is where it usually stalls — that is the part we do. But the method above is yours either way, and an agency that runs it and calls nobody is still better off than one that never looked.

Common questions

Which fields does a book export need to find term conversions?

Six: a policy identifier, the issue date, the product type or name, the level term length in years, the face amount, and the insured's date of birth. Carrier and status help. If term length is missing it can usually be read out of the product name, since most carriers put it there.

How do you calculate a term conversion deadline in a spreadsheet?

Compute up to three candidate dates and take the earliest: issue date plus the level term length, issue date plus the number of convertible years if the contract caps it, and the date the insured reaches the attained-age cap. Whichever comes first is the deadline, because the earliest limit binds.

How far ahead should you work a conversion list?

Eighteen months is a reasonable window. It is long enough that underwriting, illustration and a client decision all fit before the option closes, and short enough that the list stays small enough to actually call.

Can you do this without buying software?

Yes. Every step here is a sort, a filter and a date subtraction in whatever spreadsheet you already have. The reason most agencies have not done it is not the tooling.

Not advice. This page describes how these situations generally work. Policy terms, carrier rules and state regulations vary, and the governing document is always the contract. Confirm anything you act on with the carrier, and take compliance questions to your own counsel or compliance officer.

Why term conversion matters What the option is worth, and why it goes unworked. Conversion deadline calculator Check a single policy without building the sheet. How to export your book Getting the file out of AgencyBloc, Radius, Applied Epic, Redtail or a spreadsheet.

Finding every term conversion in your book