A step-by-step guide to computing contract renewal rate in Excel from a contract export: contracts due in the period, the outcome per contract with a stated grace window, renewal rate by count and by prior value, contraction on renewal, the rate by renewal number, the upcoming renewal calendar, and the check that every due contract has exactly one outcome. Includes the exact formulas and the point at which the spreadsheet stops being enough.
Renewal rate is contracts renewed over contracts due. In Excel it is one lookup for the following contract and a set of COUNTIFS. This guide gives the formulas, the grace window and the check.
Sheet Contracts, one row per contract term:
| Column | Content |
|---|---|
| A | Customer |
| B | Product or service |
| C | Start date |
| D | End date |
| E | Annual value |
| F | Renewal number: 0 for a first term, 1 for its first renewal, and so on, if available |
On Calc: period start B1, period end B2, grace days B3, as-of date B4.
Contracts!G2:
=AND(D2>=Calc!$B$1,D2<=Calc!$B$2)
Next start date, H2: the earliest term for the same customer and product that starts after this one started, which also catches renewals signed early:
=MINIFS(C:C,A:A,A2,B:B,B2,C:C,">"&C2)
This returns zero if there is none. Next value, I2, Microsoft 365:
=IF(H2=0,0,SUMIFS(E:E,A:A,A2,B:B,B2,C:C,H2))
J2:
=IF(NOT(G2),"Not due",IF(H2=0,IF(D2+Calc!$B$3>Calc!$B$4,"Pending","Lost"),IF(H2<=D2+Calc!$B$3,IF(H2>D2+1,"Renewed late","Renewed"),"Lost then won back")))
Five states for due contracts: Renewed, Renewed late, Lost, Lost then won back, and Pending for contracts whose grace window has not yet closed at the as-of date.
Due and decided, by count:
=COUNTIFS(Contracts!G:G,TRUE)-COUNTIFS(Contracts!J:J,"Pending")
Renewed, by count:
=COUNTIFS(Contracts!J:J,"Renewed")+COUNTIFS(Contracts!J:J,"Renewed late")
Rate is the second over the first. By value, swap COUNTIFS for SUMIFS(Contracts!E:E, ...) with the same criteria. The value used is the old contract's, so the rate measures what was kept of what was due.
For renewed contracts, K2:
=IF(OR(J2="Renewed",J2="Renewed late"),I2/E2,"")
Average it weighted by value:
=SUMIFS(Contracts!I:I,Contracts!J:J,"Renewed*")/SUMIFS(Contracts!E:E,Contracts!J:J,"Renewed*")
The wildcard matches both renewed states. A renewal rate of 92 percent by value with renewals landing at 85 percent of prior value is a 78 percent value outcome.
=COUNTIFS(Contracts!F:F,0,Contracts!J:J,"Renewed*")/(COUNTIFS(Contracts!F:F,0,Contracts!G:G,TRUE)-COUNTIFS(Contracts!F:F,0,Contracts!J:J,"Pending"))
Repeat for 1, and for 2 and above with ">=2". First renewals are normally the weakest; compare them to last year's first renewals, not to the blend.
Microsoft 365, contracts ending in the next 180 days with no following contract, largest first:
=SORT(FILTER(Contracts!A2:E5000,(Contracts!D2:D5000>Calc!B4)*(Contracts!D2:D5000<=Calc!B4+180)*(Contracts!H2:H5000=0)),5,-1)
=COUNTIFS(Contracts!G:G,TRUE)-COUNTIF(Contracts!J:J,"Renewed")-COUNTIF(Contracts!J:J,"Renewed late")-COUNTIF(Contracts!J:J,"Lost")-COUNTIF(Contracts!J:J,"Lost then won back")-COUNTIF(Contracts!J:J,"Pending")
Zero.
Renewals edited in place. If the system overwrites the end date on renewal instead of creating a new term, the export has no lost contracts at all. Ask for contract history.
Upsells read as renewals. A mid-term add-on starts after the original and looks like a following contract. Match on product as well as customer, as above.
Pending counted as lost. Contracts that ended last week drag this month's rate down until their grace window closes.
Count only. The two largest contracts on the calendar lost inside a 92.
An annual renewal rate is a fine spreadsheet job. A rolling calendar by account manager, with usage, service and touch signals beside each upcoming renewal, refreshed weekly, is where it strains; see the four signs a spreadsheet is no longer enough. For what the rate should be, see what is a good renewal rate; for the arithmetic by hand, the renewal calendar on five contracts. Covirage computes the same figures from the same export every month, with the grace rule stated and the check built in.
If the system links renewals, export the link. If not, for each contract find the earliest contract for the same customer and product that starts after this one's end date less a few days: MINIFS on start date with customer, product and a start-date criterion. Then look up its value with XLOOKUP on customer, product and start date together.
Thirty days is common. Put it in a cell. A renewal inside the window is a renewal flagged late; one outside it is a loss followed by new business. Change the cell to zero and to ninety and see how far the rate moves: that spread is how much of your renewal rate is late paperwork.
No. A contract is due once, in the period its end date falls in. That is the difference between renewal rate and retention rate, and it is why a base with many multi-year contracts has few renewals to measure in any one year.