Free Excel template · Excel for real estate
Free rental property cash flow template
For anyone looking at a rental listing and wondering whether it pays. Type the price, down payment, interest rate, rent and expenses; the sheet works out the mortgage payment with PMT, net operating income, monthly and yearly cash flow, cap rate and cash-on-cash return. The sample shows a $285,000 house that only just covers its costs, which is exactly the kind of deal this template is built to catch. It is a calculator, not investment advice.
Preview and try it
This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.
| A | B | |
|---|---|---|
| 1 | Rental property cash flow | |
| 2 | Purchase and loan | |
| 3 | Purchase price | 285000 |
| 4 | Down payment % | 0.25 |
| 5 | Closing costs and initial repairs | 9500 |
| 6 | Interest rate | 0.0675 |
| 7 | Loan term (years) | 30 |
| 8 | Loan amount | 213750 |
| 9 | Monthly mortgage payment (principal and interest) | 1386.38 |
| 10 | Cash invested | 80750 |
| 11 | ||
| 12 | Income (monthly) | |
| 13 | Rent | 2450 |
| 14 | Vacancy allowance % | 0.06 |
| 15 | Effective rent | 2303 |
| 16 | ||
| 17 | Operating expenses | |
| 18 | Property tax (per year) | 3400 |
| 19 | Insurance (per year) | 1380 |
| 20 | Repairs and maintenance (% of rent) | 0.08 |
How the formulas work
Mortgage payment with PMT
The yearly rate divided by 12, the term in months, and the loan amount with a minus sign so the payment is positive. This is principal and interest only; tax and insurance are in the expenses below, so they are not counted twice.
Monthly operating expenses
Yearly bills (tax, insurance) are divided by 12, the percentage items are taken from the rent, and monthly bills are added as they are. Putting repairs and management in as a share of rent means they rise with the rent. If you manage the property yourself, set B21 to 0, but your time still has a cost.
Monthly cash flow
Net operating income (effective rent minus operating expenses) minus the mortgage. In the sample it is about $41 a month, so one surprise repair wipes out a year of it. Try a higher down payment, lower price or higher rent to see what it takes to change the verdict.
Cash-on-cash return
Yearly cash flow divided by all the cash you put in: down payment plus closing costs and initial repairs. Compare it with what the same cash could earn elsewhere. Cap rate in B30 leaves the loan out, so it compares properties regardless of financing.
Make it yours
- Get the property tax and insurance from real quotes, not the seller's listing. Tax can change after a sale, and insurance for a rental usually costs more than for a home you live in.
- Run three versions of the sheet (copy the tab): expected, cautious (higher vacancy, higher repairs) and a rate one point higher. A deal that only works in the expected case is fragile.
- Cash flow here is before income tax and before principal paydown. Both matter to the overall picture, but they are separate questions from whether the rent covers the bills.
- For a multi-unit property, put the total rent of all units in B13 and use the building's expenses.
FAQ
How do I calculate cash flow on a rental property in Excel?
Take rent times (1 minus vacancy), subtract monthly operating expenses (tax and insurance divided by 12, repairs, management, HOA, utilities), then subtract the mortgage payment from =PMT(rate/12,years*12,-loan).
What is the difference between cap rate and cash-on-cash return?
Cap rate is yearly NOI divided by price and ignores financing. Cash-on-cash is yearly cash flow after the mortgage divided by the cash you invested, so it changes with your down payment and rate.
What is the 1% rule?
A rough screening rule some investors use: monthly rent of at least 1% of the price. The template shows the ratio in B32, but it is only a quick filter; the full cash flow above is the real test.
Functions used
Related guides
- How to calculate a loan payment in Excel
- How to calculate percentage in Excel
- Excel IF statement with multiple conditions
More free templates
- Rent roll template for ExcelFor landlords and small property managers who need one page that answers the monthly questions: which units are empty, who still owes rent, how much the vacancies cost, and whose lease ends soon.Free .xlsx
- Expense report templateFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order templateFor small businesses, office managers and buyers who send purchase orders to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
Updated