ElyxAI

Policy renewal calendar in Excel: which renewal date is due

Insurance AgentPolicy Renewal Calendar

A policy renewal calendar is one row per insurance contract you already place. You type the policy, the client, the premium, the renewal date, and the notice lead time in days for that contract. The notice date is the renewal date minus those days. Days left are the renewal date minus today while the policy is still active or in notice. A window says due when the days left are within the notice lead time you typed, and later when the renewal is still further out. The first screen is a sheet named Policies. Row 1 is the header. Row 2 is one policy you can check by hand: a policy number, a client, a premium, a renewal 20 days after today, and notice days of 30. The notice date is 10 days before today. Days left are 20. The window is due, because 20 is within 30. The premium is on the row and no formula reads it. This sheet is not a property manager lease calendar, a real-estate agent's sales list, a viewing diary, or a commission calculator. A lease tracks a unit you manage. An agent's pipeline tracks mandates that may never become a sale. Here the insurance contract already exists. The question is which renewal date is inside the notice window you typed for that row. The notice lead time is a number you type on the row, because it is not the same for every policy. The sheet does not invent a legal period. If the contract says 90 days, you type 90. If another says 30, you type 30. Changing the number moves the notice date. You still have to read the contract. Ended and renewed rows drop out of the window. They stay on the sheet. Elyx can write the notice date, the days left, and the window. You still type the renewal date and the notice days. The trial lasts 7 days, with no card.

The Problem

The renewal list fails when it lives in a diary. A note says a policy renews in the spring. Nobody can sort by renewal date, and nobody can see which row is already inside its own notice window. A sentence without a date is not a calendar. A second failure is mixing policies you place with leases or with properties you are trying to sell. A lease end date answers a property manager's question. A mandate and a viewing answer an agent's sales question. If you add unit numbers and offer amounts here, the morning filter no longer means which insurance renewal is due. Keep this sheet to policies, renewal dates, and the notice days you typed. A third failure is deleting a policy when it renews. Set the status to renewed or ended and leave the row. The window goes blank. Deleting the row removes the only record of the renewal date you already passed.

Benefits

Each policy is one row with a renewal date and a notice lead time you typed.

The notice date is the renewal date minus those days.

Days left are the renewal date minus today while the policy is still live.

Due means days left are within the notice days on that row.

Ended and renewed rows stay on the sheet and drop out of the due count.

Step-by-Step Tutorial

1

Create the list and the header

Name the sheet Policies. In row 1 type: Policy, Client, Premium, Renewal, Notice days, Notice date, Status, Days left, Window, Note. Policy is the contract number. Client is a name. Premium is the amount you typed. Renewal is a real date. Notice days is a whole number you typed for that contract. Notice date, Days left, and Window are formulas. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells. Premium is visible on the first screen and no formula reads it.

One row per current policy. A renewal that replaces the date should update Renewal, or become a new row if you need both dates. Do not keep two active rows for one policy.

2

Lock the status words

Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is active, notice, renewed, and ended. Active means the policy is in force and you have not started the renewal notice. Notice means you have sent or received notice and the renewal date still stands. Renewed means a new term is agreed: put that new date in Renewal or start a new row, and set this row to renewed if it is the old term. Ended means the policy is no longer on this term. Only active and notice stay in the due window. People must pick a word. A synonym such as finished or gone will not match.

Notice days are not a status. They are a number. Do not type 90 in the status column.

3

Enter one policy you can check by hand

On row 2 type a policy, a client, a premium, a renewal date 20 days after today, and notice days of 30. Set status to active. You will predict the notice date as 10 days before today, days left as 20, and the window as due, because 20 is within 30. On row 3 use a renewal 100 days out and notice days of 30. That window should be later. Use dates you can count on a calendar before you trust the formulas.

If you do not know the notice lead time, leave Notice days blank. The notice date and the window stay blank. Do not invent a number to make the cell look finished.

4

Write the notice date

Notice date is blank when the renewal date or the notice days are blank. Otherwise it is the renewal date minus the notice days. With Renewal in D2 and Notice days in E2, use =IF(OR(D2="",E2=""),"",D2-E2). Format the column as a date. If it shows a large plain number, the format is General: change it to a date. Excel stores dates as day counts, so subtracting 30 from a date gives the date 30 days earlier. That is the notice date for the number you typed. It is not a legal opinion and it does not read the premium.

=IF(OR(D2="",E2=""),"",D2-E2)

A notice lead time of 0 means the notice date equals the renewal date. Type 0 only if that is what the contract says.

5

Write days left and the window

Days left are blank when status is ended or renewed, or when the renewal date is blank. Otherwise they are the renewal date minus today. With Status in G2, use =IF(OR(G2="ended",G2="renewed",D2=""),"",D2-TODAY()). Format as a number, not a date. A negative number means the renewal date is already past and the row is still active or in notice. The window uses the same test against the notice days: =IF(OR(G2="ended",G2="renewed",D2="",E2=""),"",IF(D2-TODAY()<=E2,"due","later")). Due means the remaining days are less than or equal to the notice days on that row. Later means the renewal is still further out than the notice lead time.

=IF(OR(G2="ended",G2="renewed",D2="",E2=""),"",IF(D2-TODAY()<=E2,"due","later"))

TODAY moves each morning. A row that was later can become due without anyone editing the renewal date.

6

Count the rows that are due

On a sheet named Morning, the due rows are =COUNTIFS(Policies!I:I,"due"). Due rows for the client named in A2 are =COUNTIFS(Policies!B:B,A2,Policies!I:I,"due"). Ended and renewed rows are out because their window is blank. A blank window is not due. The header says Window, not due. Write the rule next to the count. Sort the policy sheet by Renewal, oldest first, when you want the nearest dates at the top. The count does not require that sort. The premium is not in the count.

=COUNTIFS(Policies!I:I,"due")

Do not count visible rows by hand after a filter and type over the formula.

7

Read a past renewal as a signal, not as a deletion

If Days left is negative and status is still active, the renewal date has passed and the row was not marked ended or renewed. That is a row to open, not a row to delete. Either the date is wrong, or the status was not updated. Fix the cell that is wrong. A negative days-left on a row marked ended should not happen, because ended blanks the days-left cell. If you see a number on an ended row, the formula was overwritten.

Filter Window to due for the call list. Filter Status to ended when you need history.

8

Check three rows before you share the count

Renewal 20 days from today and notice days 30, status active: days left 20, window due, notice date 10 days before today. Renewal 100 days out and notice days 30, status active: window later, because 100 is not within 30. The same 20-day renewal with status ended: days left blank, window blank, and the due count ignores the row. If a check fails, fix the date format or the status word before you send the count. The count is only this sheet. It does not say whether a notice was legally served, and it does not price the premium.

Write the three checks on Morning if more than one person updates policies.

Template Features

Notice date from the days you typed

Renewal date minus notice days. Blank if either input is blank.

=IF(OR(D2="",E2=""),"",D2-E2)

Days left while the policy is live

Renewal date minus today. Blank when status is ended or renewed.

=IF(OR(G2="ended",G2="renewed",D2=""),"",D2-TODAY())

Window against that row's notice

Due when days remaining are within the notice days. Later otherwise. Blank when closed.

=IF(OR(G2="ended",G2="renewed",D2="",E2=""),"",IF(D2-TODAY()<=E2,"due","later"))

Due count by client

COUNTIFS reads the client and the window word. Premium is not an argument.

=COUNTIFS(Policies!B:B,A2,Policies!I:I,"due")

History stays on the sheet

Ended and renewed rows remain. They leave the due count because the window is blank.

Concrete Examples

Inside the notice window

You want to know if a policy renewing in 20 days is already inside a 30-day notice you typed.

Renewal: 20 days after today. Notice days: 30. Status: active. Premium typed and ignored.

Result: Notice date is 10 days before today. Days left are 20. Window is due. The due count includes the row. The 30 is the number you typed, not a default.

Still later than the notice

Another policy renews in 100 days. Notice days on that contract are 30. You do not want it on this morning's list.

Renewal: 100 days after today. Notice days: 30. Status: active.

Result: Days left are 100. Window is later, because 100 is greater than 30. The due count ignores the row. Change notice days to 120 and the same date becomes due, because 100 is within 120.

An ended policy leaves the list

The term finished. You want it out of the due count and still visible.

Status changed from active to ended. Renewal date unchanged.

Result: Days left become blank. Window becomes blank. The due count drops the row. The renewal date is still on the sheet.

Pro Tips

One notice number per policy

Do not put a single notice period in a corner cell and point every row at it, unless every contract truly shares it. A column you type per row survives the policy that is different. If they do share one number, you can point E2 at that cell, and you should label the cell so a later edit is obvious.

Premium is not the window

The premium can live in its column. No formula reads it. The morning question is the renewal date. Mixing a premium total into the window formula makes the due count depend on money instead of dates.

Text dates do not subtract

If Renewal was pasted as text, D2-E2 will not be a date and D2-TODAY() will not be a day count. Re-enter the date or convert it. Then read the window again.

Distinct from leases and from property sales

Lease end dates belong on the property manager calendar. Mandates, viewings, and commissions belong on the real-estate agent lists. Do not paste a unit or a buyer into Policy.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Policy renewal calendar, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions