Many firms do not have the volume of estate tax work that would make the purchase of estate planning software economically feasible. These same firms, however, do have spreadsheet software packages, mostly Lotus 1-2-3, that they frequently use for applications outside the tax area. The benefits of using a spreadsheet program to make your own estate planning software are fourfold. Creating this type of software is cost effective, saves time, increases your productivity, and delivers side benefits that are discussed at the end of this article.

Creating the file that will calculate the federal estate tax, including the state death tax credit and unified credit, as well as the New York State estate tax, takes only about an hour for anyone that has some experience using Lotus 1-2-3 software. For those practitioners operating in other states, the New York calculations can serve as guidance for developing the specifics for their own states. The minimal time, even at a high billing rate, needed to implement the instructions that follow, plus ownership of the spreadsheet software is certainly more cost effective than purchasing a ready-made package that could cost thousands of dollars.

The various calculations that have to be made just to figure the correct tax manually is time consuming and cumbersome. If there are any changes, the chance for a miscalculation is ever present. When a series of computations are involved, doing calculations by hand can take a staggering amount of time and will have to be rechecked. The spreadsheet can eliminate the tedium of repetitive calculations and, certainly, save many hours of hard-to-bill time. Because the calculations are all automatic, a clerk or staff accountant can input the data, thereby enabling you to take care of other matters.

Setting Up Tables

The first step in creating your estate tax calculator file is to set up tax tables for the federal and state tax rates and the state death tax credit, as shown in Figure 1. These tables should be put on the spreadsheet in an area that will not be used for printing or interfere with the area you plan on using for your projections. Most projections don't require much more than one page, so the rate tables should start after cell A100. To begin, you should adjust the width of column A to 30 and the width of columns B,F,G,K and L to 13. Access the main menu by pressing the (/) key. Select Worksheet, Column, Set-Width and then type in the amount desired, either 30 or 13, then press the Enter key. To make reading the tables easier, format the range of these columns to use commas. From the main menu select Range, Format, (,) and 0. When prompted, move the cursor to the beginning of the range to be formatted.

Press the (.) key, then use the arrow keys to highlight the range to be formatted for commas. After you've highlighted the entire range press the Enter key. For columns C,H and M the range format should be for two decimal places. Select Range from the main menu, then Format, ,) and 2. When prompted, follow the same method as above to indicate the range to be formatted.

Once the rate tables are set up they should be assigned names. This will make the formulas for calculating the tax easier to work with. In Figure 1 the range (A106..C124) is named FEDTAX; (F106..H125) is named CREDIT; and (K106..M125) is named NYSTAX. To assign a name to the tables you've created select Range from the main menu then Name. When prompted, type in the name you want to use; then press Enter. Highlight the range by moving the cursor to the beginning of the range, press the (.) key and then use the arrow keys to cover the entire range. Once the entire range is highlighted press the Enter key.

The next step is where your creativity and experience in the estate tax field come into play. The projection area can be tailor-made to your particular needs. The projection in Figure 2 is a simple projection base that can be easily modified by just inserting or deleting rows in the spreadsheet.

In Figure 2 the heading is placed in Cell Al and the descriptions in Cells A3 through A13. The gross estate and deductions at Cells B3 and B4, respectively, must be input from the keyboard. The taxable estate at Cell B5 is automatic after the formula + B3-B4 is inserted. Please note that in this and all other formulas the brackets are not to be input. As stated earlier, inserting rows can create ever more sophisticated tax projections. The only requirements before you start the tax calculations, are the simple addition and subtraction type formulas as shown.

Entering the Formulas

After your projection model is complete, the next step is to input the formulas to calculate the taxes and credits. In Figure 2 the federal estate tax shown in Cell B6 is calculated by entering the formula @ ROUND(@VLOOKUP(B5,$FEDTAX, 1) + @VLOOKUP(B5, $FEDTAX, 2)*(B5 - @V LOOKUP(B5,$FEDTAX,0)),0). Note that in the formula, B5 refers to the taxable estate as computed in Cell B5. The unified credit in Cell B7 uses an IF" type formula in order to handle estates where the net federal tax before the unified credit is under $192,800. The formula for Cell B7 is @IF(B6<192800,B6,<192800). Cell B8 uses the formula + B6-B7 to automatically compute the federal tax before state death tax credit. In Cell B9 the credit for state death taxes is computed with a slight twist. Because this credit is limited to the federal estate tax less the unified credit we have to use an IF" type formula. The IF" type formula requires that the calculation formula be put in twice (i.e., if state credit is less than tax after unified credit enter state credit, otherwise, enter the tax after unified credit). Putting the state death tax credit formula in twice is too much to fit in the cell. Therefore, we need an orderly way to get around it. The solution is to put the state death tax credit calculation formula @ROUND(@VLOOKUP((B5- 600 00), $CREDIT, 1) + @VLOOKUP((B5-600 00), $CREDIT,2)*((B5-60000)- @VLOOK UP((B5-60000), $CREDIT,0)),) in Cell A150 or any other cell that's out of the way. Then in Cell B9 you use an IF" formula that substitutes A150 with the calculation formula. So, in Cell B9 put in the formula @IF(B8<A150,B8,A150). Note that Cell B8 refers to the federal estate tax less the unified credit. The net federal estate tax in Cell B10 is automatically calculated by using the simple addition/subtraction formula +B8-B9. The New York state estate tax in Cell B11 is calculated with the formula @ROUND(@VLOOKUP(B5,$ NYSTAX, 1)+@VLOOKUP(B5, $NYS TAX,2)*(B5-@VLOOKUP(B5, $NYS TAX,0)),0). The same type of IF" formula used to compute the federal unified credit is used to compute the New York State unified credit. Since New York's calculation of this credit is more difficult than the federal calculation the formula is slightly more sophisticated, @IF (B11 <2750,B 11,@IF(B11 < 5000,5500-B11, 500)). Cell B 13 uses the formula +B11-B12 to automatically compute the net New York State estate tax. The final step of adding the federal and state taxes in Cell B14 is using another addition formula, +B10+B131.

Now that the hard work is done, don't forget to save it. In fact, it's always a good idea to save the file every so often as you're inputting so that you won't lose any work already entered. Select File from the main menu then Save. Type in a file name (without an extension) such as ESTTAX and press the Enter key.

As with anything dealing with computers, you must be specific. The formulas above will not work if you miss a simple comma or bracket. You'll know if the formula is input incorrectly when Lotus 1-2-3 beeps and flashes an error signal after the enter key is pressed. However, once you've worked with the formulas for a while and have analyzed them, they become tools to build upon. That's the fourth benefit of creating your own estate tax projection package. Soon, you'll be using these tax projection basics to make 5 or 10 scenario "what if " estate plans. Your knowledge of Lotus 1-2-3 will increase and the many uses of spreadsheets will become more apparent and appealing to you as you reach new heights of computer literacy.