How Much Can I Pay for a Rental Property? The Max Offer Price Formula in Excel

Most rental calculators run forward. You type in the asking price and they tell you the cash flow. That answers "does this deal work at this price" but not the question you need before you make an offer. That question is "what is the most I can pay and still hit my numbers."

You can solve that directly. Each return target turns into one formula for price. You run all three and the lowest one is your ceiling. This guide works through it with one duplex so every number can be checked.

The deal

The example is the same duplex used in our rental property cash flow guide. The seller is asking $350,000.

At $350,000 the deal produces NOI of $22,644. The payment is $1,746.42 a month, or $20,957.03 a year. That leaves cash flow of $1,686.97 and a DSCR of 1.08.

Now assume your targets are a DSCR of at least 1.25, cash-on-cash of at least 6% and a cap rate of at least 6.5%. The question is how far the price has to come down.

Step 1: split NOI into the part that moves with price

Most expenses do not care what you paid. Rent, vacancy, management, maintenance, capex and insurance are the same at $300,000 or $350,000. Property tax is the exception because it is assessed on the price.

So NOI is a straight line in price:

NOI(P) = 26,844 − 0.012 × P

The 26,844 is EGI minus every expense except tax. The 0.012 is the tax rate. This one line is what makes the whole problem solvable without goal seek.

Step 2: turn the loan payment into a rate per dollar

The annual payment on a loan is the loan amount times a fixed factor. That factor is called the loan constant. At 7% over 30 years it is:

=PMT(7%/12, 360, -1) * 12 which returns 0.079836

Every dollar you borrow costs about 7.98 cents a year. With 25% down you borrow 75% of the price, so annual debt service is:

DS(P) = 0.75 × 0.079836 × P

Debt service is also a straight line in price. Now each target can be solved for P.

Step 3: solve each target for price

DSCR target. DSCR is NOI over debt service. Set it equal to 1.25 and solve:

P = 26,844 / (0.012 + 1.25 × 0.75 × 0.079836)

That gives $309,097. At that price NOI is $23,134.84 and debt service is $18,507.87. The ratio is exactly 1.25.

Cash-on-cash target. Cash invested is the 25% down payment plus 2% closing, so 27% of the price. Cash flow is NOI minus debt service. Set cash flow equal to 6% of cash invested and solve:

P = 26,844 / (0.012 + 0.75 × 0.079836 + 0.06 × 0.27)

That gives $304,778. At that price cash invested is $82,290 and cash flow is $4,937.40, which is a 6.00% return.

Cap rate target. Cap rate is NOI over price with no financing involved:

P = 26,844 / (0.012 + 0.065)

That gives $348,623.

Step 4: the lowest price wins

| Target | Max price | |---|---| | Cap rate 6.5% | $348,623 | | DSCR 1.25 | $309,097 | | Cash-on-cash 6% | $304,778 |

Your ceiling is $304,778 because it is the only price that passes all three. That is $45,222 under asking, about 13%.

The table also tells you something the forward calculator hides. The cap rate never binds here. Its ceiling sits only $1,377 under asking, so this deal is close to fine as a property and the problem is the financing. At 7% the loan eats almost all of the NOI. A seller who points to the cap rate is telling the truth. It just is not the number that limits you.

Note that the DSCR price does not clear your cash target. At $309,097 cash-on-cash is only 5.54%. You need to run all three every time because you cannot tell in advance which one binds.

Step 5: see how the ceiling moves with the rate

The same three formulas at different rates, with nothing else changed:

| Rate | DSCR 1.25 | CoC 6% | Cap 6.5% | Max offer | |---|---|---|---|---| | 6.0% | $337,875 | $326,730 | $348,623 | $326,730 | | 6.5% | $323,003 | $315,492 | $348,623 | $315,492 | | 7.0% | $309,097 | $304,778 | $348,623 | $304,778 | | 7.5% | $296,090 | $294,570 | $348,623 | $294,570 | | 8.0% | $283,918 | $284,851 | $348,623 | $283,918 |

Two things stand out. Each half point of rate moves the ceiling by roughly $10,000 to $11,000. And at 8% the binding target flips from cash-on-cash to DSCR. That is why one target is not enough. Which one limits you depends on the rate you can actually get.

The lender's version of the same question

A lender asks the question from the other side. Given this NOI, what is the largest loan that still covers 1.25x? Excel answers that with PV:

=PV(7%/12, 360, -NOI/1.25/12)

At the asking price NOI is $22,644. The maximum loan is $226,904.30, which is 64.8% of $350,000. To buy at asking you would need $123,095.70 down instead of $87,500. That is the same constraint as Step 3. You either pay less or you put in more cash.

Bonus: break-even occupancy

One more number belongs next to the max offer. It is the share of rent you need to collect just to cover costs. Management, maintenance and capex scale with collected rent, which is 18% of it here. Tax, insurance and the loan do not.

Break-even occupancy = (tax + insurance + debt service) / (gross rent × (1 − 0.18))

At asking that is (4,200 + 1,200 + 20,957.03) / 29,520 = 89.3%. An empty unit costs $1,230 a month after the variable costs it saves. So one unit sitting empty for about six weeks wipes out the year's $1,686.97 of cash flow. At the $304,778 ceiling break-even occupancy drops to 78.3% because both the loan and the tax shrink.

Setting it up in Excel

Put each input in its own cell: gross rent, vacancy, each expense percent, insurance, tax rate, down payment percent, closing percent, rate and term. Then add three helper cells.

The three max price cells are then one line each:

Finish with =MIN() over the three. Now changing the rate or the rent updates your ceiling at once.

Where this breaks

Written with AI assistance and checked by recalculation. For education and planning only, not financial advice.

If you want the forward version with the PASS, CAUTION or FAIL scorecard and the 10-year IRR already built, see the Numbersmith rental property analyzer.