Looking at lease-end dates, when do my tenants typically tell me they will be renewing, and are we falling behind in sending renewal letters for the lotus at starkey ranch?
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
BLUE = Font(name="Arial", size=10, color="0000FF")
BLK = Font(name="Arial", size=10)
BLKB = Font(name="Arial", size=10, bold=True)
HDR = Font(name="Arial", size=10, bold=True, color="FFFFFF")
TITLE = Font(name="Arial", size=12, bold=True)
HFILL = PatternFill("solid", fgColor="1F3864")
TOP = Border(top=Side(style="thin"))
wb = openpyxl.Workbook()
# ---------------- Sheet 1: Pipeline ----------------
ws = wb.active
ws.title = "Renewal Pipeline"
ws["A1"] = "The Lotus at Starkey Ranch (res58961) - Renewal Pipeline as of 9/23/2026"
ws["A1"].font = TITLE
ws["A3"] = "Current pipeline: leases in effect, by days to expiration"
ws["A3"].font = BLKB
hdrs = ["Window", "Expirations", "Renewal signed", "On notice", "No decision", "% resolved"]
for j, h in enumerate(hdrs, start=1):
c = ws.cell(row=4, column=j, value=h)
c.font = HDR; c.fill = HFILL; c.alignment = Alignment(wrap_text=True, vertical="center")
cur = [("0-30 days", 22, 8, 7), ("31-60 days", 20, 8, 6), ("61-90 days", 14, 1, 2), ("91-120 days", 24, 0, 0)]
r = 5
for name, e, rn, nt in cur:
ws.cell(row=r, column=1, value=name).font = BLK
for col, v in ((2, e), (3, rn), (4, nt)):
cc = ws.cell(row=r, column=col, value=v); cc.font = BLUE; cc.number_format = "#,##0"
ws.cell(row=r, column=5, value=f"=B{r}-C{r}-D{r}").font = BLK
ws.cell(row=r, column=5).number_format = "#,##0"
ws.cell(row=r, column=6, value=f"=(C{r}+D{r})/B{r}").font = BLK
ws.cell(row=r, column=6).number_format = "0.0%"
r += 1
ws.cell(row=r, column=1, value="Total 0-120 days").font = BLKB
for col in range(2, 6):
L = get_column_letter(col)
c = ws.cell(row=r, column=col, value=f"=SUM({L}5:{L}{r-1})"); c.font = BLKB; c.number_format = "#,##0"; c.border = TOP
c = ws.cell(row=r, column=6, value=f"=(C{r}+D{r})/B{r}"); c.font = BLKB; c.number_format = "0.0%"; c.border = TOP
ws.cell(row=r, column=1).border = TOP
total_row = r
# Benchmark
r += 2
ws.cell(row=r, column=1, value="Same point in prior years (as of 9/23), % of expirations already resolved").font = BLKB
r += 1
bh = ["Window", "2024: expirations", "2024: no decision", "2024: % resolved",
"2025: expirations", "2025: no decision", "2025: % resolved",
"2026: expirations", "2026: no decision", "2026: % resolved"]
for j, h in enumerate(bh, start=1):
c = ws.cell(row=r, column=j, value=h); c.font = HDR; c.fill = HFILL
c.alignment = Alignment(wrap_text=True, vertical="center")
bench_hdr = r
b24 = [("0-30 days", 23, 12, 7), ("31-60 days", 24, 11, 4), ("61-90 days", 11, 0, 2), ("91-120 days", 17, 0, 0)]
b25 = [("0-30 days", 18, 11, 6), ("31-60 days", 22, 9, 6), ("61-90 days", 19, 3, 2), ("91-120 days", 21, 0, 1)]
r += 1
first_b = r
for i in range(4):
ws.cell(row=r, column=1, value=b24[i][0]).font = BLK
e24, rn24, nt24 = b24[i][1:]; e25, rn25, nt25 = b25[i][1:]
ws.cell(row=r, column=2, value=e24).font = BLUE
ws.cell(row=r, column=3, value=f"=B{r}-{rn24}-{nt24}").font = BLK
ws.cell(row=r, column=4, value=f"=(B{r}-C{r})/B{r}").font = BLK
ws.cell(row=r, column=5, value=e25).font = BLUE
ws.cell(row=r, column=6, value=f"=E{r}-{rn25}-{nt25}").font = BLK
ws.cell(row=r, column=7, value=f"=(E{r}-F{r})/E{r}").font = BLK
ws.cell(row=r, column=8, value=f"=B{5+i}").font = BLK
ws.cell(row=r, column=9, value=f"=E{5+i}").font = BLK
ws.cell(row=r, column=10, value=f"=(H{r}-I{r})/H{r}").font = BLK
for col in (2, 3, 5, 6, 8, 9):
ws.cell(row=r, column=col).number_format = "#,##0"
for col in (4, 7, 10):
ws.cell(row=r, column=col).number_format = "0.0%"
r += 1
r += 1
ws.cell(row=r, column=1, value="How tenants signal: renewal leases executed, days ahead of the new term start").font = BLKB
r += 1
for j, h in enumerate(["Renewal cohort (by term start)", "Renewals signed", "Median days early", "Signed <30 days out", "Signed 60+ days out", "Share 60+ days out"], start=1):
c = ws.cell(row=r, column=j, value=h); c.font = HDR; c.fill = HFILL
c.alignment = Alignment(wrap_text=True, vertical="center")
lead = [(2021, 172, 25, 99, 11), (2022, 177, 15, 128, 6), (2023, 183, 21, 108, 25),
(2024, 169, 30, 80, 29), (2025, 185, 48, 45, 51), ("2026 YTD", 159, 58, 40, 75)]
r += 1
for yr, n, med, u30, o60 in lead:
ws.cell(row=r, column=1, value=str(yr)).font = BLK
for col, v in ((2, n), (3, med), (4, u30), (5, o60)):
c = ws.cell(row=r, column=col, value=v); c.font = BLUE; c.number_format = "#,##0"
c = ws.cell(row=r, column=6, value=f"=E{r}/B{r}"); c.font = BLK; c.number_format = "0.0%"
r += 1
ws.cell(row=r + 1, column=1, value="Blue = input from Yardi lease records. Black = calculated. Notice includes any lease with a notice-to-vacate date.").font = Font(name="Arial", size=9, italic=True)
widths = [30, 13, 14, 12, 13, 12, 13, 12, 13, 13]
for j, w in enumerate(widths, start=1):
ws.column_dimensions[get_column_letter(j)].width = w
for rr in (4, bench_hdr, bench_hdr + 6):
ws.row_dimensions[rr].height = 30
# ---------------- Sheet 2: At-risk exposure ----------------
w2 = wb.create_sheet("At-Risk Exposure")
w2["A1"] = "Cost of a missed renewal window - units expiring within 60 days with no decision"
w2["A1"].font = TITLE
w2["A3"] = "Assumptions"; w2["A3"].font = BLKB
inp = [("Incremental probability of turnover when the renewal offer lands late", 0.20, "0.0%"),
("Days vacant per turn", 35, "#,##0"),
("Turn cost per unit (make-ready, marketing, admin)", 1500, "$#,##0"),
("Concession / new-lease discount vs renewal rent (months)", 0.5, "0.0")]
r = 4
for lbl, v, fmt in inp:
w2.cell(row=r, column=1, value=lbl).font = BLK
c = w2.cell(row=r, column=3, value=v); c.font = BLUE; c.number_format = fmt
r += 1
P_TURN, DAYS, TURN_COST, CONC = "$C$4", "$C$5", "$C$6", "$C$7"
r += 1
w2.cell(row=r, column=1, value="Units with no renewal and no notice, expiring within 60 days").font = BLKB
r += 1
hdr_row = r
for j, h in enumerate(["Unit", "Lease end", "Days out", "Beds", "Market rent ($/mo)", "Vacancy loss if it turns", "Turn cost", "Concession", "Total cost if it turns", "Expected cost (prob-weighted)"], start=1):
c = w2.cell(row=r, column=j, value=h); c.font = HDR; c.fill = HFILL
c.alignment = Alignment(wrap_text=True, vertical="center")
w2.row_dimensions[r].height = 30
units = [("13-204", "9/26/2026", 2, 1, 1462), ("01-205", "10/5/2026", 11, 1, 1592),
("09-202", "10/10/2026", 16, 3, 2641), ("17-204", "10/19/2026", 25, 2, 1880),
("08-203", "10/22/2026", 28, 2, 1880), ("15-218", "10/22/2026", 28, 1, 1502),
("15-104", "10/24/2026", 30, 1, 1642), ("09-203", "11/5/2026", 42, 3, 2431),
("07-203", "11/11/2026", 48, 2, 1880), ("13-105", "11/15/2026", 52, 2, 1945),
("04-305", "11/21/2026", 58, 2, 1870), ("13-201", "11/21/2026", 58, 2, 1880),
("13-209", "11/21/2026", 58, 2, 1840)]
r += 1
first_u = r
for u, le, dout, bd, mr in units:
w2.cell(row=r, column=1, value=u).font = BLK
w2.cell(row=r, column=2, value=le).font = BLUE
c = w2.cell(row=r, column=3, value=dout); c.font = BLUE; c.number_format = "#,##0"
c = w2.cell(row=r, column=4, value=bd); c.font = BLUE; c.number_format = "#,##0"
c = w2.cell(row=r, column=5, value=mr); c.font = BLUE; c.number_format = "$#,##0"
c = w2.cell(row=r, column=6, value=f"=E{r}/30*{DAYS}"); c.font = BLK; c.number_format = "$#,##0"
c = w2.cell(row=r, column=7, value=f"={TURN_COST}"); c.font = BLK; c.number_format = "$#,##0"
c = w2.cell(row=r, column=8, value=f"=E{r}*{CONC}"); c.font = BLK; c.number_format = "$#,##0"
c = w2.cell(row=r, column=9, value=f"=SUM(F{r}:H{r})"); c.font = BLK; c.number_format = "$#,##0"
c = w2.cell(row=r, column=10, value=f"=I{r}*{P_TURN}"); c.font = BLK; c.number_format = "$#,##0"
r += 1
last_u = r - 1
w2.cell(row=r, column=1, value="Total").font = BLKB
w2.cell(row=r, column=1).border = TOP
for col in (5, 6, 7, 8, 9, 10):
L = get_column_letter(col)
c = w2.cell(row=r, column=col, value=f"=SUM({L}{first_u}:{L}{last_u})")
c.font = BLKB; c.number_format = "$#,##0"; c.border = TOP
for col in (2, 3, 4):
w2.cell(row=r, column=col).border = TOP
tot_row = r
w2.cell(row=r, column=3, value=f"=COUNT(C{first_u}:C{last_u})").font = BLKB
w2.cell(row=r, column=3).number_format = "#,##0"
w2.cell(row=r, column=4, value=None)
# sensitivity grid
r += 2
w2.cell(row=r, column=1, value="Expected cost of the 13 open units: turnover probability vs. days vacant").font = BLKB
r += 1
grid_hdr = r
w2.cell(row=r, column=1, value="Incremental turn probability").font = BLKB
days_axis = [21, 28, 35, 45, 60]
for j, d in enumerate(days_axis, start=2):
c = w2.cell(row=r, column=j, value=d); c.font = BLUE; c.number_format = '#,##0" days vacant"'
c.alignment = Alignment(wrap_text=True, horizontal="center")
w2.row_dimensions[r].height = 30
probs = [0.10, 0.15, 0.20, 0.30, 0.40]
r += 1
first_g = r
for p in probs:
c = w2.cell(row=r, column=1, value=p); c.font = BLUE; c.number_format = "0.0%"
for j in range(2, 7):
dl = get_column_letter(j)
f = (f"=SUMPRODUCT($E${first_u}:$E${last_u}/30*{dl}${grid_hdr}"
f"+{TURN_COST}+$E${first_u}:$E${last_u}*{CONC})*$A{r}")
c = w2.cell(row=r, column=j, value=f); c.font = BLK; c.number_format = "$#,##0"
r += 1
w2.cell(row=r + 1, column=1, value="Blue = input. Black = calculated. Market rent from the 9/23/2026 unit status file; vacancy loss, turn cost and concession are editable assumptions.").font = Font(name="Arial", size=9, italic=True)
for j, wd in enumerate([42, 14, 11, 8, 14, 15, 11, 12, 15, 16], start=1):
w2.column_dimensions[get_column_letter(j)].width = wd
wb.save("lotus_starkey_renewal_pipeline.xlsx")
print("saved", tot_row, first_g)
cd /vercel/sandbox && python renewal_pipeline_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py lotus_starkey_renewal_pipeline.xlsx --json integrity.json; echo "exit=$?"; python -c "
import json;d=json.load(open('integrity.json'))
for f in d.get('findings',[]):
if f.get('severity')=='high': print(f)
print({k:len(v) if isinstance(v,list) else v for k,v in d.items() if k in ('findings',)})
"cd /vercel/sandbox && python - <<'EOF'
import re
s=open('renewal_pipeline_workbook.py').read()
old = '''bh = ["Window", "2024: expirations", "2024: no decision", "2024: % resolved",
"2025: expirations", "2025: no decision", "2025: % resolved",
"2026: expirations", "2026: no decision", "2026: % resolved"]'''
new = '''bh = ["Window", "2024: expirations", "2024: resolved (renewed or notice)", "2024: % resolved",
"2025: expirations", "2025: resolved (renewed or notice)", "2025: % resolved",
"2026: expirations", "2026: resolved (renewed or notice)", "2026: % resolved"]'''
s=s.replace(old,new)
old2 = ''' ws.cell(row=r, column=2, value=e24).font = BLUE
ws.cell(row=r, column=3, value=f"=B{r}-{rn24}-{nt24}").font = BLK
ws.cell(row=r, column=4, value=f"=(B{r}-C{r})/B{r}").font = BLK
ws.cell(row=r, column=5, value=e25).font = BLUE
ws.cell(row=r, column=6, value=f"=E{r}-{rn25}-{nt25}").font = BLK
ws.cell(row=r, column=7, value=f"=(E{r}-F{r})/E{r}").font = BLK
ws.cell(row=r, column=8, value=f"=B{5+i}").font = BLK
ws.cell(row=r, column=9, value=f"=E{5+i}").font = BLK
ws.cell(row=r, column=10, value=f"=(H{r}-I{r})/H{r}").font = BLK'''
new2 = ''' ws.cell(row=r, column=2, value=e24).font = BLUE
ws.cell(row=r, column=3, value=rn24 + nt24).font = BLUE
ws.cell(row=r, column=4, value=f"=C{r}/B{r}").font = BLK
ws.cell(row=r, column=5, value=e25).font = BLUE
ws.cell(row=r, column=6, value=rn25 + nt25).font = BLUE
ws.cell(row=r, column=7, value=f"=F{r}/E{r}").font = BLK
ws.cell(row=r, column=8, value=f"=B{5+i}").font = BLK
ws.cell(row=r, column=9, value=f"=C{5+i}+D{5+i}").font = BLK
ws.cell(row=r, column=10, value=f"=I{r}/H{r}").font = BLK'''
s=s.replace(old2,new2)
s=s.replace(''' c.alignment = Alignment(wrap_text=True, horizontal="center")''',''' c.alignment = Alignment(wrap_text=True, horizontal="right")''')
open('renewal_pipeline_workbook.py','w').write(s)
EOF
python renewal_pipeline_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py lotus_starkey_renewal_pipeline.xlsx --json integrity.json >/dev/null; echo "gate=$?"; python skills/xlsx/scripts/recalc.py lotus_starkey_renewal_pipeline.xlsxcd /vercel/sandbox && python -c "
import openpyxl
wb=openpyxl.load_workbook('lotus_starkey_renewal_pipeline.xlsx',data_only=True)
w=wb['Renewal Pipeline']
for r in range(4,25):
print(r,[w.cell(row=r,column=c).value for c in range(1,11)])
w2=wb['At-Risk Exposure']
for r in range(3,34):
print('E',r,[w2.cell(row=r,column=c).value for c in range(1,11)])
"Your tenants now commit about two months out — and that's exactly where you've slipped: seven leases expire inside 30 days with no renewal and no notice, versus one at this point last year.
Renewal commitments at the Lotus have moved dramatically earlier. Renewal leases executed in 2026 were signed a median of 58 days before the new term started, up from 30 days in 2024 and just 15 in 2022 . Nearly half of this year's renewals (75 of 159) came in 60+ days ahead , versus 6 of 177 in 2022. Move-outs signal earlier still: notice-to-vacate lands a median 73 days before lease end . So the decision window at this property is now roughly 60–75 days out, not 30 — and a letter that goes out 45 days ahead arrives after the tenant has already made up their mind.
Measured against that window, the pipeline is behind at the front end only. Of 80 leases expiring in the next 120 days, 48 have no decision — most of those (24) expire 91–120 days out, which is normal. The problem is the near term: 68% of leases expiring within 30 days are resolved, against 94% at the same date in 2025 and 83% in 2024 . The 31–60 and 61–90 day cohorts are running in line with prior years, so this reads as a batch of missed or late first touches on the October expirations, not a broken program.
| Window | 2024 % Resolved | 2025 % Resolved | 2026 % Resolved | 2026 Expirations | 2026 No Decision |
|---|---|---|---|---|---|
| 0-30 days | 68.2%1 | 94.4% | 82.6% | 23 | 4 |
| 31-60 days | 70.0% | 68.2% | 62.5% | 24 | 9 |
| 61-90 days | 21.4% | 26.3% | 18.2% | 11 | 9 |
| 91-120 days | 0.0% | 4.8% | 0.0% | 17 | 17 |
Thirteen units have no renewal and no notice inside 60 days, carrying $24,445 of monthly market rent . Seven of them expire within 30 days — 13-204 in two days, 01-205 in eleven, 09-202 (a 3-bed at $2,641) in sixteen. At a 20% incremental turn risk from late outreach and 35 days of downtime, that open book carries roughly $12,000 of expected cost, and $32,000 if outreach slips further and turns run 60 days vacant . Not portfolio-moving money, but it's avoidable, and a unit that goes month-to-month by default also gives up the renewal increase entirely.
What I'd change: move the first renewal letter to 90 days before expiration and the follow-up to 75, so the offer is in hand before the tenant's decision point rather than after it. Then work today's seven near-term units by phone, not mail — at 2 to 30 days out, a letter is no longer the tool. Both sheets of the attached workbook are live, so you can reset the turn probability and downtime assumptions to your own make-ready experience.
If your renewal letters are logged somewhere outside Yardi, send me that log and I can measure actual send-date-to-decision lag rather than inferring the window from signing behavior.
This is a read-only snapshot of a RealAI analysis.