Articles

How do you build a lease expiry profile in Excel?

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 →

The profile

Brackenridge portfolio (fictional), 16 leases, passing rent of EUR 3,795.5 thousand at 31 December 2026.
ExpiryLeasesRent (EUR k)ShareCumulative
20274655.517.3%17.3%
20283318.08.4%25.6%
20293730.419.2%44.9%
20301588.015.5%60.4%
20311304.08.0%68.4%
2032 and later41,199.631.6%100.0%
Total163,795.5100.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%.

The formulas

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:

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 check

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.

Four traps

Reading the profile

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.

Asking an assistant for these formulas

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.

Sources

  1. Julian R. Sterling, AI in Excel for Analysts (video course, 2026): running case Brackenridge portfolio
  2. Microsoft Support, function reference for SUMPRODUCT, SUMIFS, COUNTIFS, VALUE, SUBSTITUTE, TRIM and DATE
  3. ICAEW, Twenty principles for good spreadsheet practice

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.

Formulas you can prove, step by step

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).

Watch module 1 free →

Questions readers ask

What is a lease expiry profile?

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.

How do you calculate lease expiries by year in Excel?

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.

Should a lease expiry profile use rent or area?

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.

Read the whole case

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

Also on this site

All 2221 articles →