Home › Templates › Rental property analysis spreadsheet

Rental Property Analysis Spreadsheet

A free rental property analysis spreadsheet for Excel and Google Sheets: type the price, loan, rent and expenses once, and it works out NOI, monthly and annual cash flow, cap rate, cash-on-cash return, DSCR, break-even occupancy, GRM and the 1% rule check, then projects 30 years of cash flow, loan balance and equity with an IRR for the sale year you pick. A second part logs actual income and expenses by property in Schedule E categories.

Download Excel template (.xlsx) 172 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 2 Nov 2026

Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-on, no password-locked cells.

Preview: the Example Deal and Example Projection tabs (fictional duplex: $240,000 price, 25% down, 7% 30-year loan, $2,400 rent). Growth rates of 3% a year are example assumptions, not forecasts.
$79.46cash flow per month ($953.47 a year)
6.39% · 1.25%cap rate · cash-on-cash return
1.07 · 90.8%DSCR · break-even occupancy
10.69%IRR if sold in year 10 (before tax)
Example Deal (fictional duplex)Per monthPer year
Gross scheduled income (rent $2,400 + other $50)$2,450.00$29,400.00
Vacancy and credit loss (6%)-$147.00-$1,764.00
Effective gross income$2,303.00$27,636.00
Property taxes$300.00$3,600.00
Insurance$125.00$1,500.00
Owner-paid utilities$120.00$1,440.00
Maintenance (6% of rent)$144.00$1,728.00
CapEx reserve (5% of rent)$120.00$1,440.00
Management (8% of rent)$192.00$2,304.00
Other expenses$25.00$300.00
Total operating expenses$1,026.00$12,312.00
Net operating income (NOI)$1,277.00$15,324.00
Debt service (P&I)$1,197.54$14,370.53
Cash flow before tax$79.46$953.47
Example Projection, 30 years: property value grows from $255,000 to $618,952 at 3% a year, the loan balance falls from $180,000 to $0, and equity rises from $75,000 to $618,952. Sale year 10 is marked: equity $188,236.$0k$100k$200k$300k$400k$500k$600k$700k051015202530Yearsale year 10ValueLoan balanceEquity

Chart drawn from the Example Projection values in the same file. The workbook has native Excel charts of value, loan balance, equity and annual cash flow.

What's inside the spreadsheet

TabWhat you typeWhat it calculates
Start hereNothingSteps, how the maths works, tax notes, limits, disclaimer
DealPurchase price, closing costs, rehab, value after rehab (optional), down payment %, rate, term; rent, other income, vacancy %; taxes, insurance, HOA, utilities, maintenance %, CapEx %, management %, other; rent, expense and value growth; sale year; selling costs %Loan amount, cash invested, monthly P&I (PMT), a monthly and annual income statement down to NOI and cash flow; cap rate (on price and on all-in cost), cash-on-cash, DSCR, expense ratio, break-even occupancy, GRM, 1% rule ratio; sale price, selling costs, loan payoff, net proceeds, total profit, equity multiple, IRR; an input check
ProjectionNothingYears 0–30: gross and effective income, operating expenses, NOI, debt service, cash flow, cash-on-cash, DSCR, value, loan balance, equity, cumulative cash flow, net proceeds if sold that year, the IRR cash flow column and IRR(); two native charts
LogUp to 600 rows: date, property, category (dropdown), amount, notesGroup (income, operating expense, interest, principal, capital improvement), Schedule E line, year, month and a row check
SummaryOptional year and property filterEvery category by month for the year, group totals (NOI, cash flow before principal, net cash flow, Schedule E-style expenses), a table by property and a native chart
ListsYour property names (optional)Feeds the property and category dropdowns
Example Deal, Example Projection, Example Log, Example SummaryAlready filledA fictional duplex analysis and 107 fictional Log rows for two made-up properties (Jan–Oct 2026)

The loan payment here is one number a month. To see every payment split into interest and principal, use the loan amortization schedule; to track the property inside your whole balance sheet, use the net worth tracker.

How to use it

  1. Open Deal and type the purchase price, closing costs and rehab budget. The CFPB lists common closing fees such as "Appraisal fees", "Title insurance", "Government taxes" and "Prepaid expenses such as property taxes, homeowners insurance, and interest until your first payment is due" (Ask CFPB); take the real figures from your lender's Loan Estimate.
  2. Type the down payment %, interest rate and term. 100% down means an all-cash purchase: debt service becomes $0 and DSCR shows "no loan".
  3. Type the monthly rent for all units, any other income, and a vacancy and credit loss %. Use your own local figures; the template has no built-in market data.
  4. Type each operating expense. Taxes, insurance and other costs are per year; HOA and utilities per month; maintenance, CapEx and management as a % of rent.
  5. Check F4 says OK, then read the results. Change one input at a time (rent, rate, price) to see which one moves cash flow most.
  6. Set rent, expense and value growth plus a sale year and selling costs %. Open Projection for the year-by-year view and the IRR.
  7. After you buy, add the property name on Lists, log every payment in and out on Log, and read Summary for monthly and yearly totals by Schedule E category.

The formulas, explained

Exact formulas from the file. On Deal, column B holds the inputs (B6 price, B7 closing costs, B8 rehab, B9 value after rehab, B10 down %, B11 rate, B12 term, B15 rent, B16 other income, B17 vacancy, B20–B27 expenses, B30–B34 growth and sale) and column F the results. N() turns a blank cell into 0, so optional inputs can stay empty.

FieldFormula
Input check (Deal!F4)=IF(N($B$6)<=0,"Add purchase price",IF(N($B$15)<=0,"Add monthly rent",IF(OR(N($B$10)<0,N($B$10)>1),"Down payment must be 0%-100%",IF(N($B$11)<0,"Rate cannot be negative",IF(AND(N($B$10)<1,OR(N($B$12)<1,N($B$12)>40,N($B$12)<>INT(N($B$12)))),"Loan term: whole years 1-40",IF(OR(N($B$33)<1,N($B$33)>30,N($B$33)<>INT(N($B$33))),"Sale year: whole number 1-30",IF(N($B$17)>=1,"Vacancy must be below 100%","OK")))))))
Loan amount=IF($F$4<>"OK","",$B$6-F8)
Total cash invested=IF($F$4<>"OK","",F8+N($B$7)+N($B$8))
Monthly P&I=IF($F$4<>"OK","",IF(F9<=0,0,IF(N($B$11)=0,F9/(N($B$12)*12),PMT(N($B$11)/12,N($B$12)*12,-F9))))
Effective gross income (per month)=IF($F$4<>"OK","",F15+F16)
Total operating expenses (per month)=IF($F$4<>"OK","",SUM(F18:F25))
NOI (per month)=IF($F$4<>"OK","",F17-F26)
Cash flow (per month)=IF($F$4<>"OK","",F27-F28)
Cap rate=IF($F$4<>"OK","",G27/$B$6)
Cash-on-cash return=IF($F$4<>"OK","",IF(F10<=0,"n/a",G29/F10))
DSCR=IF($F$4<>"OK","",IF(G28<=0,"no loan",G27/G28))
Break-even occupancy=IF($F$4<>"OK","",(G26+G28)/G15)
Gross rent multiplier=IF($F$4<>"OK","",$B$6/G15)
1% rule ratio=IF($F$4<>"OK","",$B$15/($B$6+N($B$8)))
Projection: gross income, year 1 (B10)=IF(Deal!$F$4<>"OK","",12*(N(Deal!$B$15)+N(Deal!$B$16))*(1+N(Deal!$B$30))^(A10-1))
Projection: operating expenses (E10)=IF(Deal!$F$4<>"OK","",(N(Deal!$B$20)+N(Deal!$B$21)+12*N(Deal!$B$22)+12*N(Deal!$B$23)+N(Deal!$B$27))*(1+N(Deal!$B$31))^(A10-1)+(12*N(Deal!$B$15)*(N(Deal!$B$24)+N(Deal!$B$25)+N(Deal!$B$26)))*(1+N(Deal!$B$30))^(A10-1))
Projection: debt service (G10)=IF(Deal!$F$4<>"OK","",IF(Deal!$F$9<=0,0,Deal!$F$11*MAX(0,MIN(12,12*N(Deal!$B$12)-12*(A10-1)))))
Projection: loan balance (L10)=IF(Deal!$F$4<>"OK","",IF(Deal!$F$9<=0,0,IF(N(Deal!$B$11)=0,MAX(0,Deal!$F$9-Deal!$F$11*MIN(12*A10,12*N(Deal!$B$12))),MAX(0,FV(N(Deal!$B$11)/12,MIN(12*A10,12*N(Deal!$B$12)),Deal!$F$11,-Deal!$F$9)))))
Projection: net proceeds if sold (O10)=IF(Deal!$F$4<>"OK","",K10*(1-N(Deal!$B$34))-L10)
Projection: IRR cash flow (P10)=IF(Deal!$F$4<>"OK","",IF(A10<Deal!$B$33,H10,IF(A10=Deal!$B$33,H10+O10,0)))
Projection: IRR (B5)=IF(Deal!$F$4<>"OK","",IFERROR(IRR(P9:P39),"n/a"))
Log: group from category (F6)=IF(OR(C6="",COUNTIF(Lists!$A$3:$A$20,C6)=0),"",INDEX(Lists!$B$3:$B$20,MATCH(C6,Lists!$A$3:$A$20,0)))
Summary: one category, January (C9)=IF($B$4="",SUMIFS(Log!$D$6:$D$605,Log!$C$6:$C$605,$A9,Log!$H$6:$H$605,$B$5,Log!$I$6:$I$605,1),SUMIFS(Log!$D$6:$D$605,Log!$C$6:$C$605,$A9,Log!$H$6:$H$605,$B$5,Log!$I$6:$I$605,1,Log!$B$6:$B$605,$B$4))

PMT. Microsoft's PMT help page says to "Make sure that you are consistent about the units you use for specifying rate and nper": for a monthly payment the template divides the annual rate by 12 and multiplies the years by 12. A 0% rate uses loan ÷ months instead.

Loan balance. FV(rate/12, months paid, P&I, -loan) gives the balance left after that many payments, the same figure an amortization table shows at that month.

IRR. Column P on Projection holds the cash flows: year 0 = minus the cash invested; each year up to the sale = that year's cash flow; the sale year adds value × (1 − selling costs %) − loan balance; later years are 0, which does not change the result. Microsoft's IRR help page notes the cash flows "must occur at regular intervals, such as monthly or annually" and that Excel "uses an iterative technique for calculating IRR"; if it cannot find an answer the cell shows n/a.

Growth. Year-n rent = year-1 rent × (1 + rent growth)n−1. Taxes, insurance, HOA, utilities and other costs grow at the expense rate; maintenance, CapEx and management stay a % of rent, so they grow with rent. Value at the end of year n = starting value × (1 + value growth)n.

Worked example from the sample data

The Example Deal is a made-up duplex. Every input is fictional and chosen to show the formulas, not to describe a real market.

Purchase and loan

Price $240,000; 25% down = $60,000; loan $180,000 at 7% for 30 years. Closing costs $6,000 and rehab $10,000 bring the cash invested to $76,000. PMT(0.07/12, 360, -180000) = $1,197.54 a month.

Income, NOI and cash flow

Rent $2,400 plus $50 laundry = $2,450 gross; 6% vacancy ($147) leaves $2,303. Operating expenses are $1,026 a month: taxes $300, insurance $125, utilities $120, maintenance $144, CapEx $120, management $192 and other $25. NOI = $2,303 − $1,026 = $1,277 a month, or $15,324 a year. After the $1,197.54 loan payment, cash flow is $79.46 a month ($953.47 a year).

Ratios

RatioCalculationResult
Cap rate$15,324 ÷ $240,0006.39%
Cap rate on all-in cost$15,324 ÷ ($240,000 + $6,000 + $10,000)5.99%
Cash-on-cash return$953.47 ÷ $76,0001.25%
DSCR$15,324 ÷ $14,370.531.07
Break-even occupancy($1,026 + $1,197.54) ÷ $2,45090.76%
Gross rent multiplier$240,000 ÷ $29,4008.16
1% rule ratio$2,400 ÷ ($240,000 + $10,000)0.96%

The deal covers its costs, but only just: cash-on-cash of 1.25% and a DSCR of 1.07 leave little room for a bad month. Break-even occupancy of 90.8% means the property needs to collect about 91% of its scheduled income to cover expenses and the loan. At 0.96% it is just below the 1% rule of thumb.

30-year projection and a sale in year 10

With 3% rent, expense and value growth (example assumptions) and a starting value of $255,000 after rehab:

YearEffective gross incomeOperating expensesNOIDebt serviceCash flowValueLoan balanceEquity
1$27,636$12,312$15,324$14,371$953$262,650$178,172$84,478
2$28,465$12,681$15,784$14,371$1,413$270,530$176,211$94,319
3$29,319$13,062$16,257$14,371$1,887$278,645$174,109$104,537
5$31,105$13,857$17,247$14,371$2,877$295,615$169,437$126,178
10$36,059$16,064$19,994$14,371$5,624$342,699$154,462$188,236
20$48,460$21,589$26,871$14,371$12,500$460,558$103,140$357,418
30$65,126$29,014$36,112$14,371$21,741$618,952$0$618,952

In year 10 the projected value is $342,698.68. Selling costs of 7% ($23,988.91) and the loan payoff of $154,462.29 leave net proceeds of $164,247.48. Add $31,967.15 of cash flow over ten years and the $76,000 invested comes back 2.58 times: a profit of $120,214.63 and an IRR of 10.69% a year, before income tax. Most of that comes from the assumed 3% value growth and the loan paydown, not from cash flow; set value growth to 0% to see how much.

Logging actuals by Schedule E category

After you buy, the Log tab records what really happens. Each category maps to a line of Schedule E (Form 1040), the form US owners use to report rental income and expenses. IRS Publication 527, Residential Rental Property explains what counts:

The Schedule E instructions draw the repair line clearly (line 14): "You can deduct the amounts paid for repairs and maintenance. However, you cannot deduct the cost of improvements." Their examples: repairs are "fixing a broken lock or painting a room"; improvements include "adding substantial insulation or replacing an entire HVAC system." Improvements are depreciated instead; Publication 527 says residential rental property is depreciated "over 27.5 years" under GDS. The template does not calculate depreciation.

Log categoryGroupSchedule E lineExample, Jan–Oct 2026
RentIncome3$36,300.00
Other rental incomeIncome3$500.00
AdvertisingOperating expense5$45.00
Auto and travelOperating expense6$38.50
Cleaning and maintenanceOperating expense7$360.00
CommissionsOperating expense8$0.00
InsuranceOperating expense9$2,140.00
Legal and other professional feesOperating expense10$500.00
Management feesOperating expense11$1,824.00
Mortgage interestInterest12$10,460.66
Other interestInterest13$0.00
RepairsOperating expense14$950.00
SuppliesOperating expense15$62.40
TaxesOperating expense16$5,900.00
UtilitiesOperating expense17$1,220.00
Other (HOA, pest control, etc.)Operating expense19$2,850.00
Loan principalPrincipalnot deductible$1,514.74
Capital improvementCapital improvementcapitalize (depreciate)$4,200.00

HOA dues are not a named Schedule E line, so the template puts them under Other (line 19, which the instructions describe as "any ordinary and necessary expenses not listed on lines 5 through 18"). Confirm your own categories with a tax professional.

Example Summary, January to October 2026

PropertyRowsIncomeOperating expensesNOIInterestPrincipal + capitalNet cash flow
Maple St duplex80$23,300.00$9,474.90$13,825.10$10,460.66$5,714.74-$2,350.30
Oak Ave condo27$13,500.00$6,415.00$7,085.00$0.00$0.00$7,085.00
Total107$36,800.00$15,889.90$20,910.10$10,460.66$5,714.74$4,734.70

The made-up duplex shows $13,825.10 of NOI but -$2,350.30 of net cash flow, because the $4,200 water heater and venting job (a capital improvement) and $1,514.74 of loan principal are real cash out but not operating expenses. Unit B sat empty in June, so rent that month was $2,550 instead of $3,750 across both properties. The condo has no loan, so its NOI and net cash flow are the same.

Rules of thumb, and why they are only a first filter

The full income statement, DSCR and break-even occupancy tell you more, and the projection shows how much of a projected return depends on growth you assumed.

Common mistakes

Limits

FAQ

How do you analyze a rental property in a spreadsheet?

Start with the purchase: price, closing costs, repairs and the loan. Add the rent, a vacancy allowance and every operating expense. The sheet then works out net operating income (income after operating expenses), the loan payment with PMT, monthly cash flow, cap rate, cash-on-cash return and DSCR. A multi-year projection adds rent, expense and value growth so you can see equity and an IRR for a chosen sale year. This template does all of that from one input tab.

How do you calculate cash flow on a rental property?

Cash flow = rent and other income, minus vacancy, minus operating expenses (taxes, insurance, HOA, utilities, maintenance, CapEx reserve, management, other), minus the loan's principal and interest. In the fictional example: $2,303.00 effective income - $1,026.00 expenses - $1,197.54 P&I = $79.46 a month, or $953.47 a year, before income tax.

What is the 1% rule for rental property?

It is a rule of thumb, not a law or an official standard: monthly rent should be at least 1% of the purchase price (this template uses price plus rehab). It is a quick filter only, because it ignores taxes, insurance, interest rates and every other cost. The fictional duplex comes in at 0.96%, just below it, yet the full analysis shows positive cash flow; a property can also pass the 1% test and still lose money.

What is the difference between cap rate and cash-on-cash return?

Cap rate = annual NOI / purchase price. It ignores the loan, so it compares properties as if bought with cash. Cash-on-cash return = annual cash flow after the loan payment / cash you put in (down payment, closing costs, rehab). In the example, the cap rate is 6.39% but cash-on-cash is 1.25%, because the 7% loan payment takes most of the NOI.

What is DSCR on a rental property?

The debt service coverage ratio is annual NOI divided by annual loan payments. Above 1.00, the property's income covers the loan; below 1.00, it does not. The example's DSCR is 1.07. Lenders set their own minimums, so ask yours what it uses.

Which rental expenses go on Schedule E?

Schedule E (Form 1040) lists advertising, auto and travel, cleaning and maintenance, commissions, insurance, legal and other professional fees, management fees, mortgage interest, other interest, repairs, supplies, taxes, utilities, depreciation and other (lines 5 to 19). Loan principal is not on the list, and improvements are depreciated rather than deducted. The Log tab uses these categories; IRS Publication 527 explains each one.

Related tools and templates

Educational template only, not financial, tax, legal or investment advice. Example data is fictional: the properties, prices, rents, expenses, growth rates and log entries are made up to show the formulas and are not a forecast or a recommendation. Results are before income tax. IRS, CFPB and Microsoft material was retrieved on 2 November 2026.