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.
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.
| Example Deal (fictional duplex) | Per month | Per 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 |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, how the maths works, tax notes, limits, disclaimer |
| Deal | Purchase 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 |
| Projection | Nothing | Years 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 |
| Log | Up to 600 rows: date, property, category (dropdown), amount, notes | Group (income, operating expense, interest, principal, capital improvement), Schedule E line, year, month and a row check |
| Summary | Optional year and property filter | Every 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 |
| Lists | Your property names (optional) | Feeds the property and category dropdowns |
| Example Deal, Example Projection, Example Log, Example Summary | Already filled | A 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
- 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.
- Type the down payment %, interest rate and term. 100% down means an all-cash purchase: debt service becomes $0 and DSCR shows "no loan".
- 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.
- 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.
- 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.
- Set rent, expense and value growth plus a sale year and selling costs %. Open Projection for the year-by-year view and the IRR.
- 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.
| Field | Formula |
|---|---|
| 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
| Ratio | Calculation | Result |
|---|---|---|
| Cap rate | $15,324 ÷ $240,000 | 6.39% |
| Cap rate on all-in cost | $15,324 ÷ ($240,000 + $6,000 + $10,000) | 5.99% |
| Cash-on-cash return | $953.47 ÷ $76,000 | 1.25% |
| DSCR | $15,324 ÷ $14,370.53 | 1.07 |
| Break-even occupancy | ($1,026 + $1,197.54) ÷ $2,450 | 90.76% |
| Gross rent multiplier | $240,000 ÷ $29,400 | 8.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:
| Year | Effective gross income | Operating expenses | NOI | Debt service | Cash flow | Value | Loan balance | Equity |
|---|---|---|---|---|---|---|---|---|
| 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:
- "Rental income is any payment you receive for the use or occupation of property. It isn’t limited to amounts you receive as normal rental payments."
- "Include advance rent in your rental income in the year you receive it regardless of the period covered or the method of accounting you use."
- Security deposits: "Don’t include a security deposit in your income when you receive it if you plan to return it to your tenant at the end of the lease." If you keep part of it, "include the amount you keep in your income in that year."
- "In most cases, the expenses of renting your property, such as maintenance, insurance, taxes, and interest, can be deducted from your rental income."
- "You can’t simply deduct your mortgage or principal payments, or the cost of furniture, fixtures, and equipment, as an expense." That is why the Log keeps Loan principal and Capital improvement in their own groups.
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 category | Group | Schedule E line | Example, Jan–Oct 2026 |
|---|---|---|---|
| Rent | Income | 3 | $36,300.00 |
| Other rental income | Income | 3 | $500.00 |
| Advertising | Operating expense | 5 | $45.00 |
| Auto and travel | Operating expense | 6 | $38.50 |
| Cleaning and maintenance | Operating expense | 7 | $360.00 |
| Commissions | Operating expense | 8 | $0.00 |
| Insurance | Operating expense | 9 | $2,140.00 |
| Legal and other professional fees | Operating expense | 10 | $500.00 |
| Management fees | Operating expense | 11 | $1,824.00 |
| Mortgage interest | Interest | 12 | $10,460.66 |
| Other interest | Interest | 13 | $0.00 |
| Repairs | Operating expense | 14 | $950.00 |
| Supplies | Operating expense | 15 | $62.40 |
| Taxes | Operating expense | 16 | $5,900.00 |
| Utilities | Operating expense | 17 | $1,220.00 |
| Other (HOA, pest control, etc.) | Operating expense | 19 | $2,850.00 |
| Loan principal | Principal | not deductible | $1,514.74 |
| Capital improvement | Capital improvement | capitalize (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
| Property | Rows | Income | Operating expenses | NOI | Interest | Principal + capital | Net cash flow |
|---|---|---|---|---|---|---|---|
| Maple St duplex | 80 | $23,300.00 | $9,474.90 | $13,825.10 | $10,460.66 | $5,714.74 | -$2,350.30 |
| Oak Ave condo | 27 | $13,500.00 | $6,415.00 | $7,085.00 | $0.00 | $0.00 | $7,085.00 |
| Total | 107 | $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
- 1% rule: monthly rent ≥ 1% of price (plus rehab here). It ignores taxes, insurance, financing and repairs. The sheet labels it a rule of thumb and shows the ratio, not a verdict.
- Cap rate: useful to compare properties without financing; it says nothing about your loan or your cash return.
- GRM: price ÷ gross income. Fast, but blind to expenses: two buildings with the same GRM can have very different NOI.
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
- Leaving out vacancy, CapEx or management. Even if you self-manage and the unit is full today, a 0% line makes the cash flow look better than it will be.
- Using the cap rate as your return. With a loan, cash-on-cash and IRR matter more; in the example, a 6.39% cap rate becomes a 1.25% cash-on-cash return.
- Counting loan principal as an expense in NOI. NOI stops before the loan; principal is part of debt service and, for tax, not deductible.
- Booking an improvement as a repair. A new roof or HVAC system is a capital improvement; log it as one.
- Trusting the IRR without testing growth. Set value growth to 0% and rent growth to 0% to see the downside.
- Typing 6 instead of 6%. Percentage cells expect 6% or 0.06; the input check and validation flag values over 100%.
Limits
- Fixed-rate, fully amortizing loan with monthly payments; no interest-only, ARM, balloon or second loan.
- Before income tax: no depreciation, passive loss rules, capital gains or depreciation recapture.
- Growth rates are constant every year and are your assumptions. The example rates are not market data or forecasts.
- Maintenance, CapEx and management are a % of scheduled rent; some managers charge on collected rent instead.
- One deal per Deal tab (copy the tab to compare deals). Log: 600 rows, one currency, no bank connection.
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
- Loan amortization scheduleEvery payment: interest vs principal
- Net worth trackerProperty equity next to other assets
- Investment trackerHoldings, returns and allocation
- ROI calculatorQuick return on any investment
- CAGR calculatorAnnual growth between two values
- All templatesFree spreadsheets
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.