Known Users


Known Users

Getting down to Brash Tax; A spreadsheet approach to tax planning



Author: Joe Duroux
Date: February 1994
Keywords: software IRS tax tips excel
Text: Some people like specialized tax software. You may like it, but don't be oversold by its advertising. The guaranteed accuracy applies only to arithmetic. The publishers disclaim responsibility for the on-screen tax information, and I have found flaws in it. Another bit of hype is that the software can print the filled-in tax forms. But, as I said in last February's issue, "Filling out the forms and doing the arithmetic is the easy part of filing a tax return. The difficult part is learning the rules to determine what is required." To do your own taxes properly, you have to read all the IRS instructions, and get all the IRS publications, that apply to your situation, whether or not you use tax software. I have tried two kinds of tax software, and I find it much easier to do my own thing on a spreadsheet. A computer is useful mainly for tax planning. At the beginning of each year, you can make a copy of the previous year's spreadsheet, or tax software, and enter estimated figures for the coming year. As the year progresses, you can refine the estimates. You can also try hypothetical entries to determine their effects. At the end of the year, when you receive the new IRS forms, you can update the line structures and formulas on the spreadsheet, or buy the tax software update, and get the additional forms, instructions, and publications that you need from the IRS and the state. When you receive your confirming forms like the W-2, 1098, 1099, K-1, etc., you can finalize the figures and fill out the IRS forms for filing. I use Excel 3, but my procedures can be used with other spreadsheets. After trying many approaches, I have found it easiest to have all of the tax forms on a single worksheet. I separate them horizontally because they differ in column structure, and vertically, with extra rows between, so that the line structure of each form can be updated without affecting the other forms. The order (top left to bottom right) is based on the direction of most of the data transfer. I have configured a separate window to view each form. I stagger the windows as shown in Figure 1 so that I can click on a corner or edge of the form that I want to view. The title row and entire width of each form can be frozen in its window, preventing horizontal scrolling and providing an upper stop for vertical scrolling. The windows look like separate worksheets. I put the IRS Return (1040) window at the top, although it isn't first on the worksheet, because I want it to show the most lines. Then I lock (protect) the windows (but not the cells) so that they can't be changed or closed (and lost) inadvertently. To keep things simple, I use the following principles: The formulas are straightforward, except for computing the tax. If tax formulas are too much for you, use the tax tables. The formulas represent the Tax Rate Schedules. In a blank area below the 1040, you can set up matching columns containing tax rates, bracket thresholds, and increment formulas. Figure 2 shows Schedule X. Each formula in the right hand column computes the added tax on the increment of taxable income above the threshold in the second column, using the added increment of tax rate above the previous row (e.g. 0.280.15=0.13). The example is for a taxable income of $60,025. The full tax is the total of the third column. To use a different Schedule, you need only change the figures in the second column. To emulate the actual IRS Table (for a taxable income between $3,000 and $100,000), first round the taxable income to the nearest odd $25 (divide by 50, truncate, multiply by 50, and add 25) before putting it into the formula. Then round the result to the nearest integer. For the California Table, round the taxable income to the nearest $100 before using the formula. But round it so that a number ending in 50 rounds downward instead of upward (subtract from a larger number, then round, then subtract from the same larger number). For final filing, use the actual tax tables to be sure. If you want, I can upload my blank Excel file (with formulas) for 1993 to the BBS as an example. You'd have to modify it quite a bit to use it. It includes only the forms that I use for planning (A, B, D, E, 1040, CA, and 540), and it leaves out many lines that I never use. It also has little quirks that fit my own situation and taste.

Copyright © February 1994 by Joe Duroux


Return to:
Known Users archive