Using Spreadsheets for Self Employed Tax Preparation

Working for yourself at home in your PJs is awesome! Paying taxes and keeping track of what’s deductible when your finances can get a little mixed isn’t always awesome. If you’re a freelance consultant or making money from home as a blogger, spreadsheets are a useful tool to organize your finances for tax time. Using Google Sheets takes it a step further because you can directly share your spreadsheet with your accountant.

In this post I’ll show you how you could pull together some of the Tiller tax time tips and tricks from our previous post into a tax preparation spreadsheet from the perspective of someone who works from home and has a secondary income. The end goal is to create a Google Sheet that can easily be shared with your accountant and provide most of the tax information she might need to file your return.

A minimal financial spreadsheet

Let’s say you have a Tiller Sheet that you use for your day-to-day expense tracking that includes only the transactions from personal accounts. You might have some customized spending analysis reports and other data here that you don’t really want to share with your accountant. So you may want to start with a fresh Tiller Sheet that only links your business accounts for sharing with your accountant. In this example, we start out with the Build Your Own template for the business accounts because we need the category logic, but we want to build other custom finance reports and don’t need the weekly spending ones included in the Standard template.

However, we will need the personal spending data available in this business sheet because if you’re working from home some of the spending from those personal accounts is tax deductible, like a percentage of rent, utilities, Internet and cell phone bills. To connect the spreadsheet with your personal accounts and the spreadsheet with your business accounts, we’ll used the Google Sheets IMPORTRANGE formula to pull in the transaction data from the personal spending Tiller Sheet.

The IMPORTRANGE formula is awesome because it will continue to pull in new categorized transactions into this business accounting sheet. We can even hide this sheet so it’s not readily visible to your accountant. Right click on any tab along the bottom to open a menu and hide that sheet.

Categories are the backbone of pivot tables

If you’re diligent with tracking your spending, a few times a week you might tidy up your transactions sheet in both of these money management spreadsheets to make sure all the transactions are categorized. This makes your job easy at the end of the year when it’s actually time to file taxes. Your accountant needs to see the categorized business transactions in order to classify and justify each business expense and the categorization of your personal spending helps quickly identify where deductions can be made.

Remember if you are just starting a new sheet for the business expenses, or you have months of uncategorized data from 2016, you can quickly categorize using these steps.

Totaling personal deductible expenses

Now you have all the data you need to start building out a few pivot tables to get your accountant the totals that she needs to complete your taxes. First, you start out with the personal deductible expenses. You can create a year-end pivot table using all the data from the IMPORTRANGE transactions sheet, which is the personal spending, and then filter it only on the categories that are deductible like charity, rent, utilities, cell phone and others for the year 2016. An accountant can help you with the specific percentages of these totals that can be deducted as business expenses for your circumstances.

Totaling business expenses, income, interest and paid taxes

Next, you create a pivot table from the transactions sheet that only includes data from your business checking account for 2016. All the spending here is likely deductible because it’s all business expenses. You can filter out income categories from expense categories and put them in their own pivot table.

As a freelance consultant or blogger you’re likely paying estimated taxes throughout the year so your final pivot table includes that data. It’s probably helpful to create separate pivot tables for these totals because it makes the data easier to understand, but you could create one pivot table that has business data and one table that has personal spending data.

Finally, you can add some manual calculations to estimate the federal taxes that might be remaining based on how much you’ve already earned and paid throughout the year. This is a useful baseline to compare against your accountant’s tax calculations, and it can also help with some projections for the year to come.

Sharing your tax data with your accountant

The last and final step is to share this sheet with your accountant using the Share button in the upper right corner of your spreadsheet. Since we’re using Google Sheets in this example it’s super simple and you can read more about permissions and how to share this tax prep Google Sheet in our post Collaborative Financial Worksheets: Sharing Your Tiller Sheet

Of course, depending on your situation your tax spreadsheet might look entirely different, but this is one example of what’s possible with the power of Tiller and Google Sheets.