One SUMIFS per year, a last bucket that stays open, and a check that every euro of rent landed somewhere.
A lease expiry profile shows how much passing rent ends in each year. In Excel it is one SUMIFS per year on the expiry year of each lease, a last bucket for everything beyond the horizon, and a check that the buckets add up to the total rent. On the rent roll of this example, 26% of the rent expires within two years and 32% runs to 2032 or later.
The case comes from the AI in Excel for Analysts course by Julian R. Sterling, author of The Real Estate Asset Manager.Get the DRM-free PDF, $19.99 →
| Expiry | Leases | Rent (EUR k) | Share | Cumulative |
|---|---|---|---|---|
| 2027 | 4 | 655.5 | 17.3% | 17.3% |
| 2028 | 3 | 318.0 | 8.4% | 25.6% |
| 2029 | 3 | 730.4 | 19.2% | 44.9% |
| 2030 | 1 | 588.0 | 15.5% | 60.4% |
| 2031 | 1 | 304.0 | 8.0% | 68.4% |
| 2032 and later | 4 | 1,199.6 | 31.6% | 100.0% |
| Total | 16 | 3,795.5 | 100.0% |
The largest single year is 2029, with EUR 730.4 thousand of rent, 19.2% of the total. The open bucket, 2032 and later, holds 31.6%.
With the passing rent in column G and the expiry year in column H of the data sheet, and the year of each bucket in row 4 of the summary:
=SUMIFS(Data!$G$5:$G$24,Data!$H$5:$H$24,D$4)=SUMIFS(Data!$G$5:$G$24,Data!$H$5:$H$24,">="&I$4)=COUNTIFS(Data!$H$5:$H$24,D$4,Data!$G$5:$G$24,">0")The comparison operator goes inside the quotation marks and the cell reference outside, joined by an ampersand. If the data holds a full expiry date, add a column with =YEAR(date) first: a criterion on a year is easier to read and to check than a pair of date bounds.
The six buckets must add up to the total passing rent, EUR 3,795.5 thousand, and the number of leases to 16. Write the difference in a cell and test it against a tolerance. A profile that stops at a closed last year silently drops every lease beyond it: here, EUR 1,199.6 thousand would be missing.
Share of rent, not number of leases, measures the exposure: 4 leases expire in 2027 and carry 17.3% of the rent. The cumulative line answers the lender's question directly: how much of the income must be renewed before the loan matures.
Give the layout, the buckets and the rule for the last one: "years 2027 to 2031 in D4:H4, 2032 and later in I4, vacant units have an expiry year of 0 and a rent of 0". Without the last sentence, the assistant cannot know how your export codes a vacant unit. Then ask for nothing more until the buckets tie to the total.
Every figure is computed by script from the course's running case, the Brackenridge portfolio, a fictional rent roll of 20 units, and checked on 19 November 2026. Function availability depends on your version of Excel. For education, not investment advice.
26 short video lessons on using an AI assistant in Excel without being wrong: the five-part brief, SUMIFS, SUMPRODUCT, INDEX and MATCH, a mini sample with a known answer, checks that tie and the audit of a received model. You build a checked summary from a rent roll, with step files, exercises, a prompt kit and a certificate exam ($149).
A table that shows, for each future year, the passing rent of the leases that end in that year, usually with its share of total rent and a cumulative share.
Add a column with the expiry year of each lease, then use one SUMIFS per year on the rent, with a last bucket that uses the criterion greater than or equal to the final year.
Rent for income risk, area for leasing workload. Lenders and valuers usually read the rent-weighted profile. State the basis in the title of the table.
This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
See the course →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Prefer a PDF? The same book is sold as a DRM-free PDF edition, searchable and printable. Get the PDF edition, $19.99