Cash on Cash Return in Excel: The Formula and a Small Model

The cash on cash return formula in Excel is one division: =B26/B27, annual pre-tax cash flow divided by total cash invested, with the answer cell formatted as a percentage. That part takes ten seconds. Building the two numbers above it is the real job, and that is where spreadsheets go wrong. For the arithmetic on its own, with no cells involved, read the cash on cash return formula. This page covers what that one leaves out: a sheet layout you can copy, the PMT function, monthly versus annual handling, and the errors that hide inside working files.

Two blocks: inputs and calculations

Put every number you type in one block and every number Excel computes in another. Nothing you type should sit inside a formula. When 500000 is buried in three places, changing the price means hunting for all three, and you will miss one.

Here is the input block, built on a $500,000 rental that rents for $5,200 a month with $10,000 of rehab, $6,000 of taxes and $2,400 of insurance.

Cell	Input	Value
B2	Purchase price	500000
B3	Down payment	25%
B4	Closing costs	3%
B5	Rehab before first tenant	10000
B6	Monthly rent	5200
B7	Vacancy allowance	5%
B8	Annual property tax	6000
B9	Annual insurance	2400
B10	Maintenance	5%
B11	Capital reserves	5%
B12	Management	8%
B13	Interest rate	7.25%
B14	Loan term in years	30

Two notes on those cells. Use the property tax bill you will pay after closing, since many counties reassess at sale, and the seller’s number can be years stale. The reserves row stands for capital expenditure, the roof and the water heater, which is money you spend in lumps and should hold back every month.

The calculation block

Cell	Line	Formula	Result
B17	Gross scheduled income	=B6*12	62,400
B18	Vacancy loss	=B17*B7	3,120
B19	Effective gross income	=B17-B18	59,280
B20	Taxes and insurance	=B8+B9	8,400
B21	Variable expenses	=B19*(B10+B11+B12)	10,670
B22	Net operating income	=B19-B20-B21	40,210
B23	Loan amount	=B2*(1-B3)	375,000
B24	Monthly payment	=PMT(B13/12,B14*12,-B23)	2,558
B25	Annual debt service	=B24*12	30,698
B26	Annual cash flow	=B22-B25	9,512
B27	Cash invested	=B2B3+B2B4+B5	150,000
B28	Cash on cash return	=B26/B27	6.3%

B21 multiplies the percentage expenses by B19, not B17. Management and maintenance follow collected rent, not the rent roll you hoped for. B22 is net operating income, the line every other real estate metric is built from, and it stops before the loan. B27 adds the down payment, the closing costs and the rehab, because all three left your bank account. B28 returns 0.0634. Format it as a percentage with one decimal and the sheet reads 6.3%.

PMT and the sign problem

=PMT(B13/12, B14*12, -B23) gives 2558.16. The minus in front of the loan amount is there because PMT treats money going out as negative, so without it the cell shows -2558.16 and B25 and B26 both read wrong.

The error to watch for is negating twice, once on the principal and once in front of the function. Two negatives make the payment positive again but with the sign flipped where it matters, so B25 comes back as -30,698, B26 adds the debt service instead of subtracting it, and the sheet reports a return near 47%. A cash flow larger than net operating income is the tell.

For an interest-only loan, skip PMT. The annual cost is =B23*B13, or $27,188 on this loan, and it will rise when the interest-only period ends.

Keep one time unit below the input line

Rent is monthly, taxes and insurance are annual, and PMT returns a monthly figure. Pick annual for everything below the inputs, convert once, and never mix again. Most broken spreadsheets subtract a $6,000 tax bill from one month of rent and end up with a negative return on a property that cash flows fine.

The quieter version of the same mistake is =PMT(B13, B14, -B23) with the annual rate and 30 annual periods. That returns about $30,983 a year against the correct $30,698, a gap of $285 on this loan. It is under 1% here, so it survives review, and it gets worse at higher rates and shorter terms.

Five checks before you trust the output

• A percent typed as a whole number. 25 in a percent-formatted cell is 2500%.
• Multiplying by 100 and formatting as a percentage, which applies the factor twice and shows 630%.
• The double negative on PMT described above.
• Percentage expenses taken off gross scheduled income instead of collected rent.
• Relative references that slide when you copy a column.

For an empty template you plan to reuse, wrap the last line as =IFERROR(B26/B27,"") so the sheet shows a blank rather than a divide-by-zero error before anything is filled in.

Comparing scenarios across columns

The usual next step is columns C and D for 20% down and all cash. Copy the calculation block across and lock the input cells with dollar signs, so =$B$2*(1-C3) reads the price from column B and the down payment from its own column. Without the locks, the copies point at blank cells.

At 20% down on this property, the loan is $400,000, annual debt service runs $32,744, cash flow falls to $7,466, and cash invested drops to $125,000. The return is 6.0%, below the 6.3% at 25% down. Borrowing more lowered it here because the loan costs more per year than the property yields.

The two-cell version

If you already know both numbers, the model is two cells and a division. Type 12000 in B1, 150000 in B2, put =B1/B2 in B3, format it as a percentage, and you get 8%. That is worth keeping on a second tab as a sanity check on the long sheet, and the question of how the inputs are defined matters far more than the division ever will.

Or skip the build

The cash on cash return calculator on the home page runs these same lines and shows every one of them, including the all-cash return next to the financed one. It is faster than a blank workbook when you are screening deals, and the spreadsheet is the better tool once you want scenario columns of your own.