Intro (with reference handout)

If you’re building valuation or underwriting sheets, mastering a small set of Excel functions goes a long way. A solid refresher is the University of Nottingham handout: Excel: More Functions (PDF). In this post, we extend those ideas with modern dynamic arrays and LAMBDA patterns investors use day-to-day.

1) Lookup & Reshape (clean joins, fewer errors)

• XLOOKUP / XMATCH — safer, clearer joins than legacy VLOOKUP.
• INDEX + MATCH — still useful for 2-way lookups or locked column/row combos.
• CHOOSECOLS / CHOOSEROWS — trim input tables for lighter models.
• TAKE / DROP — grab last N rows (e.g., trailing 12 months) without helpers.
• UNIQUE / FILTER / SORTBY — live slices for dashboards and red-flag lists.

Example (active leases with DSCR < 1.20, sorted by expiry):

=SORTBY( FILTER(Leases, Leases[DSCR]<1.2), Leases[EndDate], 1 )

Example (last 12 months of cash flows):

=TAKE(CashflowTable, 12)

2) Financial Math that Matches Cash-Flow Timing

• XNPV, XIRR — use these for irregular cash flows (real deals rarely align perfectly).
• PMT / IPMT / PPMT / RATE / NPER — amortization and refinancing scenarios.
• DURATION / YIELD — quick fixed-income checks where relevant.

Example (monthly cash flows, exact dates):

=XNPV(CashFlows, Dates, 0.095)
=XIRR(CashFlows, Dates)

Example (refinancing — new payment at rate r for n months):

=PMT(r/12, n, -OutstandingPrincipal)

3) Time & Calendars (schedules that match reality)

• EOMONTH — quarter-end / month-end dates.
• WORKDAY.INTL / NETWORKDAYS.INTL — business-day schedules by locale/holiday set.
• SEQUENCE — generate date timelines instantly.

Example (quarter-end schedule for next 8 quarters):

=EOMONTH(TODAY(), SEQUENCE(8,1,3,3))

4) Text & Data Ingestion (turn raw notes into fields)

• TEXTSPLIT / TEXTAFTER / TEXTBEFORE — parse covenant strings or rent steps.
• TOCOL / TOROW — unpivot notes into one column for analysis.

Example (extract covenant threshold from “DSCR ≥ 1.25x (quarterly test)”):

=VALUE(TEXTAFTER(A2, "≥ "))

Example (pull the % rent step from “2026-01 +3.0% CPI-linked”):

=VALUE(TEXTAFTER(TEXTBEFORE(A2,"%" ), "+"))/100

5) LET + LAMBDA + Helper Functions (readable, fast, reusable)

• LET — name sub-calculations once; faster & easier to audit.
• LAMBDA — package logic into a reusable function (no bloated helpers).
• REDUCE / MAP / SCAN / BYROW / BYCOL / MAKEARRAY — vectorize common patterns.

Example (portfolio DSCR shortfall vs 1.20x using REDUCE):

=LET(
  ds, Table1[DSCR],
  target, 1.20,
  REDUCE(0, ds, LAMBDA(acc, v, acc + MAX(0, target - v)))
)

Example (wrap cap-rate value in your own LAMBDA):

=LAMBDA(noi, caprate, noi / caprate)

Define it once (Name Manager → New), then call: =CapValue([@NOI],[@Cap])

6) Scenario & Stress (investor-grade analysis)

• Data Tables — 1-way/2-way for rate/NOI or cap-rate sensitivity.
• CHOOSE + named scenarios — switch between Base / Bear / Bull cleanly.
• RANDARRAY — quick sampling to test tails (use with care, fix seeds for demos).

Example (cap-rate sensitivity grid):

1. Place target cell Value = NOI / CapRate.
2. Lay out cap rates down rows, NOI across columns.
3. Use a 2-way Data Table to populate values.

Example (list failing assets under a rate shock):

=FILTER(Portfolio, Portfolio[DSCR_Shocked]<1.20)

Resource Box

• Starter handout: University of Nottingham — Excel: More Functions (PDF)
• Dynamic arrays: FILTER, UNIQUE, SORTBY, TAKE, DROP, CHOOSECOLS for cleaner models.
• LAMBDA helpers: MAP, REDUCE, SCAN patterns to replace sprawling helper columns.
• Finance functions: Prefer XNPV/XIRR over legacy NPV/IRR for irregular real-world timing.

Download & Next Steps

In the next post I’ll share a companion workbook outline (tabs: Inputs, Lease Rollover, DSCR, Cap Sensitivity, Debt, Scenarios, LAMBDA Library). For now, start by replacing VLOOKUPs with XLOOKUPs, then layer in FILTER/UNIQUE and one LAMBDA you’ll reuse every week.

Want the template? Add a “File” block here later and attach your workbook (XNPV/XIRR + DSCR dashboard).