Commercial Real Estate & Leasing
Commercial Lease TI, Free Rent & Net Effective Rent Formula
Calculate ASC 842 Straight-Line Effective Rent, Unamortized Landlord Net Effective Rent (NER), and Time-Value-Discounted Landlord NER ($/SF/yr) after Tenant Improvements, Free Rent, and Commissions.
Compare headline face rent against true landlord Net Effective Rent after concessions!
Office and industrial landlords often preserve a high $60/SF/yr Headline Face Rent R_face for lender appraisals while quietly granting hefty upfront Tenant Improvement Allowances TI_psf, upfront Free Rent Abatement m_free months out of a T_yr-year term, and Broker Leasing Commissions lc_pct on collected base rent! Under GAAP ASC 842 Straight-Line Rent, free rent reduces annual revenue to R_SL = R_face * (T_yr - m_free/12) / T_yr; subtracting unamortized concessions yields Undiscounted NER = (R_face * (T_yr - m_free/12) * (1 - lc_pct) - TI_psf) / T_yr. Even better, institutional asset managers discount front-loaded free rent and Day-1 TI/LC outlays at Landlord Discount Rate r_disc using Annuity Factor AF(T) = (1 - (1 + r_disc)^(-T_yr)) / r_disc: Discounted_NER = (R_face * (AF(T_yr) - AF(m_free/12)) - TI_psf - LC_psf) / AF(T_yr)!
Verify on Headline_Face_Rent R_face = $60.00/SF/yr (B2), Lease_Term T_yr = 10 years (B3), Upfront_Free_Rent m_free = 12 months (1.0 year, B4), TI_Allowance TI_psf = $80.00/SF (B5), Leasing_Commission lc_pct = 0.05 (5.0% of $540 collected rent = $27.00/SF, B6), and Discount_Rate r_disc = 0.08 (8.0%, B7): ASC 842 Straight-Line Base Rent is $54.00/SF/yr (E2); Undiscounted Landlord NER is (540 - 80 - 27) / 10 = $43.30/SF/yr (E3); Discounted NER drops to $35.77/SF/yr (E4—40.4% below the $60.00 face rent because $107/SF of Day-1 cash + Year-1 abatement occur upfront!); and Concession Payback Period is (80 + 27) / 60 + 1.0 = 2.78 years (E5)!
| Commercial Lease Metric ($60/SF Face, 10-Yr, 12m Free, $80 TI, 5% LC, 8% Disc) | Formula | Verified Output |
|---|---|---|
| ASC 842 Straight-Line Rent ($/SF/yr) | R_face * (T_yr - m_free/12) / T_yr | $54.00 / SF / yr |
| Undiscounted Landlord NER ($/SF/yr) | (Collected_Rent - TI - LC) / T_yr | $43.30 / SF / yr |
| Discounted Landlord NER ($/SF/yr) | (PV_Rent - TI - LC) / AF(T_yr, r_disc) | $35.77 / SF / yr |
| Lease Concession Payback (Years) | m_free / 12 + (TI + LC) / R_face | 2.78 years |
Time-Value-Discounted Landlord Net Effective Rent Formula
Handle r_disc = 0 cleanly via IF(r_disc = 0, T_yr, (1 - (1 + r_disc)^(-T_yr)) / r_disc) so setting 0% discount collapses Discounted NER identically to Undiscounted NER ($43.30/SF/yr)!
=LET(r_fc, B2, t_yr, B3, m_fr, B4, ti_p, B5, lc_r, B6, rt_d, B7, t_fr, m_fr / 12, IF(OR(r_fc <= 0, t_yr <= 0, m_fr < 0, t_fr >= t_yr, ti_p < 0, lc_r < 0, lc_r >= 1, rt_d < 0), "Invalid Lease Terms", LET(af_t, IF(rt_d = 0, t_yr, (1 - (1 + rt_d) ^ (-t_yr)) / rt_d), af_f, IF(t_fr = 0, 0, IF(rt_d = 0, t_fr, (1 - (1 + rt_d) ^ (-t_fr)) / rt_d)), lc_p, r_fc * (t_yr - t_fr) * lc_r, ROUND((r_fc * (af_t - af_f) - ti_p - lc_p) / af_t, 2))))Landlord Concession Payback Years Formula
Measure how many years of occupancy pass before cumulative rent collected recovers free-rent months plus Day-1 TI and broker checks!
=IF(ISTEXT(E4), E4, ROUND(B4 / 12 + (B5 + B2 * (B3 - B4 / 12) * B6) / B2, 2))Try the example
Use the original values or change them to test your understanding.