Open the full-size previewRecord twelve months of cash activity
Enter rent actually collected and other operating cash income. Record property taxes, insurance, repairs, management charges, utilities, HOA/service charges and other operating costs paid. The file then shows the operating cash surplus before financing.
Interest and principal are entered separately and combined into debt service. Cash after debt service is reduced by actual capital expenditure and the separately entered reserve allocation to show available cash after those uses. A reserve allocation is an internal earmarking assumption, not a second operating expense or proof that a repair occurred.
Reproduce the example
Each invented month contains $2,400 rent collected and $100 other operating income. Operating costs total $850, leaving a $1,650 cash operating surplus. Interest of $900 and principal of $300 produce $1,200 debt service and $450 cash after debt. Subtracting $50 capex and a $100 reserve allocation leaves $300 available.
With twelve identical invented months, available cash totals $3,600. The operating-surplus/debt-service ratio is 1.375 before display rounding. The annual ratio divides matching annual totals; it does not add monthly ratios. A month with no debt service has an unavailable coverage ratio.
Do not deduct vacancy twice
Rent actually collected is already an observation of the cash that arrived. The record has no additional vacancy-percentage deduction. Subtracting an assumed vacancy loss from actual receipts again would understate the recorded cash. Use the separate property-analysis file when starting from potential rent and forecast loss assumptions.
Refundable deposits are not included in operating cash income. The file is not a complete bank ledger: it does not reconcile deposit custody, owner transfers, opening and closing bank balances or all accounting adjustments. Keep those records separately and use classifications appropriate to the actual transaction.
Check missing months and negative results
Every blue monthly numeric input is required. Enter 0 for a verified absence of activity. A missing input makes the affected month and annual result unavailable instead of silently omitting that amount. Zero rent can legitimately produce negative available cash, and the file preserves that shortfall.
The first recorded month must be a valid first day of a month. The following eleven headers advance from it. All example months are invented, including months that have not occurred. Replace the period and every input consistently when creating an actual record. The result is a cash-based management view, not taxable profit or a certified lender NOI/DSCR calculation.
Open, edit and save
Open the file in Microsoft Excel and save a dated working copy before replacing blue inputs. Native recalculation, representative input changes and save/reopen were tested in separate copies. Google Sheets can import XLSX through File → Import → Upload, but native Sheets layout and recalculation have not been verified. Review formulas, dates and charts after conversion.
Your edited records stay on your device or the storage service you choose. This site supplies a template and does not receive tenant names, lease files or account credentials. Normal hosting can log page and file requests. Use fictitious labels when experimenting with examples.
Sources and definition boundaries
Fannie Mae Form 4254.DEF distinguishes rental operating income, operating costs and reserve-related measures. Its underwriting and reporting conventions are more detailed than this original model. The IRS rental-income overview explains the distinction between rent and refundable deposits, among other tax topics. These files do not determine tax treatment, prepare a tax return or certify lender coverage. Layouts and all numerical examples are original AllMarkets material.
Read the full methods and verification · Compare all three workbooks