Classic Computer Magazine Archive CREATIVE COMPUTING VOL. 10, NO. 1 / JANUARY 1984 / PAGE 180

Using VisiCalc to compute your income tax. Williams. David A..

Using VisiCalc to Compute Your Income Tax

Income tax afflicts everyone, but if you have a computer, you can make the calculations less painful and perhaps avoid over-or underpayment by estimating your tax liability throughout the year. Many programs on the market calculate your tax and even fill out the forms for you, but if you own VisiCalc and your return is not too complex, you don't need to buy another program.

This article describes a VisiCalc "program' that helps you estimate your tax liability as the year progresses and then does the necessary calculations for your return. It won't print the data on the return, but it is a simple job to transfer the data because the format is designed to resemble the appropriate IRS forms.

The program is set up for a joint return that includes Form 1040 and Schedules A, B, D, and W. The output from the schedules is automatically entered on Form 1040 in the appropriate place. This is about all that 48K of RAM can handle, but data from other schedules can be entered manually on the 1040 in the spaces provided.

Program Description

Figures 1 through 5 show the VisiCalc display or printout before data have been entered. The row and column headings are not part of the program, but were added for clarity. As much as possible the format matches the corresponding IRS forms. The width, except for Schedule D, was held to six columns or 54 characters so that the entire width would be visible on my 64-column display. Line numbers are included to help transfer the data to the real forms. The forms have been arranged in vertical sequence to make it easy to add more rows. In some cases space has been provided to enter the data in more detail than is required to aid in keeping records.

Figure 6 is a listing of the formulas used to calculate the data. The letter and numbers following the arrow indicate the cell in which the formula must be entered. Logic functions are used extensively to calculate entries that are dependent on the data. For example, F18 contains the formula for calculating general sales tax based on the tables provided by the IRS. The formula shown is for a family of five with an adjusted gross income greater than $38,001 and living in Florida. The formula for the same family with an income between $26,001 and $38,000 would be: @IF (F179<26001,0,277 + (@INT((F179 + @IF (E159 > E158, E158, E159) + F67+F104-26001)/2000)*14))

The formula can be adapted to other income ranges and numbers of dependents by changing the numbers 26001, 277, and 14 to the appropriate values from the sales tax tables. Figure 7 shows an excerpt from the Optional State Sales Tax tables from which these figures are taken.

To set up the formula for your situation, first select your state, then find the row where the minimum income is less than your range of interest (26001 in the example). Next, find the sales tax in the column under the correct number of dependents (277 in the example). Finally, find the difference between that and the sales tax in the next row down (14 in the example). The formula is then accurate for as many rows as the increment is constant.

For incomes greater than $38,001, use the sales tax shown in the row for incomes between $38,001 and $40,000, and the increment given directly below. Substitute these values in the formula shown in the listing.

The income used is the adjusted gross income from Form 1040, line 33, the dividends exclusion, All-Savers interest, and the married couple deduction. You may be entitled to include additional non-taxable income. Consult the instructions for the sales tax tables. Make sure your total income doesn't exceed the upper limit for the range you have set up, and don't forget to change the formula if the number of dependents changes.

If your income turns out to be less than that for which the formula was set up, it will return $0, which should alert you that something is wrong. No warning is given if you exceed the range on the high side, but the error will be small if you don't go too far over.

The formulas in cells H182, G183, H183, and F184 are used to calculate your tax if you use either Schedule Y or the tax tables, your taxable income is greater than $11,900, and you are a married couple filing jointly. If your filing status is different, a comparison of these formulas with the numbers in Schedule Y shows how they were derived. H182 adjusts incomes under $50,000 so that the formulas give the same answer as the tax tables. The others calculate the tax and are separate only because the formulas are too long to fit in one cell. The correct tax always appears in F184; therefore, it is the only one that needs to be displayed or printed.

Loading The Program

Enter the program by typing the labels from Figures 1 through 5, the numbers in cells F48 and E159 (entered as values), and the formulas from the listing in Figure 6. Everything to the right of the colon must be entered. F103 contains a format statement and a value (.05), and F154 contains a format statement. The purpose of the format statements is to override whatever global format is invoked. Be sure to enter these as formats, not formulas.

Using The Program

The program should be easy to use for anyone familiar with VisiCalc. If a cell is blank, then you must enter data if it is appropriate for your particular situation. Set the recalculation mode to manual so you won't have to wait after each value is entered. If a cell contains a formula, then the value will be calculated by the program. Rows of x's are used in some places for clarity and indicate that no data are to be entered in that cell. Figure 8 shows a completed set of forms.

The data may be entered in any order, but error messages may appear or some cells may have incorrect values if data are missing. The convoluted nature of the forms and the inter-dependence of the calculations make it impossible to calculate all of the cells correctly in one pass. Two or three passes may be required, depending on the particular entries you use. VisiCalc flags the incorrect cells with the error message on the initial load, but after that no indication is given that more than one pass is required. One way to be sure is to look at the cell containing the tax owed and recalculate until the amount is the same twice in succession.

Wages, IRA deductions, self-employment income, employee business expense, and Keogh payments are entered on Form W because they must be listed separately for the husband and wife. The program combines these inputs and enters the amount in the appropriate place on Form 1040. If you are single, you may still enter the data there. The program sets the married couple deduction to zero if income is not entered in both columns. Or you may delete Schedule W and the corresponding formulas in Form 1040 and enter the data there.

The data for schedules A and B are entered in much the same way as on the real forms. Make sure you change the number in cell F48 (Line A29) to match your filing status. The program automatically calculates the medical exclusions and the remaining All-Savers Exclusion. Schedule D is laid out in the same format as the real form. The program calculates all the entries except the information pertaining to the actual transactions. Form 1040 is largely automatic. You have to enter the number of exemptions, additional adjustments or income, and the amount of tax withheld. Some lines were omitted from this form, but blank lines are provided to enter additional lines if you need them.

The global format command can be used to display the data in either dollars and cents or rounded to the nearest dollar.

Updating The Program

The example shown and all of the formulas are set up for the 1982 tax laws. For 1983 the number in cell F103 will change to .1 from .05. If the sales tax tables change, the formula in cell F18 will change. The tax cut, if there is one, will change G183, H183, and F184. There may be other changes that I am not aware of. Read the instructions just as carefully as you would if you were calculating by hand.

A final word of caution. I have checked the program carefully, and it seems to work for my particular circumstances, but I strongly advise that you check your results by hand until you are sure it is working for you.

Photo: Figure 1. Schedule A.

Photo: Figure 2. Schedule B.

Photo: Figure 3. Schedule W.

Photo: Figure 5. Form 1040.

Photo: Figure 6. Formula listing.

Photo: Figure 7. Derivation of sales tax parameters for use in formula F18.

Photo: Figure 8. Completed tax forms.