Boston Multi Family Cash Flow Worksheet: A Worked Example With Stress Tests

Affiliate disclosure: This article contains commercial referral links. Homzora may earn a commission from qualifying activity.

A rental investment worksheet should make it possible to trace every result back to an input. If a projected return cannot be explained without changing definitions halfway through the calculation, it is not ready to support a purchase. Separate scheduled rent, collected income, operating expenses, financing, reserves, and initial cash so that each decision remains visible.

The entire property example in this article is hypothetical. It is not a Boston listing, market average, lender quote, or investment recommendation. Its purpose is to demonstrate a consistent calculation. Replace every assumption with records and appropriate professional advice for the building you are considering. Tax treatment, legal obligations, and lending decisions require their own review.

Create an input register

Start with columns for the input, amount, period, evidence, and confidence. A monthly rent belongs beside its lease or supporting record. An annual insurance estimate belongs beside the quote and its date. An unknown cost should be labeled unknown rather than entered as zero merely to complete the spreadsheet.

Keep the source documents outside the calculation sheet but give them clear references. This lets another reviewer understand the model without searching through an unorganized folder. When a figure changes, preserve the earlier version and record the reason. Your analysis should improve as evidence arrives, not silently replace inconvenient assumptions without an explanation.

Establish scheduled rental income

Assume three hypothetical units each have scheduled monthly rent of $2,000. The total is $6,000 per month and $72,000 per year. This is gross scheduled rent before vacancy, concessions, or collection losses. It is not money already received and it is not profit.

For an actual building, list units separately and reconcile the rent schedule with agreements and payment records. Do not substitute a seller’s proposed future rent for current contracted income without clearly labeling the scenario. If there are other income sources, document them separately so that they are not accidentally counted both in rent and in an additional revenue line.

Apply the stated income allowance

In this example, assume a $3,600 annual allowance for vacancy and collection shortfalls, equal to 5 percent of scheduled rent. Subtracting it from $72,000 produces $68,400 of modeled effective rental income. The 5 percent is an invented assumption for this worksheet, not a recommended Boston allowance.

For a real purchase, examine the property’s records and your operating plan. A percentage can make the arithmetic tidy while concealing a specific vacancy or unpaid balance. Show known circumstances directly and explain how the allowance relates to them. Avoid deducting a known loss twice unless you intentionally describe a separate additional downside scenario.

Build operating expenses by category

Assume annual operating expenses of $9,000 for taxes, $4,800 for insurance, $3,600 for owner paid utilities, $4,000 for routine maintenance, $5,000 for management, and $2,000 for other recurring services. These hypothetical amounts total $28,400. They are illustrative inputs, not local quotes.

Use Boston’s property information as one starting point for the actual property’s municipal record, then obtain the applicable bills and other supporting evidence. Insurance and service costs should come from appropriate quotes or records. Do not assume that a seller’s personal arrangements will remain available to you at the same price after the purchase. Source: Boston: Property lookup

Calculate net operating income consistently

Subtract the example’s $28,400 operating expenses from $68,400 effective rental income. The result is $40,000 of net operating income under this model. Financing and the separately stated replacement reserve have not yet been deducted. Explain that treatment whenever you present the figure.

Different analyses can handle certain items differently, so the definition matters. Do not compare two properties’ reported figures without checking what each includes. A model that excludes management because the seller works without charging a fee is not directly comparable to one that budgets professional management. Reconcile the categories before interpreting the apparent difference in operating performance.

Show the capitalization calculation

Assume a hypothetical purchase price of $800,000. Dividing the $40,000 modeled net operating income by $800,000 produces 0.05, or a 5 percent capitalization rate using this stated definition. The result is not a market valuation, a guaranteed return, or a measure of cash remaining after your loan payments.

The calculation depends on both the income assumptions and the price. If an input is unsupported, extra decimal places do not make the result more reliable. Keep the operating worksheet beside the percentage. An investor should be able to identify exactly which costs and income allowances produced the numerator before comparing it with another property.

Add financing as a separate layer

Assume annual debt service of $32,000 for illustration. Subtracting that from $40,000 leaves $8,000 before the reserve contribution used below. This article does not calculate a mortgage rate or loan amount that produces that payment. An actual financing scenario must come from the lender and applicable loan terms.

Keep principal and interest treatment clear, and ask how the lender evaluates income and coverage. Your investment worksheet is not the lender’s underwriting decision. If the financing changes, update this layer without rewriting the property’s operating income simply to preserve a desired return. That separation helps you understand whether the issue is the building, the financing, or both.

Make the replacement reserve explicit

Assume a planned annual replacement reserve contribution of $5,000. Deducting it from the $8,000 remaining after debt service leaves $3,000 of modeled cash after that contribution. The reserve is a planning choice in this example, not proof that future replacements will cost exactly that amount.

Distinguish a reserve contribution from a repair that is already due. If a known project requires cash shortly after closing, include it in the initial cash plan or the appropriate period. Do not make it disappear by assuming that an annual reserve will somehow fund an immediate obligation before enough money has accumulated.

Define initial cash before calculating a percentage

Assume total initial invested cash of $250,000 for the example. State what that includes in your real model, such as the down payment, transaction costs, and specified initial work. Avoid using only the down payment in one comparison and a broader cash total in another without disclosure.

The $3,000 modeled annual cash after the reserve contribution divided by $250,000 equals 1.2 percent. Before that contribution, $8,000 divided by $250,000 equals 3.2 percent. Both calculations are arithmetic descriptions of this example, but they use different numerators. Label them clearly instead of displaying the more attractive percentage without explaining the treatment.

Test an expense increase

Increase operating expenses by a hypothetical $4,000 while keeping every other assumption unchanged. Net operating income falls from $40,000 to $36,000. After $32,000 debt service and the $5,000 reserve contribution, modeled cash becomes negative $1,000. This isolates the effect of the expense change.

Do not describe the test as a forecast. It shows sensitivity to a selected assumption. For an actual building, choose a plausible issue supported by the evidence, such as an insurance quote not yet confirmed or a recurring service cost omitted from the seller’s summary. Explain why the scenario deserves attention and what would resolve the uncertainty.

Test income separately before combining risks

Return to the original base case and reduce effective rental income by $6,000. It becomes $62,400. With original operating expenses of $28,400, net operating income is $34,000. After debt service and the reserve contribution, modeled cash is negative $3,000.

Now combine that income reduction with the $4,000 expense increase. Net operating income becomes $30,000, leaving negative $7,000 after the same financing and reserve assumptions. Show the separate and combined cases so readers can follow the arithmetic. Do not accidentally apply the same loss twice when moving between worksheets or describing the final scenario.

Keep resident funds outside operating cash

Security deposits and related resident obligations require appropriate handling. They should not be casually included as revenue available to cover a projected shortfall. Massachusetts publishes specific requirements for security deposits and last month’s rent; obtain advice on the transaction and your responsibilities. Source: Massachusetts: Security deposits and last month’s rent

Request a reconciliation of those records during due diligence and maintain clear accounts after acquisition. A spreadsheet that appears solvent only because it uses money owed or held for someone else is not presenting a reliable operating plan. Keep the legal and accounting treatment visible and ask qualified professionals to resolve uncertainty before relying on the funds.

Choose tools that preserve the definitions

A spreadsheet can work if its formulas and inputs are controlled. Software can help organize records, but it does not make unsupported figures correct. Test imports, categories, and reports with fictional data before using resident information. Reconcile the software output to the same definitions used in your purchase analysis.

The optional Buildium partner link is a commercial referral, and Homzora may earn a commission. Review the provider’s current features, fees, and terms independently. This is not a claim that the platform guarantees compliance, profitability, or correct bookkeeping. Select a tool only after confirming that it supports the records and workflow you actually need.

Decide what the worksheet still cannot answer

The example does not include every possible cost, tax consequence, capital event, sale outcome, or legal issue. It also does not establish the likelihood of the stress cases. Write the exclusions and open questions beside your real analysis. A clear limitation is more useful than a falsely complete return figure.

Finish with a decision sheet identifying verified inputs, provisional amounts, required cash, and the questions that could change your conclusion. An investment calculation is useful when it exposes the conditions under which the purchase works or fails. It should help you investigate the property, not decorate an assumption that the deal must be attractive.