Blog · Territory, capacity and quota planning
Build a property operating workbook from rent, vacancy, expenses and capital spending. Follow a monthly example, a vacancy sensitivity and the checks that connect property income with planned cash.
Real estate financial modeling connects rent, vacancy, operating costs and capital spending to property cash. Start with the rent roll and expense ledger, calculate each subtotal, and compare the result with actual collections and payments. A useful operating workbook shows why cash changed, not just whether reported income rose.
Choose one operating property or a clearly identified property group. Use the same month, currency and ownership scope for the rent, expenses, capital spending and debt schedule. This example is an illustrative US apartment operator planning one month in USD.
The purpose is operating planning: how much rental revenue supports running costs, planned improvements and debt payments. Property valuation, securities recommendations, transaction underwriting and investor distribution waterfalls require different schedules.
Use the general financial modeling guide for the wider workbook structure. Construction draws and development milestones belong in a separate real estate development financial model.
Before calculating, agree on the meaning of net operating income, or NOI. Here it is effective property revenue less the listed recurring property operating expenses. It excludes capital spending, depreciation, debt service and income taxes. That is the management definition for this example, not a claim that every lender or report uses the same boundary.
Use a rent roll with property, unit, rentable status, contractual monthly rent, lease dates and concessions. Add an operating expense export with property, posting period, expense category and amount. Bring capital project payments and scheduled principal and interest payments as separate rows.
Keep vacant units in the potential-rent schedule. Their missed rent becomes a visible vacancy deduction. If the rent roll contains only occupied units, rebuild potential rent before applying a vacancy percentage, or you will deduct vacancy from an already reduced base.
Keep refundable tenant deposits distinct from rental revenue. IRS Publication 527 distinguishes deposits intended to be returned from amounts used as rent. That is tax reporting guidance, but the distinction also highlights why a bank deposit is not enough to classify a property receipt for this workbook.
Tag capital spending separately from recurring expenses. Tax depreciation has its own timing: IRS Publication 946 describes recovering qualifying property costs over time through depreciation. The cash schedule below instead records the improvement payment when it is made; tax depreciation does not determine this management cash calculation.
The example assumes effective revenue is collected and operating bills are paid in the planning month. There are no changes in receivables, advance rent or operating payables. Add those timing movements when that assumption does not hold.
Effective property revenue = Potential rent - Vacancy loss - Credit losses + Other operating income
NOI = Effective property revenue - Defined property operating expenses
Planned cash after debt service = NOI - Capital spending - Debt service
Put the labels below in column A and the values or formulas in column B, starting on spreadsheet row 2.
| Row | Item | Amount (USD), except units and percentages |
|---|---|---|
| 2 | Rentable apartments | 40 |
| 3 | Monthly potential rent per apartment | 2,000 |
| 4 | Gross potential rent | 80,000 |
| 5 | Vacancy assumption | 5.0% |
| 6 | Vacancy loss | 4,000 |
| 7 | Credit losses | 1,000 |
| 8 | Other operating income | 2,000 |
| 9 | Effective property revenue | 77,000 |
| 10 | Property taxes | 8,000 |
| 11 | Insurance | 3,000 |
| 12 | Recurring repairs | 4,000 |
| 13 | Utilities | 5,000 |
| 14 | Property payroll | 6,000 |
| 15 | Management expense | 3,000 |
| 16 | Total operating expenses | 29,000 |
| 17 | NOI | 48,000 |
| 18 | Capital spending paid | 12,000 |
| 19 | Debt service paid, principal and interest | 18,000 |
| 20 | Planned cash after debt service | 18,000 |
The spreadsheet formulas are:
B4 = B2*B3
B6 = B4*B5
B9 = B4-B6-B7+B8
B16 = SUM(B10:B15)
B17 = B9-B16
B20 = B17-B18-B19
This property generates $48,000 of NOI but only $18,000 after the listed capital payments and debt service. The difference is fully visible in the two rows below NOI. The cash figure is before distributions and income taxes, and includes no financing proceeds or asset purchases beyond the listed capital spending.
Copy the calculation into column C and change only the vacancy assumption from 5.0% to 10.0%. Keep potential rent, credit losses, other income and all spending fixed. This isolates vacancy rather than combining it with rent increases or cost reductions.
| Measure | Base (USD) | Higher vacancy (USD) | Change (USD) |
|---|---|---|---|
| Gross potential rent | 80,000 | 80,000 | 0 |
| Vacancy loss | 4,000 | 8,000 | 4,000 |
| Effective property revenue | 77,000 | 73,000 | -4,000 |
| Operating expenses | 29,000 | 29,000 | 0 |
| NOI | 48,000 | 44,000 | -4,000 |
| Capital spending | 12,000 | 12,000 | 0 |
| Debt service | 18,000 | 18,000 | 0 |
| Cash after debt service | 18,000 | 14,000 | -4,000 |
The cash reduction is potential rent times the five percentage point vacancy increase: $80,000 times 5.0% equals $4,000. Dollar figures are shown to whole dollars and percentages to one decimal place; calculations use the underlying inputs.
The property manager owns the occupancy actions and leasing assumptions. Finance owns the cash bridge and consistency with approved records. Use real estate financial reporting to organize the resulting actual-versus-plan review.
Three checks close the workbook:
Revenue check = B4-B6-B7+B8-B9
Operating check = B9-B16-B17
Cash check = B17-B18-B19-B20
Each must equal zero. Then reconcile collected rent and other operating receipts to the cash records, and each expense category to the property ledger. Match the capital schedule to approved project payments and the debt schedule to the payment records.
When rent remains unpaid or expenses remain in payables, build an explicit timing bridge. Do not silently treat accrual NOI as bank cash. The base example's collection assumption is a simplification to remove that timing difference, not a general rule for properties.
Bring your prepared rent, vacancy, expense and spending workbook to Covirage; its tools map the columns, compute and check the supplied figures, and the external AI model explains actual-versus-plan differences with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; account creation takes about a minute.
See forecast analysis for the supporting planning context.
In the operating definition used here, net operating income excludes capital expenditures, debt service, depreciation and income taxes. Show capital spending as a separate cash requirement below that subtotal, and document your definition before comparing it with another property report.
Calculate vacant rent from gross potential rent and the chosen vacancy assumption, or from identified vacant units. Keep concessions and credit losses separate. Check that a vacant unit has not already been removed from the potential-rent starting figure.
Yes. Capital projects, debt payments and collection delays can use cash even when the operating subtotal is positive. A property workbook needs an explicit bridge from operating income to cash, with timing assumptions stated for receipts and payments.