Blog · Finance metrics and formulas · Investment management
Calculate rent-weighted unexpired lease terms from a commercial property lease schedule. Build the Excel formulas, separate contractual expiry from tenant break dates, and reconcile the average to rent and expiry concentrations.
A WAULT calculation in Excel is the rent-weighted average of the years remaining on commercial property leases. Multiply each remaining term by annual rent, add the products, and divide by the same leases' total rent. Label the endpoint: contractual expiry and the earliest tenant break answer different exposure questions.
Rent-weighted WAULT = Sum of (annual rent × remaining lease years) / Sum of annual rent
Remaining years, Actual/365 = Days from reporting date to selected endpoint / 365
The selected endpoint is essential. INREV's definition of WAULT uses the earlier of the first tenant break or lease expiry and weights terms by current annual headline rent. This workbook shows both a contractual-expiry calculation and a separate calculation to the earliest available tenant break, using unchanged rent weights.
Use a fixed reporting date instead of a moving date function in a saved reporting pack. State whether rent means annual contractual base rent, cash currently received, or another approved definition. Here it means current annual contractual base rent before reimbursements, with no rent-free periods or scheduled increases in the example.
This is an asset-management exposure measure from the landlord's lease schedule. Keep it separate from the remaining lease term used in a company's lease-liability accounting. Different purposes can produce different populations and assumptions.
Export lease ID, property, tenant, commencement date, contractual expiry, next available tenant break, annual rent, and reporting currency. Retain the original dates and the administrator's approval of amendments. Use a separate property or tenant identifier for concentration checks rather than treating slightly different names as different exposures.
The worked example is a fictional US commercial property portfolio at January 1, 2026. The three leases are active, all rents are USD, and all dates below represent endpoints measured from the start of the reporting date. Do not add an extra day to one lease only.
| Lease | Annual rent (USD) | Contractual expiry | Next tenant break |
|---|---|---|---|
| A: Ohio warehouse | 120,000 | January 1, 2027 | None |
| B: Texas industrial unit | 180,000 | January 1, 2029 | January 1, 2028 |
| C: Arizona distribution center | 300,000 | January 1, 2031 | January 1, 2029 |
| Total | 600,000 |
Check the lease population before computing an average. Vacant space has no active lease term and belongs in a separate vacancy report. A positive rent with an expired lease date requires review for holdover or an unrecorded extension; silently deleting it could improve the apparent result.
Put the input table in A1:D4, with annual rent in B and actual Excel dates in C and D. Leave D blank where no break exists. Set M1 to =DATE(2026,1,1). After validating that the active lease dates are present and future-dated, enter remaining years in E2 and weighted years in F2:
=IF(C2<=$M$1,0,YEARFRAC($M$1,C2,3))
=B2*E2
Fill down through row 4. Microsoft's YEARFRAC documentation identifies basis 3 as Actual/365. That convention includes leap days in the numerator while keeping the denominator fixed, so an anniversary interval spanning a leap year is slightly longer than its whole calendar-year count.
| Lease | Remaining days | Remaining years | Rent × years (USD-years) |
|---|---|---|---|
| A | 365 | 1.0000 | 120,000.00 |
| B | 1,096 | 3.0027 | 540,493.15 |
| C | 1,826 | 5.0027 | 1,500,821.92 |
| Total | 2,161,315.07 |
Remaining years are shown to four decimals and weighted products to two; calculations retain full precision. WAULT to expiry is 2,161,315.07 / 600,000 = 3.6022 years, rounded to four decimals. Calculate from the unrounded cells:
=SUM(F2:F4)/SUM(B2:B4)
An independent spreadsheet check uses SUMPRODUCT, which sums corresponding products:
=SUMPRODUCT(B2:B4,E2:E4)/SUM(B2:B4)
Both formulas must agree. The unweighted average is 3.0018 years, rounded to four decimals, because it gives the smallest lease as much influence as the largest. The rent shares are 20.0%, 30.0%, and 50.0%, respectively, so the longest lease properly carries the greatest weight here.
In G, select the earlier of expiry and the next available tenant break. Use expiry when the break cell is blank. In H calculate remaining years and in I multiply by rent:
=IF(D2="",C2,MIN(C2,D2))
=IF(G2<=$M$1,0,YEARFRAC($M$1,G2,3))
=B2*H2
| Lease | Selected endpoint | Remaining days | Remaining years | Rent × years (USD-years) |
|---|---|---|---|---|
| A | January 1, 2027 | 365 | 1.0000 | 120,000.00 |
| B | January 1, 2028 | 730 | 2.0000 | 360,000.00 |
| C | January 1, 2029 | 1,096 | 3.0027 | 900,821.92 |
| Total | 1,380,821.92 |
The same formula structure gives 1,380,821.92 / 600,000 = 2.3014 years. The difference from contractual expiry is 1.3008 years using unrounded results. This is a timing scenario assuming available breaks are exercised, rather than a prediction that tenants will leave.
Have the lease administrator confirm whether notice deadlines, conditions, or exercised options change the usable date. A historical break date is not automatically the next available exit. Keep assumptions visible in a scenario-analysis workbook rather than overwriting the contractual dates.
The rent denominator must reconcile to the included rent roll: 120,000 + 180,000 + 300,000 = $600,000. Every included lease must contribute to both numerator and denominator under the same ownership and currency basis.
Through January 1, 2028, contractual expiry affects only lease A: 120,000 / 600,000 = 20.0% of annual rent. The break-or-expiry view includes A and B: (120,000 + 180,000) / 600,000 = 50.0%. Both percentages come from the same schedule, yet they point to different leasing workloads.
Use the same exposure discipline as customer concentration analysis when several leases belong to one tenant. Review WAULT alongside vacancy and other property asset-management KPIs, with a named owner for the leases approaching a decision date.
Covirage is not affiliated with Microsoft.
Bring your lease workbook with annual rents, expiry dates, break dates, and a reporting date. Covirage maps the columns, and its tools compute and check the weighted figures; 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 data.
See Covirage for asset managers for analysis from your business files.
WAULT means weighted average unexpired lease term. It summarizes the remaining time on leases using a stated weighting basis, commonly annual rent. Specify whether the endpoint is contractual expiry or the earlier tenant break, because the label alone does not establish that assumption.
Either basis can be used in a clearly labeled property report. Rent weighting measures exposure to the current rent roll, while floor-area weighting measures exposure by occupied space. Do not compare the two as though they were identical, especially when rent per square foot differs across properties.
Keep unexercised renewal options outside a contractual-expiry calculation unless the report explicitly presents an option scenario. Store exercised extensions in the approved lease schedule. The lease administrator should confirm which dates are legally effective before the analyst changes the calculation.
No. WAULT describes timing under stated lease assumptions. It does not assess tenant credit, collectibility, property condition, or the cost of reletting. Review near-term expiry concentrations and tenant exposure alongside the average before deciding where leasing effort or cash reserves are needed.