Blog · Finance metrics and formulas
Build a property cash view from rent receipts, paid operating costs, capital spending and debt payments. Use spreadsheet formulas and a bank reconciliation to check the result by property.
Cash flow for commercial real estate is collected rent less property costs paid, with capital spending and debt payments shown separately. Build it by property, then prove that the total cash movement matches reconciled bank balances. Billed rent alone cannot tell a property finance team what cash remains.
Use the rent ledger, cash receipts register, property payment export, capital spending register, loan statement and bank reconciliation for the same month. Keep a property identifier on each row. An operating statement helps explain the business, but a statement prepared using accrual accounting needs a bridge to cash.
The SEC's financial statements guide distinguishes profit from cash generation and explains that cash flow statements track cash inflows and outflows. This management report follows those cash movements, while presenting property operations and debt payments in separate layers. It does not replace the company's financial statements.
For each cash transaction, retain the posting date, property, payment reference, cash class and amount. Use classes such as rent receipts, property costs, capital spending, interest, principal and other cash. Property costs can include maintenance, utilities, insurance, property taxes and management fees actually paid in the period. Keep supporting categories so an unusual payment can be traced.
Exclude depreciation from the payment rows because it is not a cash payment. Keep bills not yet paid in a separate payable schedule. The distinction between earnings and money received is covered in cash flow versus profit.
In an Excel worksheet named Cash, use column A for posting date, B for property, C for cash class and D for signed cash amount. Receipts are positive; payments are negative. Enter actual date values, rather than date-like text.
On the summary sheet, put the month start in E1, the next month start in F1, the property in G1 and the cash class in H1. Put a sign multiplier in I1: positive one for receipt classes and negative one for payment classes. This displays outflows as positive amounts to subtract later:
=$I$1*SUMIFS(Cash!$D$2:$D$5000,Cash!$B$2:$B$5000,$G$1,Cash!$C$2:$C$5000,$H$1,Cash!$A$2:$A$5000,">="&$E$1,Cash!$A$2:$A$5000,"<"&$F$1)
Repeat for each property and class. Extend every range together if the export is longer. Microsoft documents SUMIFS as adding values that meet multiple criteria, with criteria ranges matching the sum range. Covirage is not affiliated with Microsoft.
Check the imported row count and signed cash total against the original export before grouping it. A correct formula cannot recover missing transactions or a payment assigned to the wrong property.
Consider an illustrative US company's North and South commercial properties for September 2026. All amounts below are whole USD; no rounding is needed. The receipt amounts come from cash records, while billed rent and receivables come from the rent ledger.
Place the labels in column A, North in B, South in C and the total in D. The row numbers identify the spreadsheet positions.
| Sheet row | Line | North (USD) | South (USD) | Total (USD) |
|---|---|---|---|---|
| 2 | Rent billed | 60,000 | 40,000 | 100,000 |
| 3 | Opening rent receivables | 8,000 | 4,000 | 12,000 |
| 4 | Closing rent receivables | 13,000 | 2,000 | 15,000 |
| 5 | Rent cash receipts | 55,000 | 42,000 | 97,000 |
| 6 | Property operating costs paid | 30,000 | 18,000 | 48,000 |
| 7 | Cash before capital spending and debt | 25,000 | 24,000 | 49,000 |
| 8 | Capital spending paid | 12,000 | 5,000 | 17,000 |
| 9 | Interest paid | 4,000 | 2,500 | 6,500 |
| 10 | Principal paid | 6,000 | 3,500 | 9,500 |
| 11 | Cash after capital spending and debt | 3,000 | 13,000 | 16,000 |
Calculate North's intermediate figure in B7:
=B5-B6
Calculate its final figure in B11, then copy both formulas across for South:
=B7-SUM(B8:B10)
In D2 enter =SUM(B2:C2) and fill down through D11.
North generates $25,000 before capital spending and debt, slightly above South's $24,000. After the listed payments, North retains $3,000 against South's $13,000. The capital and debt lines explain why similar operating cash produces different remaining cash. Neither result establishes a property's value or an investment recommendation.
First reconcile rent. In this example there are no credits, write-offs or advance-rent movements, so expected receipts equal opening receivables plus billed rent minus closing receivables. In an empty check cell for North, enter:
=B5-(B3+B2-B4)
The result is zero: $8,000 plus $60,000 minus $13,000 equals $55,000 collected. South also ties to zero. Add separate bridge lines for credits, write-offs or advances when they exist; do not force a difference into receipts.
Next reconcile the combined bank cash on a separate sheet:
| Sheet row | Cash bridge | Amount (USD) |
|---|---|---|
| 2 | Opening reconciled bank cash | 30,000 |
| 3 | Property cash after capital spending and debt | 16,000 |
| 4 | Owner funding received | 10,000 |
| 5 | Tenant security deposits received | 2,000 |
| 6 | Expected closing cash | 58,000 |
| 7 | Actual reconciled closing bank cash | 58,000 |
| 8 | Difference | 0 |
In B6 enter =SUM(B2:B5). In B8 enter =B7-B6; the difference must be zero.
Funding and security deposits explain bank movement but are not rent in this report. Show them separately and assess any restrictions before treating closing cash as available. Eliminate transfers between included bank accounts; resolve clearing differences through the bank reconciliation.
Once the actual month ties, use the cash flow forecast method to project timing. For near-term collection and payment planning, the 13-week cash flow layout provides a separate weekly view.
Bring a sample of your own rental receipts, property payments and supporting statements as a file or Google Sheet. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.
See Excel analysis in Covirage for the supporting workflow.
No. Billed rent records what tenants were charged; receipts record what they paid. Collections can include older balances, while current bills may remain unpaid. Tie the rent ledger to cash receipts before using billed rent in a property cash report.
Include principal when showing cash remaining after debt payments, but display it separately from property operating costs and interest. That lets managers distinguish changes in property operations from changes in financing payments without losing the full cash outflow.
Funding, tenant security deposits, transfers and other movements can change bank cash without changing property operating performance. Reconcile these separately. Also resolve outstanding payments and deposits in transit when comparing ledger cash with a bank statement.
Do not assume so. This report uses actual receipts and payments. A net operating income report may use recognized rental income and accrued expenses under its own definitions. Label each measure and reconcile timing differences before comparing them.