Recently I got asked for help by a colleague. She had helped a friend set up a spreadsheet to track his income and expenditure on his properties. Of course he had started off with one and that was easy to track but he had been very successful and was now up to 20 and found tracking his income and expenditure really tricky in the format she had used. Different properties were on different sheets so it was nearly impossible to get an overview. He was coming to the end of the year and looking to see how to make it easier.

I gave this some thought. My thinking was the following:

How can I make this relatively easy to use?
How can I make it so that he can easily add new properties/costs etc.
How can I set it up so that he can get a quick snapshot of what his income and taxable liability should be?

So I decided to go the pivot table route…but worked to keep it as easy as possible. So I am doing this over 2 blog posts. In this first blog post, I will show you how to set up your data so that it’s pivot table friendly and in the next blog post (to be published on 18/11/2013) I will show you how to create a pivot table from it that gives you your summary view.

I’ve got the sample ready for you at the bottom of this blog post.

First step:

Create a data entry sheet (called. ..surprisingly enough…Data Entry) . I’ve added the following headings:
1. Property Name: Enter a name you can identify the property by
2. Amount – Full: (this is the actual amount paid or received). If it’s a cost enter it as a minus e.g. -25
3. Amount – Taxable (Not all of all expenses are allowed for tax purposes, this is where you can enter the amount that IS tax-deductible). If it’s a cost, enter it as a minus e.g. -20
4. Date: Just enter date of transaction (and if you are not sure of the date, just enter the first day of the month)
5. Category: – Broadly speaking, a transaction will be income or expense. Rent is income (assuming you are receiving it and mortgage payments, repairs etc are expenses)
6. Description: This is for yourself later so that you know what it relates to.

Data Entry guidelines:

Assuming every property will receive a monthly rent, you will need to enter 12 rows for this e.g. an entry for every month. Assign it the category Income. Description is optional

For Costs: Enter the property name, the full Amount (with a minus), taxable amount e.g. if you have paid €100 for repairs but you can only deduct €80 for tax purposes . Enter- €100 under Amount-Full and -80 under Amount-Taxable. Enter the date and assign it a category e.g. Cost.

You can add a further description in Description or you can add a comment by right clicking on the cell and going to Insert Comment

When you have added in some sample data – convert this to a table (Insert – Table). This will make our Property Portfolio Data Entry much more pivot table friendly. We will be covering this in the next blog post.

Have fun with this. The beauty of this is that it’s a template that can be adapted to quite a few situations…

As always if you have any comments/suggestions, please feel free to add them 🙂

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!) that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper.

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

2 Comments

shamsu on March 9, 2015 at 8:06 am

sing Excel to track property rent and expenses | 01 of 02 – Getting data set up
Published on November 11, 2013 by admin in Excel | General
0
Hi I looking for the second part of this template can u mail the same to me
regrads
shamsu
- admin on March 31, 2015 at 8:59 am
  
  hi Shamsu, sent you the link to this…hope you got it.

Cookie	Duration	Description
cookielawinfo-checbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Using Excel to track property rent and expenses | 01 of 02 – Getting data set up