Rental property spreadsheet

• File name: rental-property-spreadsheet.xlsx
• File size: about 23 KB
• Price: free
• Works with: Excel, or Google Sheets via File, then Import

Inside the rental property spreadsheet

There are 4 sheets in the workbook, and typing only happens on the first.

• Inputs: fill in the purchase price, monthly rent, other income, vacancy rate, property tax, insurance, HOA dues, repairs, a capital reserve and a management fee. Add your loan details too: down payment, interest rate, loan term and closing costs. Next, set your DSCR target and the growth you expect, including how much the home may appreciate, how rent may rise, how costs may climb and what selling costs to plan for. Every sample value comes with a note about where it came from.
• Analysis: all the results, from yearly income to returns for five-year and ten-year holds.
• Hold and sale: shows how rent, costs and the sale lay out year by year for five-year and ten-year holds.
• Amortization: all 360 months of the loan, showing each payment, the interest, the principal and the balance.

The Analysis sheet covers:

• Income: the gross scheduled rent, the vacancy allowance, the income left after that, operating expenses, NOI and the cap rate.
• Loan: the loan amount, monthly principal and interest, and yearly debt service.
• Cash: what you keep each month and each year, the cash you put in, your return on that cash, and your full monthly payment including tax and insurance (PITIA).
• Lender checks: lender DSCR, NOI DSCR, the biggest loan amount your DSCR target allows, and the down payment needed to clear it.
• Quick checks: the 1% rule, gross rent multiplier (GRM), break-even occupancy and year-1 interest.
• Returns: IRR for a five-year hold and a ten-year hold.

If this is your first rental, some of these terms may be unfamiliar to you. Net operating income, shown as NOI on the sheet, is a year's rent after vacancy minus the operating costs, before any loan payment. The cap rate is NOI divided by the purchase price. Cash-on-cash return works out the yearly cash flow against the cash you put in. For DSCR, you divide income by loan payments, and lenders usually want to see about 1.25. The 1% rule compares monthly rent with the purchase price, and GRM is the price divided by the yearly gross rent.

Every result on the Analysis sheet runs on a live formula. Select any cell and the bar along the top displays the calculation behind that number, so you can check every figure on your own.

Using the rental property analysis spreadsheet in Excel

Open the downloaded file in Excel to begin. A sample deal appears right away, so you can see a completed analysis before you make any changes.

1. Head to the Inputs sheet, since it is the only sheet where you need to enter anything.
2. Type in the purchase price, the monthly rent you expect, and your yearly property tax and insurance, replacing the sample figures.
3. Update any other input only when you have your own numbers for it. Each sample value has a note on where it came from.
4. Look at your results on the Analysis sheet. The Hold and sale and Amortization sheets update on their own, so there is nothing to enter there.

Entering a figure on Inputs updates all the other sheets. If a number on Analysis looks odd, click the cell; its formula reveals the inputs it relies on, which usually points to a typing slip on Inputs.

Working with Google Sheets

You can use it in Google Sheets: open Google Sheets, choose File, then Import, and select the downloaded file. Then enter your details on Inputs and review the results on Analysis.

The sample deal you see when the file opens

The sample property is a three-bedroom house in ZIP 46219, Indianapolis, Marion County, Indiana, with a purchase price of $205,000 and a monthly rent of $1,840. That rent is HUD's FY2027 Small Area Fair Market Rent for a three-bedroom home in this ZIP code.

Using these values, the Analysis sheet reports a cap rate of 6.82%. The cap rate calculator on our home page gives the same 6.82% with these inputs, because the spreadsheet uses the same formulas.

After reviewing the sample, enter the details of your own deal over it on the Inputs sheet.