Excel Speadsheet basics - Example?

9 Replies

I'm managing one single family and we're looking to buy our first duplex.  I'd like to create an excel spreadsheet to manage when and what rent has come in, what expenses are and generally everything that needs and should be kept up with on a monthly and yearly basis with rental property, but I don't really know where to begin to create an effective spreadsheet.  I've checked the file resources and see many, many different options, but most deal with calculating things or most aren't just basic management.  Perhaps this is too common, but I've never created an Excel spreadsheet and I'm afraid I'll not think of or leave out very important things.

Does someone have an example of how they create or manage their properties with Excel and what they've included.

Thanks,

Brandon

I don't bother with when rent needs to come in, because if it's not in my account 5 days after the first someone is getting a Notice to Vacate. That takes care of that record keeping. 

I keep spreadsheets on each property mostly to keep up with tax information. I use 3 categories - mileage, meals, materials, and just drop the receipt expenses into each. At the end of the year I'll pull out of materials anything that needs to be capitalized (i.e. kitchen cabinets). My spreadsheet for each property more or less looks like this: date of item in Column A, expense in appropriate column B-D. Autosum formula at the bottom of each column.

I don't bother with maintenance; I keep up with repairs as necessary and most regular maintenance (i.e. cutting grass) is done by the tenant. If you keep up with all maintenance, or contract it out, you would probably want to have a spreadsheet for this. Just like with expenses, except you probably list the maintenance item in Column A (clean gutters, change filters, etc), and create however many Columns B-whatever as necessary for the frequency of the item. Then you can fill in the column with the date it was done, or if you want to calculate it for tax purposes, the amount you paid (where the headers are the months/weeks/days). 

Thank you for your reply.  So do most people keep a separate spreadsheet for each individual property?  If it is a duplex, would they have one spreadsheet for the property or one per door?  I'm curious how people keep track of receiving rent and all of the various things that go along with managing multiple rentals with excel.

@Brandon G.   My business model is a little different from yours but I use Excel to keep track of ALL of my information.  I've found that it's flexibility suits my needs better than anything else.  I don't even use Quicken because it's too restrictive and has a lot of stuff I don't want or need.

The best way I've found to build a useful spreadsheet is to just start throwing things on the page.  When I look at it the next day, I usually see things that need to be moved that would make it better and more user friendly.  Over time it will be well-organized and it will have everything you need.  Record everything and sort it out as you go.  

Originally posted by @Brandon G. :

... I'm afraid I'll not think of or leave out very important things.

...

 So what?  You can edit that spreadsheet to add extra rows or columns to insert anything you might have forgotten. If it's important you add it. And it's not like you are putting your spreadsheet for sale like it's a finished product, correct?

For multiple properties I would suggest Quickbooks rather than excel. Far easier to use once you set up and the reports are nice to analyze the performance. Plus makes it really easy for your tax guy at year end.

I agree with Anish my accountant also suggested Quickbooks rather than using excel. It saved my time a lot and I find it easier to use. I've been using it for years now. You can try it as well Brandon.

Originally posted by @Brandon G. :

Thank you for your reply.  So do most people keep a separate spreadsheet for each individual property?  If it is a duplex, would they have one spreadsheet for the property or one per door?  I'm curious how people keep track of receiving rent and all of the various things that go along with managing multiple rentals with excel.

 I use a separate file per property and then break up the sheets for my categories (materials, mileage, etc). I like clean looks and rarely have to mess with multiple properties at a time anyway. You could do everything in one file and then break up the sheets per property, or per door, if you wanted to go that way.

If you have a boatload of properties Excel might not be for you. I don't, so it works for me. I don't have to keep track of deposits, because I put them in a separate bank account. I know who has paid rent and who hasn't, so I don't need Excel for that either. If you have a lot of late payers, or people on payment plans, you might have a different set of needs.  

Quickbooks does take a little bit to get used to and to set up.  I would recommend sitting down with your accountant to get it situated properly.  Mine inserted tags for all sorts of things that I personally don't use, but he uses come tax time.  Once the tools for lack of a better term are set up for the needs of your business and you understand how the account generally wants things set up outside of the obvious ones, it really is quick and convenient.  You can print a report for a specific property or class in minutes with customized dates, etc.  I personally like quickbooks because of the layout of the report.  It just registers better with me personally because it is similar to how I would write it out.  You can break things down as specifically as you like, or keep things on a more general level.  

This post has been removed.

Create Lasting Wealth Through Real Estate

Join the millions of people achieving financial freedom through the power of real estate investing

Start here