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:

Make the forms easy to use on-screen. Accurate facsimiles are not
needed.
Design only forms that are personally needed to estimate taxes in
advance. Secondary forms like the 4562 (depreciation) and 8582
(passive loss limitations) are rarely useful for advance planning.
Their outputs to the primary forms can normally be estimated directly.
In each form, include only lines that are needed, at least in some
years. Leave out lines, and even whole sections, that are never used.
And to reduce scrolling, hide (make zero height) lines that are not
currently being used.
Use reliable data links between forms. Be sure that cell references
are maintained when line structures and formulas are updated. I find
named references the easiest to maintain.

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.28Ð0.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.
Return to:
Known Users archive