Commercial Real Estate Development
Development Yield-on-Cost & Interest Reserve Formula
Solve Construction Loan Interest Reserve IR = (k * C_hs) / (1 - k) without circular spreadsheet references, Total Project Cost, Stabilized Yield on Cost (YoC), and Development Spread.
Eliminate circular construction-loan references with one exact algebraic formula!
In ground-up real estate development models, turning on Iterative Calculation to size a Funded Construction Interest Reserve creates fragile circularity: Total Project Cost TPC = C_hs + IR (Land + Hard + Soft Costs C_hs plus Interest Reserve IR), Senior Construction Loan L = LTC * TPC, and Interest Reserve IR = L * r_loan * T_yrs * f_draw (where f_draw ~ 0.50–0.60 is the S-curve average drawn balance factor)! Substituting L = LTC * (C_hs + IR) solves the loop in one exact non-circular formula: let k = LTC * r_loan * T_yrs * f_draw; then TPC = C_hs / (1 - k) and Funded Interest Reserve IR = C_hs * k / (1 - k)! From TPC and Stabilized_NOI, developers benchmark Untrended Yield on Cost YoC = NOI_stab / TPC against Market Exit Cap Rate cap_exit to verify Development Spread = YoC - cap_exit >= 150–200 bps!
Verify on Land_Hard_Soft_Costs C_hs = $38.00M (B2), Loan_to_Cost LTC = 0.625 (62.5%, B3), Construction_Interest_Rate r_loan = 0.08 (8.0%, B4), Build_Duration T_yrs = 2.0 years (B5), S_Curve_Draw_Factor f_draw = 0.50 (B6 -> k = 0.625 * 0.08 * 2.0 * 0.50 = 0.05 exact!), Stabilized_NOI = $3.00M (B7), and Market_Exit_Cap_Rate cap_exit = 0.055 (5.50%, B8): Non-Circular Interest Reserve is 38.00 * 0.05 / 0.95 = $2.00M exact (E2); Total Project Cost is $40.00M (Senior Loan = 0.625 * 40.00 = $25.00M; check interest: $25.00M * 8% * 2 * 0.50 = $2.00M exact without circularity!); Stabilized Yield on Cost is 3.00 / 40.00 = 0.0750 (7.50% in E4); and Development Spread is 0.0750 - 0.0550 = +0.0200 (+200 bps, creating $3.00M / 0.055 - $40.00M = +$14.55M of development profit)!
| Ground-Up Development Metric (C_hs=$38M, 62.5% LTC @ 8%, 2yr, f=0.50, NOI=$3M) | Formula | Verified Output |
|---|---|---|
| Closed-Form Interest Reserve ($M) | C_hs * k / (1 - k), k = LTC*r*T*f_draw | $2.00M (Zero Circularity) |
| Total Project Cost TPC ($M) | C_hs / (1 - k) | $40.00M ($25.00M Loan) |
| Stabilized Yield on Cost (YoC) | NOI_stab / TPC | 0.0750 (7.50%) |
| Development Value Spread | YoC - cap_exit | +0.0200 (+200 bps Spread) |
Non-Circular Interest Reserve & Yield-on-Cost Formula
Validate C_hs > 0, 0 <= LTC < 1, r_loan >= 0, T_yrs > 0, 0 < f_draw <= 1, k = LTC*r_loan*T_yrs*f_draw < 1, NOI_stab > 0, and cap_exit > 0!
=LET(c_hs, B2, lt_c, B3, rt_l, B4, t_yr, B5, f_dr, B6, no_s, B7, cp_x, B8, k_ir, lt_c * rt_l * t_yr * f_dr, IF(OR(c_hs <= 0, lt_c < 0, lt_c >= 1, rt_l < 0, t_yr <= 0, f_dr <= 0, f_dr > 1, k_ir >= 1, no_s <= 0, cp_x <= 0), "Invalid Dev Pro-Forma", ROUND(no_s / (c_hs / (1 - k_ir)), 4)))Development Yield Spread Over Market Exit Cap Formula
Stress-test interest rates rising to r_loan = 0.12 (12.0%, k = 0.075): TPC climbs to $41.08M, YoC compresses to 7.30%, and Development Spread narrows to +180 bps!
=IF(ISTEXT(E4), E4, ROUND(E4 - B8, 4))Try the example
Use the original values or change them to test your understanding.