5 Years ago I purchased an investment property. I get an annual statement from my property manager but that’s after the year’s gone and is only a historical look at how my investment is performing. I want to be able to track it’s historical performance and forecast it’s future performance. So I made an Excel spreadsheet which, over the years, has turned into many Excel spreadsheets. Now I have an organized system for tracking income, expenses, forecasting future expenses, a comparison calculator when I want to refinance my loan, management fees calculator and spreadsheets I can send to my accountant at tax time.
If you have an investment property as well, or want a spreadsheet to help you forecast how much it’s really going to cost (there’s been a lot more unexpected expenses than I thought there’d be), I’ve recorded a video walkthrough of my spreadsheets:
Let’s take a closer look!
Toenlarge the screenof the video, click the square icon in the bottom right hand corner of the video (it will say ‘full screen’ when you hover your mouse over the icon).
If you’d like a copy of these spreadsheets, they’re available from my online store or Etsy shop:
Online store
- Rental property spreadsheets (use for 1 property)
- Property spreadsheets (use for up to 10 properties)
Etsy store
- Rental property spreadsheets (use for 1 property)
- Property spreadsheets (use for up to 10 properties)
Version 1
Record income and expenses for your investment property for up to 10 years (1 tab / spreadsheet per year). With these spreadsheets you can track positive or negative gearing and how much the property is truly costing you.
For each year, there is a tab to record income and expenses. The expense categories are pre-filled but you can type over the top of the existing categories and add your own in the blank rows as well. On each of these tabs is a pie graph of the expenses (by category) which will automatically update as you enter values into the spreadsheet, so you can see which expenses are big ticket items, where you might be able to cut back, and also to forecast future maintenance items.
Anything you enter in the tabs for each year will automatically populate the summary tab. The summary tab has a pie graph of expenses (by category) and a bar graph for income versus expenses.
Version 2
This version has the same income and expenses summary spreadsheet as version 1, but it can be used for any year and is not linked to a summary tab. This spreadsheet can be used for scenario analysis to see if a property you are interested in purchasing is likely to be positively or negatively geared. I enter forecast costs in red. Once they have been spent I change the font color to grey.
Make sure you’ve received all of the rental income you’re owed. Especially helpful if you have a share house with multiple tenants, or are leasing out rooms in the home you live in
Pre-filled with more than 20 ongoing costs such as pool maintenance, gardening, pest control, smoke detector maintenance, insurance etc. You can add your own items as well. This spreadsheet can be used before you even purchase a property so you know how much you’re up for. Once you’ve purchased a property you can transfer these forecasts to the ‘income and expenses’ tab
Improvements made to the investment property (e.g. cost of painting, purchase a new oven). This spreadsheet is not for repairs or regular ongoing expenses such as body corporate. If you live in Australia, send this to your Quantity Surveyor when they are preparing your tax depreciation schedule
Use this to record how much you paid for the property (not just the selling price, all of the other on-costs too!)
You can also use this to help determine if a property you are interested in purchasing is actually feasible (and the true costs).
There are sections to record upfront repairs and renovations, as well as the sold price and selling costs. This spreadsheet will assist in calculating Capital Gains Tax and / or guide you to determine how much you need to sell the property for in order to make a profit.
Use this when you first obtain the mortgage, and also when you want to refinance. You can compare fixed and variable interest rate loans for up to 3 lenders. There is space to record upfront fees, annual fees, extras (e.g. credit card with no annual fee) and any specials or promotions. There is also a section to record refinance costs such as break-fees so you can determine the total true cost of refinancing
Calculate how much it costs to hire a property manager to perform regular inspections, keep up to date with legislation, handle the lease documents etc. You can see different scenario’s of how much you’ll pay in property management fees depending on how much rent you charge per week/month/year. You can change their commission % to whatever number you like
______________________________________
ABOUT THE SPREADSHEETS
– Instant download – start using it straight away!
– Excel file format (can also be used in Google Sheets and Numbers for Mac)
– Formulas will automatically pull the numbers through from the detail tabs (where you input your information) into the summary tab so you don’t need to enter things twice
– Can make as many copies of the spreadsheet as you need for each of your rental properties
– Purchase once and re-use again and again each year
Note: I have not included tax implications in this spreadsheet so you can live anywhere in the world and use these spreadsheets
Instructions for using in Google Sheets arehere.
I’ve created another version of the spreadsheet where you can track income and expenses for up to 10 properties (if you need more you can just make a copy of the spreadsheet for properties 11 to 20).
Update: Per reader’s request, there is another version of the spreadsheet included in the download if you want to track up to 20 properties
At the start is a summary of weekly rent and the date rent is due for each property.
Then it has the same format as the other spreadsheet with a summary for the entire year, but this time each column is a property instead of a year. I.e. you’ll use one Excel file each year instead of 10 excel files for 10 years.
These spreadsheets can be used in Google Sheets. Instructions are in this post.
If you’d like a copy of these spreadsheets, they’re available from my online store or Etsy shop:
Online store
- Rental property spreadsheets (use for 1 property)
- Property spreadsheets (use for up to 10 properties)
Etsy store
- Rental property spreadsheets (use for 1 property)
- Property spreadsheets (use for up to 10 properties)
______________________________________
MORE SPREADSHEETS
- Budget Calculator
- Personal budgeting
- Family budgeting spreadsheets
- Business budgeting
- Renovation planner
- First Home Budget
