Cellwise Try it free

Excel for your field

Excel for landlords and real-estate investors

For small landlords and people sizing up their first rental, who want the numbers before they sign anything. Excel turns a listing into monthly cash flow, cap rate and cash-on-cash return, works out the mortgage payment with PMT, and keeps a rent roll and expense log that show which property is pulling its weight. The examples use a small five-property portfolio. They are calculations, not investment or legal advice.

Spreadsheet calculations only, not investment, legal or lending advice. Check real quotes and local rules before you buy.

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

ABCDEFG
1PropertyPriceRent / moVacancyOp. expenses / moMortgage / moCash invested
2Maple St duplex31000029000.05980161074000
3Oak Ave condo18900016500.0461094542000
4Elm Ct house25500021500.06720129058000
5Pine Rd triplex42000040500.081460219096000
6Birch Ln townhouse22800019000.05690118551000
7
8Next purchase
9Price285000
10Down payment %0.2
11Interest rate0.0675
12Term (years)30

What Excel does for real estate

Work out monthly cash flow

Cash flow is what is left each month after every bill, including the mortgage. Start with rent, knock off a vacancy allowance (no unit is rented 12 months of every year), then subtract operating expenses and the mortgage payment. Writing it as C2*(1-D2) keeps the vacancy rate in its own cell, so you can test 5% against 10% without editing the formula.

Fill the formula down to compare properties. A property with thin or negative cash flow can still be a choice someone makes for other reasons, but you should know the number before you decide.

=C2*(1-D2)-E2-F2

Result: 165

$165 a month for the Maple St duplex: $2,755 of effective rent minus $980 of expenses and the $1,610 mortgage.

Calculate the cap rate

Cap rate compares a property's yearly net operating income (NOI: effective rent minus operating expenses, before the mortgage) with its price. Because it leaves the loan out, it lets you compare properties as if each were bought with cash, however they are financed.

Cap rates that look normal vary a lot by city and property type, so compare a property with similar ones nearby rather than with a single target number.

=(C2*(1-D2)-E2)*12/B2

Result: 0.068709677419 shows as 6.87% with Percent format

About 6.87% for the duplex: $21,300 of yearly NOI on a $310,000 price.

Calculate cash-on-cash return

Cash-on-cash return answers a different question: what does the cash you actually put in (down payment, closing costs, initial repairs) earn each year? It is yearly cash flow, after the mortgage, divided by cash invested.

This is where leverage shows up. The same property can have a decent cap rate and a small cash-on-cash return when the mortgage takes most of the NOI, as the duplex does here.

=(C2*(1-D2)-E2-F2)*12/G2

Result: 0.026756756757 shows as 2.68% with Percent format

About 2.68% for the duplex: $1,980 a year of cash flow on $74,000 invested.

Estimate the mortgage payment with PMT

PMT returns the fixed monthly payment on a loan. Give it the monthly rate (yearly rate / 12), the number of payments (years x 12) and the loan amount with a minus sign so the answer comes out positive. The loan here is the price minus the down payment.

PMT covers principal and interest only. Property tax and insurance go in operating expenses, even when your lender collects them in an escrow account with the payment.

=PMT(B11/12,B12*12,-B9*(1-B10))

Result: 1478.8036602 shows as $1,478.80 with Currency format

About $1,478.80 a month on a $228,000 loan (20% down on $285,000) at 6.75% over 30 years.

Put a dollar figure on vacancy

A vacancy percentage is easy to wave away; a dollar figure is not. SUMPRODUCT multiplies each property's rent by its vacancy allowance and adds the results, giving the rent you expect to lose across the portfolio in a month. Multiply by 12 for the year.

Use this when you set aside reserves: the money for empty months has to come from somewhere.

=SUMPRODUCT(C2:C6,D2:D6)*12

Result: 9108 shows as $9,108 with Currency format

About $9,108 a year of rent lost to vacancy across the five properties, or $759 a month.

Find the property earning the least

With several properties it is easy to miss the one quietly costing you money. Build the cash flow for every row inside MIN, then MATCH finds which row has that value and INDEX returns the property's name. No helper column needed, although a Cash flow column is easier to audit if you share the file.

The weakest property is the first one to look at for a rent review, an expense check or a refinance.

=INDEX(A2:A6,MATCH(MIN(C2:C6*(1-D2:D6)-E2:E6-F2:F6),C2:C6*(1-D2:D6)-E2:E6-F2:F6,0))

Result: Birch Ln townhouse

Birch Ln townhouse: $1,805 of effective rent minus $690 of expenses and a $1,185 mortgage leaves it about $70 a month short.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

  1. 1What is the effective rent for the Oak Ave condo after its vacancy allowance?

    Show answer

    =C3*(1-D3) = 1584. $1,650 times 0.96 is $1,584, the rent you can count on in an average month.

  2. 2How much rent would all five properties bring in over a year at full occupancy?

    Show answer

    =SUM(C2:C6)*12 = 151800. Monthly rents add to $12,650; times 12 is $151,800.

  3. 3What is the monthly cash flow of the Elm Ct house?

    Show answer

    =C4*(1-D4)-E4-F4 = 11. $2,021 effective rent minus $720 and $1,290 leaves $11 a month, barely break-even.

  4. 4What is the cap rate of the Pine Rd triplex?

    Show answer

    =(C5*(1-D5)-E5)*12/B5 = 0.064742857143. NOI is $2,266 a month, $27,192 a year, on $420,000: about 6.47%.

  5. 5How much cash is the down payment on the next purchase?

    Show answer

    =B9*B10 = 57000. 20% of $285,000 is $57,000, before closing costs.

  6. 6What is the rent-to-price ratio for the Birch Ln townhouse (the number the "1% rule" looks at)?

    Show answer

    =C6/B6 = 0.0083333333333. $1,900 / $228,000 is about 0.83%, under the 1% that rough screen looks for.

  7. 7What is the monthly mortgage payment on the next purchase?

    Show answer

    =PMT(B11/12,B12*12,-B9*(1-B10)) = 1478.8036602. PMT spreads the $228,000 loan over 360 monthly payments at 0.5625% a month: about $1,478.80.

  8. 8How many of the five properties have positive monthly cash flow?

    Show answer

    =SUMPRODUCT(--(C2:C6*(1-D2:D6)-E2:E6-F2:F6>0)) = 4. Four of the five clear zero. Birch Ln loses about $70 a month, and Elm Ct clears only $11, which is why the count alone is not enough.

Free templates for real estate

Guided project: Rental expenses by property

Turn a year of landlord expenses into a per-property report with net income, expense ratio and the property to look at first.

5 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

What Excel formulas do landlords use most?

PMT for mortgage payments, simple arithmetic for cash flow and NOI, SUMIF and SUMIFS to total rent and expenses by property or category, COUNTIF for occupancy, and COUNTIFS with dates to find leases ending soon.

How do I calculate cap rate in Excel?

Divide yearly net operating income by the price: =(rent*(1-vacancy)-monthly expenses)*12/price, and format it as a percentage. Leave the mortgage out; that is what makes cap rate comparable between properties.

Does NOI include the mortgage payment?

No. Net operating income is income minus operating expenses such as tax, insurance, repairs and management. The mortgage comes off afterwards to give cash flow.

Should I put the property tax in the mortgage payment?

Keep them separate in the spreadsheet even if your lender collects tax and insurance through escrow. PMT gives principal and interest only, so tax and insurance belong in operating expenses, where they also count toward NOI.

Excel for other fields

Updated