By Duane Buziak, NMLS #1110647: this guide builds an investment property analysis worksheet that answers two questions about a rental deal: does it cash flow, and will it finance? You finish with a reusable spreadsheet, one fully worked example, and a DSCR check that lenders actually use. Before you start, have a property address or listing, a spreadsheet tool such as Excel or Google Sheets, and rough rent and expense estimates. Every number below is illustrative, not a rate quote or a lending decision.
Step 1: Set Up the Worksheet and Enter the Purchase Inputs
Open a blank sheet and make one labeled input block at the top. Every formula you write later should point to these cells and nowhere else:
- Purchase price
- Down payment percentage (and the dollar amount it calculates)
- Closing costs
- Rehab budget
- Cash reserves
- Interest rate, loan term, and rent assumptions, added as you reach those steps
Decide the financing scenario now, because it changes the down payment and rate inputs. A conventional investment loan underwrites your personal income and debts. A DSCR loan, covered in Step 6, underwrites the property’s rent. Minimum down payments on investment properties vary by program, lender, and unit count. As of 2026, conventional investor loans commonly require meaningfully more down than owner-occupied loans, so check the current Fannie Mae Selling Guide and eligibility matrix before you lock the input.
The most common spreadsheet mistake is hardcoding a number inside a formula, such as typing =300000*0.25. It works for one property and silently breaks when you test a second. Write =B2*B3 instead, where those cells hold price and down payment percentage.
The worked example for the rest of this guide uses these illustrative inputs:
- Purchase price: $300,000
- Down payment: 25%, or $75,000, leaving a $225,000 loan
- Closing costs: 3% of price, or $9,000 (an assumption)
- Rehab budget: $6,000
- Reserves set aside: $9,000
- Expected rent: $2,400 per month
Total cash invested is $75,000 + $9,000 + $6,000 + $9,000 = $99,000. Give that figure its own cell. You will need it in Step 5.
Step 2: Estimate Rental Income Realistically
Do not use the owner’s hoped-for rent or a single listing. Pull three to five comparable rentals of similar size, condition, and location, and use the middle of the range. Record the source of each comp in the sheet so you can defend the number later.
Then add a vacancy allowance row. This is your assumption, not a statistic. Many investors use something in the range of 5 to 8% depending on the market and the property type. The example uses 6%:
- Gross scheduled rent: $2,400 x 12 = $28,800
- Vacancy allowance (6%): $1,728
- Effective gross income: $27,072
Add secondary income lines for laundry, parking, or pet fees only when they are documented, meaning a lease clause or existing history. Leave them at zero otherwise. The example has none.
Lenders treat rent differently from how you do. On conventional loans, underwriters typically count only a portion of market rent, supported by an appraiser’s rent schedule and often a lease, rather than the full figure. The exact percentage and documentation rules are set in the Fannie Mae Selling Guide and can change, so confirm the current treatment as of 2026 before relying on it.
The mistake to avoid is treating gross rent as income you keep. Rent is the top of the waterfall. Vacancy, taxes, insurance, repairs, and management all come out before a dollar reaches you.
Step 3: List Every Operating Expense
Give each expense its own row: property taxes, insurance, maintenance and repairs, a capital expenditure (capex) reserve for big-ticket replacements like roofs and HVAC, property management, HOA dues, and any utilities the owner pays. Keep the mortgage payment out of this block. It belongs in Step 4.
Source real numbers instead of rules of thumb:
- Taxes: check the county assessor or tax collector’s records, and remember that a sale can trigger reassessment in some places.
- Insurance: get an actual landlord policy quote, which usually differs from a homeowner policy.
- Management: ask a local manager for their fee schedule, including leasing and renewal fees, even if you plan to self-manage. Your time has a price.
- Maintenance and capex: use a percentage of rent or a per-year figure based on the property’s age and condition, and write down the assumption.
The “50% rule,” which says expenses will run about half of rent, is a quick screen for deciding whether a listing deserves a closer look. It is not a calculation. Once you have real quotes, the rule has no role.
The example’s annual expenses:
- Property taxes: $3,300
- Insurance: $1,500
- Maintenance (5% of gross rent): $1,440
- Capex reserve (5% of gross rent): $1,440
- Property management (8% of effective gross income): $2,166
- Owner-paid water and trash: $600
- HOA: $0
Total operating expenses: $10,446. The mistake here is skipping maintenance and capex because the property is newer. Newer buildings still need repairs, and the reserve line exists so the big bill does not erase a year of cash flow.
Step 4: Calculate Net Operating Income and the Mortgage Payment
Net operating income (NOI) is effective gross income minus operating expenses. It measures what the property earns before any financing, so it excludes the mortgage payment (debt service), income taxes, and your own depreciation.
NOI = $27,072 − $10,446 = $16,626 per year.
Now add the loan. In your sheet, use the PMT function with cell references: =-PMT(rate/12, years*12, loan amount). For the example, the loan amount is $225,000 and the rate cell holds a placeholder of 6.5% on a 30-year term. That rate is an assumption for illustration, not a quote. For general market context, the Freddie Mac Primary Mortgage Market Survey publishes weekly average rates, though those reflect owner-occupied conventional loans. As of 2026, investment property loans are generally priced differently, often at a higher rate, so treat any survey figure as a starting reference and not as your investor rate.
The arithmetic:
- Monthly principal and interest: about $1,422
- Annual debt service: $1,422.16 x 12 = about $17,066
Compare the two annual figures. NOI of $16,626 is already lower than debt service of $17,066, which tells you the deal has a problem before you calculate a single return metric. Step 5 puts exact numbers on it.
Step 5: Compute Cash Flow, Cap Rate, and Cash-on-Cash Return
Three formulas, each answering a different question:
- Monthly cash flow = NOI / 12 minus monthly principal and interest. It shows what lands in your account.
- Cap rate = NOI / purchase price. It shows what the property yields regardless of how you pay for it.
- Cash-on-cash return = annual cash flow / total cash invested. It shows the yield on your actual dollars, so it reflects your financing.
Completing the example:
- Monthly NOI: $16,626 / 12 = $1,385.50
- Monthly cash flow: $1,385.50 − $1,422.16 = about −$37, or roughly −$440 per year
- Cap rate: $16,626 / $300,000 = 5.54%
- Cash-on-cash: −$440 / $99,000 = about −0.44%
At these assumptions the property roughly breaks even and the return on your cash is essentially nil. Whether that is acceptable is your call. Some investors accept thin early returns for appreciation or rent growth, and others require a positive return from day one. There is no universal threshold, which is why Step 7 asks you to set yours in advance.
Use the metrics for their jobs. Cap rate lets you compare two properties on equal footing because it ignores financing. Cash-on-cash tells you whether this particular loan structure works for you. A property can have an attractive cap rate and still produce a poor cash-on-cash figure if the loan is expensive.
The classic error is understating cash invested. If you leave out closing costs, rehab, and reserves and divide by the down payment alone, the return looks better than it is. The $99,000 denominator above includes all four.
Step 6: Add a DSCR Check and Compare Lender Fits in a Table
The debt service coverage ratio (DSCR) is monthly rental income divided by PITIA, which is principal, interest, taxes, insurance, and association dues. Add it as a worksheet row. In the example, PITIA is $1,422.16 + $275 taxes + $125 insurance = $1,822.16, so DSCR = $2,400 / $1,822.16 = 1.32.
Notice the gap. The deal shows slightly negative cash flow in Step 5, yet the DSCR is above 1.0. That is because DSCR ignores maintenance, capex, vacancy, and management. Lenders see 1.32 while your own analysis sees a break-even deal. Keep both numbers in the sheet, because they answer different questions: one is “will it finance,” the other is “is it worth owning.”
DSCR loans qualify the borrower on the property’s cash flow rather than personal income and tax returns. They are a Non-QM product, and Duane Buziak offers them as an independent broker with wholesale access to lenders that specialize in this space. The comparison below shows how three of them are known in the market. It is a starting point for scenario matching, not a program guide:
| Lender | Known for | Typical fit for a rental scenario | Minimum DSCR, max LTV, minimum credit score |
|---|---|---|---|
| A&D Mortgage | Non-QM and alternative-documentation lending | Investors who need underwriting beyond agency guidelines | Confirmed by Duane for your scenario |
| Angel Oak Mortgage Solutions | Non-QM lending | Investors comparing alternative-doc and property-cash-flow programs | Confirmed by Duane for your scenario |
| Arc Home | Non-QM and specialty products | Investors whose file doesn’t fit a standard agency box | Confirmed by Duane for your scenario |
Program minimums change often and differ by property type and borrower profile, which is why the last column is left open. Duane Buziak is an independent broker with wholesale relationships, not an affiliate of any lender above. That arrangement is the practical advantage: he can run your scenario past several lenders and compare pricing and guidelines, whereas calling one lender direct gets you only that lender’s answer.
Step 7: Stress-Test the Deal and Take the Worksheet to a Broker
Duplicate your sheet into three scenario columns: base, higher rate, and higher vacancy or expenses. Change only the inputs and let the formulas do the rest. With the example:
- Base (6.5%): annual cash flow about −$440
- Rate up 1 point (7.5%): P&I about $1,573, annual debt service about $18,879, cash flow about −$2,253
- Rate down 1 point (5.5%): P&I about $1,278, annual debt service about $15,330, cash flow about +$1,296
- Vacancy at 10%: NOI falls to about $15,566, and cash flow at 6.5% is about −$1,500
A one-point rate swing moves this deal from slightly positive to clearly negative. That is the sensitivity row to keep in every version of the sheet. In this example, the negotiating levers are price, rent, and the loan structure, not hope.
Set your decision rule before you look at the results: a minimum cash-on-cash figure, a minimum DSCR, and a reserve target. Numbers written down in advance are harder to rationalize after you have fallen for a property.
When the sheet is ready, send it to Duane Buziak along with the address, your estimated rent, and your financing goals. He will compare conventional and DSCR options across wholesale lenders and tell you where your assumptions diverge from how underwriters count rent. Pre-qualification through Wholesale Mortgage Rates by Duane Buziak does not affect your credit. For borrower education, the Consumer Financial Protection Bureau offers free mortgage resources. Rules and lender guidelines change, so confirm current terms before you commit to a deal.
Your Worksheet Is Built: What to Verify Before You Make an Offer
Before relying on the results, check that every input traces to a source: rent comps, tax records, an insurance quote, a management fee. Recompute the example arithmetic on your own property, and confirm that the DSCR row uses PITIA rather than principal and interest alone. Then take the worksheet to someone who can test it against live lender guidelines.
Your dream home is within reach. Discover how wholesale mortgage rates can put more money back in your pocket while you secure the financing you need. Get your personalized rate estimate today with no credit impact and see exactly what you qualify for with the Mortgage Maestro’s expert guidance. Wholesale Mortgage Rates by Duane Buziak is ready to review your numbers.
One Response