如何在 Excel 或 Google Sheets 計算出租物業最高出價（現金流、cash-on-cash 回報率及 DSCR 實測）

Most rental calculators answer "is this deal good at the asking price?" The more useful question before you make an offer is the reverse: what is the most I can pay and still hit my numbers? If you know you want at least $200 a month of cash flow, an 8% cash-on-cash return and a DSCR of 1.25, there is one highest price that satisfies all three, and it is usually the right anchor for a first offer.

This tutorial builds a one-tab max offer sheet. You type the deal (rent, expenses, loan terms, closing and repair costs) and your three targets. The sheet solves the maximum price for each target in closed form, takes the lowest one, rounds it down to the nearest $500, tells you which target is the bottleneck, and then re-checks the deal at that price. It works the same in Excel, Google Sheets and LibreOffice, using only PMT, MIN, ROUNDDOWN, IF and ISNUMBER.

To test it, we built a workbook with these exact formulas, recalculated it in LibreOffice and compared every cell to the worked examples below and to an independent Python model that finds each maximum price by bisection on the full cash-flow model (no algebra shortcuts), on the examples plus 40 randomized deals including a 0% loan rate, 0% vacancy, no closing or repair costs and deals where no price works. We also checked the results against the max-offer finder in Rental Property Calculator Pro on its three sample deals.

Why price can be solved directly

When you change only the purchase price, the rent and operating expenses don't move (property tax here is a fixed dollar amount you enter; if your county reassesses on sale, put the post-sale tax in). Two things scale with price:

Debt service. The loan is price × (1 − down %), and the monthly payment is proportional to the loan. So yearly debt service = price × k, where k is the debt service per $1 of purchase price.
Cash invested. Down payment plus points are price × (down % + (1 − down %) × points %), plus the fixed closing and repair costs.

Each target then becomes a straight-line inequality in price:

Cash flow: NOI − price × k ≥ 12 × minimum monthly cash flow
DSCR: NOI ÷ (price × k) ≥ minimum DSCR
Cash-on-cash: NOI − price × k ≥ minimum CoC × (price × cash-per-$1 + closing + repairs)

Solve each one for price and the smallest answer is your max offer.

1. Inputs

Column A holds labels, column B the values. The numbers are the Elm St Duplex sample deal from the Rental Deal Analyzer and Rental Property Calculator Pro.

B1 (Asking price):                285000
B2 (Closing costs):               7500
B3 (Upfront repairs):             12000
B4 (Down payment %):              25%
B5 (Interest rate):               6.75%
B6 (Loan term, years):            30
B7 (Points / loan fees %):        1%
B8 (Rent, all units / month):     2900
B9 (Other income / month):        50
B10 (Vacancy & credit loss %):    6%
B11 (Fixed expenses / year):      6340
B12 (Repairs + CapEx % of rent):  12%
B13 (Management % of collected):  8%
B14 (Min monthly cash flow):      200
B15 (Min cash-on-cash):           8%
B16 (Min DSCR):                   1.25

Fixed expenses are everything that doesn't depend on rent: property tax $3,100 + insurance $1,500 + owner-paid utilities $120/month + other $25/month = 3,100 + 1,500 + 145 × 12 = $6,340. Repairs and CapEx are a percentage of the unit rent (not other income); management is a percentage of what you actually collect.

2. NOI

B18 (Gross scheduled income):  =(B8+B9)*12
B19 (Effective gross income):  =B18*(1-B10)
B20 (Operating expenses):      =B11+B12*B8*12+B13*B19
B21 (NOI):                     =B19-B20

For the duplex: gross income $35,400, effective gross income $33,276, operating expenses $13,178.08, NOI $20,097.92. None of these depend on the price.

3. The price-scaled pieces

B22 (Debt service per $1 of price):  =12*PMT(B5/12,B6*12,-(1-B4))
B23 (Cash in per $1 of price):       =B4+(1-B4)*B7

B22 is the yearly principal and interest on the 75% loan behind one dollar of purchase price: about 0.058374. B23 is the down payment plus points per dollar: 0.25 + 0.75 × 0.01 = 0.2575. PMT handles a 0% rate in all three apps (it becomes loan ÷ number of payments), so interest-free seller financing works too. These formulas assume there is a loan; for an all-cash purchase B22 is 0 and the DSCR line divides by zero.

4. Max price for each target, and the max offer

B24 (Max price, cash flow target):    =(B21-12*B14)/B22
B25 (Max price, cash-on-cash target): =(B21-B15*(B2+B3))/(B22+B15*B23)
B26 (Max price, DSCR target):         =B21/(B16*B22)
B27 (Max offer):                      =IF(MIN(B24:B26)<500,"No price works",ROUNDDOWN(MIN(B24:B26)/500,0)*500)
B28 (Binding target):                 =IF(B24=MIN(B24:B26),"Cash flow",IF(B25=MIN(B24:B26),"Cash-on-cash","DSCR"))
B29 (Below asking by):                =IF(ISNUMBER(B27),B1-B27,"")

For the duplex with $200/month, 8% and 1.25:

Target	Max price
$200/month cash flow	$303,182.44
8% cash-on-cash	$234,734.98
1.25 DSCR	$275,437.41

The lowest is cash-on-cash, so the max offer is $234,500, $50,500 below the $285,000 asking price. Rounding down to $500 matters: rounding to the nearest $500 could land a few dollars above the line and miss the target.

"No price works" means even a $500 price misses a target, typically because NOI is below 12 × your minimum monthly cash flow (no amount of negotiating fixes a rent problem).

5. Check the deal at the max offer

Always recompute the deal from scratch at the solved price. If these rows don't meet your targets, a formula above is wrong. (When B27 says "No price works", rows 31–36 show #VALUE!; that's expected.)

B31 (Loan):               =B27*(1-B4)
B32 (Monthly P&I):        =PMT(B5/12,B6*12,-B31)
B33 (Monthly cash flow):  =(B21-12*B32)/12
B34 (Cash invested):      =B27*B4+B2+B3+B31*B7
B35 (Cash-on-cash):       =(B21-12*B32)/B34
B36 (DSCR):               =B21/(12*B32)
B37 (Cash-on-cash at asking): =(B21-12*PMT(B5/12,B6*12,-B1*(1-B4)))/(B1*B4+B2+B3+B1*(1-B4)*B7)

At $234,500: loan $175,875, monthly P&I $1,140.72, monthly cash flow $534.10, cash invested $79,883.75, cash-on-cash 8.02%, DSCR 1.47. All three targets pass, and cash-on-cash is the one sitting right at the line. At the asking price the same deal returns only 3.73% cash-on-cash.

A second case: when DSCR is the bottleneck

Keep the same duplex but change the targets to 5% cash-on-cash (B15) and a 1.30 DSCR (B16), as a lender might require. The cash-on-cash limit rises to $268,396.27 while the DSCR limit drops to $264,843.66, so the binding target becomes DSCR and the max offer is $264,500 ($20,500 below asking). At that price DSCR is 1.30 and monthly cash flow is $388.17.

Common mistakes

Forgetting closing and repair costs in cash-on-cash. They don't scale with price, but they are in the denominator. Leave them out and the max offer comes out too high.
Using the asking-price tax bill. If your area reassesses at sale, the post-sale tax is what you'll pay; enter that in fixed expenses.
Mixing monthly and yearly. B14 is monthly; NOI and debt service are yearly. The formulas multiply by 12 where needed.
Rounding to the nearest $500. Round down, or the offer can miss a target by a few dollars.
Treating the max offer as the price to offer. It is your walk-away ceiling. Offer below it and leave room for inspection surprises.