Rental template methods and verification

The three workbooks serve different workflows: a monthly unit snapshot, an actual cash record and a first-year forecast. They do not automatically exchange data.

Input conventions

Examples use invented property labels and numeric USD amounts. Blue inputs on pale yellow are editable; formulas calculate the results. Green formulas in working areas refer to another worksheet. Comparison outputs use dark text. Monetary negatives display in parentheses and true zeros as dashes, except reconciliation differences which display 0.00.

Required blanks differ from zero. Incomplete amounts expose n.a. rather than being removed from totals. Invalid active property inputs affect that property’s forecast, leaving a separately valid property usable. A zero-loan scenario ignores its unused rate and term and reports debt coverage as unavailable.

Rent roll scope

The twenty-row range uses a unique property/unit key for each active row. Occupied units have full scheduled potential rent; vacant and unused rows have zero scheduled due. Collections are entered separately. Shortfall and above-due amounts are calculated per row. Refundable deposits are held balances, not monthly rent. Lease-end review is informational and does not determine legal occupancy.

The snapshot does not prorate partial months, reconstruct prior arrears, distinguish every type of advance rent, issue receipts or send tenant messages. To extend beyond twenty rows, update all bounded summary and validation ranges as well as formulas. A row below the range is not automatically included.

Actual cash record

Operating cash income less operating costs paid gives cash operating surplus. Subtract separately entered interest and principal for cash after debt. Then subtract actual capex and a reserve allocation for available cash after those uses. A reserve allocation is not an additional operating expense. The allocation difference verifies this displayed relationship.

All annual flows are sums of twelve complete months. Annual coverage divides annual operating surplus by annual debt service. No vacancy percentage is deducted from rent already collected. The record does not calculate taxable income, bank balances or lender-normalized NOI.

Two-property first-year forecast

Vacancy is deducted from potential rent first, followed by credit loss on occupied rent. Effective gross income includes other operating income. Fixed annual operating costs are allocated monthly with a final-month cent adjustment; management is a percentage of effective income. NOI excludes financing, capex and reserves.

The loan uses monthly annual-rate/12 interest rounded to cents, a rounded annuity payment and a final contractual payoff adjustment. Month-end debt equals opening principal plus interest minus payment. The annual debt balance uses the last month, not the sum of monthly balances. Cash invested is acquisition cost plus closing and initial rehabilitation less the loan. Debt above total acquisition cost is outside scope.

Cap rate divides annual NOI by price. Cash-on-cash divides available cash after capex and reserve allocation by invested cash. Coverage is shown using both NOI and NOI less reserve allocation, each divided by debt service. These are disclosed model measures, not certified underwriting. Zero denominators remain unavailable. There is no appreciation, sale, tax, depreciation schedule, variable-rate path, fee APR or daily loan interest.

Verification performed

Every worksheet was rendered and inspected. Independent high-precision calculations were compared with exported workbook results for rent-roll reconciliation, annual cash allocation, monthly property income and amortization. Microsoft Excel checks include missing versus zero, duplicate units, deposit exclusion, no-loan inputs, full vacancy, a one-month loan, annual-cent adjustments, missing chart data and save/reopen. No macro or external workbook relationship is included.

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.