Open the full-size previewTwenty unit rows, one monthly snapshot
Each row has a property label, unit label, status, potential monthly rent, actual rent collected, refundable deposits held and an optional lease-end date. Status is Occupied, Vacant or Unused. The full monthly scheduled amount equals potential rent for an occupied unit and zero for a vacant or unused row. The snapshot date does not prorate partial-month leases.
Potential rent includes the entered potential of vacant units. Physical occupancy counts occupied units divided by occupied plus vacant units. It is a count-based ratio, not a rent-weighted ratio. With no active units, the occupancy ratio is unavailable rather than an invented 0%.
Collections and shortfalls
The file calculates a shortfall for each unit when current-month scheduled rent exceeds recorded collections. A separate amount-above-due field retains receipts beyond the scheduled amount. These two fields do not offset one another across units. The reconciliation is scheduled due plus amounts above due minus collections minus shortfalls, which should equal zero.
In the invented example, three units are occupied and one is vacant. Potential monthly rent is $4,700, scheduled due is $3,600 and collections total $3,600. One occupied unit is $50 short, while another has $50 above due. A portfolio-level collection total equal to scheduled due therefore does not prove that every unit paid exactly its own monthly amount.
Refundable deposits and lease dates
Refundable deposits held are a separate stock balance and are never added to rent collections. The example has $3,600 deposits held, but that does not create an extra $3,600 of rent. If you retain or apply a deposit, review the records and applicable treatment separately; this file does not determine a legal or tax classification.
A lease-end date before the snapshot date receives a review label. That label does not establish that a tenant must leave, owes arrears or lacks a valid month-to-month agreement. No legal conclusion or tenant message is generated.
Prevent missing and duplicate records
Active property/unit pairs must be unique. A duplicate key is exposed in both affected rows and prevents complete summary totals. All active numeric fields are required, including a deliberate 0 when no collection occurred. An unused row requires zero money inputs. A blank required amount is not treated as a zero.
The range is bounded to twenty rows. Extending it requires updating every summary, validation and formula range; simply typing a twenty-first row does not include it automatically. Keep a dated copy of the original and reconcile your edited snapshot to source lease and receipt records.
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