A Spreadsheet for Netting Property Transactions at Year End

By William A. Duncan and John O. Everett

Net gains on casualty or theft of personal properties are included in capital gains and losses. A net loss is available as an itemized deduction to the extent it exceeds 10% of AGI. And so it goes. Like items must be netted and moved along the chain until they reach their final destination in the tax return. The authors explain the flow and provide a spreadsheet that simplifies the entire process.

One of the nettlesome tasks in tax practice is the need to determine quickly the tax consequences of property transactions when a number of different types of dispositions occur during the year. The tax treatment of a particular gain or loss often depends on other property transaction netting results, and tracing these results through Forms 4684 and 4797, Schedule D, Schedule A, and the basic 1040 or 1120 can quickly become tiresome.

One answer may lie with a simplified spreadsheet approach for 1) analyzing the effects of additional property transactions before year-end and 2) consolidating all property transactions at the end of year. The consolidation procedure is a sequential netting of five basic types of transactions, as provided in the IRC. References to the location of these netting results on the relevant tax forms may also be of help and the consolidated technique is illustrated with comprehensive examples. A Lotus 1-2-3 template, developed by the authors for quick consolidation of property transactions, is presented at the end of this article.

The Property Transactions Netting Process

The year-end consolidation of property transactions requires a series of netting procedures for transactions relating to 1) personal property casualty and theft, 2) business and income-producing property casualty and theft, 3) IRC Sec. 1231, 4) capital assets, and 5) ordinary income.

Figure 1 presents a format for the netting procedure. Each property transaction that results in a recognized taxable gain or loss is first entered into one or more of the five columns, and each of the first four columns is then totaled and closed to the appropriate column to the right. The appropriate column depends on whether the columnar result is a net gain or loss. Ultimately, the final effect of all property transactions will be closed to the fifth column, ordinary income. Figure 1 also contains footnotes as guides to where the netting results are displayed on the various tax forms.

The following discussion describes each of the five basic netting processes with reference to the hypothetical data for an individual taxpayer in Figures 2 and 3. The only differences in Figures 2 and 3 are the sizes of the losses in the personal casualty and theft and business casualty and theft columns. These changes convert net gain results in Figure 2 to net loss results in Figure 3.

Personal Casualty or Theft. For individual taxpayers, gains and losses realized because of casualty or theft of personal use properties are netted separately from those related to business and investment properties. If losses exceed gains, the net loss is available as an (ordinary) itemized deduction, but only to the extent the net loss exceeds 10% of AGI. If gains exceed losses, all gains and losses are treated as arising from the sale of a capital asset and are transferred to the capital gain and loss column. Because of this possibility, the personal casualty and theft transactions must be netted before the capital gains and losses netting can be completed.

For simplicity, Figure 1 shows the net personal casualty and theft result being transferred to the long-term capital gains column. However, the measurement of personal casualty and theft gains and losses may include properties held one year or less. An overall casualty and theft gain that includes short-term gains and losses must be separated into short- and long-term components before transfer to the capital gain and loss column. This separation is both necessary and potentially beneficial in that short-term losses may be reduced or eliminated by other short-term gains, leaving long-term
gains intact.

The personal casualty and theft provision is the first of three "best of all possible tax worlds" opportunities offered by the IRC. This netting of personal casualty and thefts may be especially advantageous where casualty losses do not exceed 10% of adjusted gross income and will yield no tax benefit. Where multiple properties with a mix of gains and losses result from casualty or theft of personal use property, the netting process allows taxpayers to use losses to offset gains before those losses are reduced by 10% of adjusted gross income.

Example 1: Assume taxpayer Martha Quinn had a $3,000 personal casualty gain (insurance proceeds received on her pleasure boat purchased in 1980 and destroyed in a storm this year exceed the boat's adjusted basis by $3,000), and a $1,000 personal theft loss (uninsured diamond ring costing $1,500 in 1987 and with a current fair market value of $1,000 was stolen this year). The theft loss is reduced to $900 due to the $100 floor on personal casualty and thefts. The two transactions net to a $2,100 gain, and the net gain is transferred to the capital gain and loss column as a long-term capital gain. The facts of Example 1 are incorporated as part of the comprehensive spreadsheet of Figure 2.

Example 2: If the casualty loss were $10,000 rather than $1,000, the netting of personal casualty gains and losses would result in an overall net loss of $6,900 ($3,000--$9,900). The $6,900 loss is deductible against ordinary income after applying the 10% of AGI floor. Since AGI is unknown in the early stages of the consolidation, transferring net casualty losses to ordinary income must be the next to last step in the netting process. The final entry to ordinary income is shown in the comprehen-sive spreadsheet of Figure 3, that incorporates the facts of Example 2.

Business and Investment Casualty or Theft. A separate consolidation is required for casualty and theft gains and losses on business and investment (i.e., income producing) properties. These properties must have been held on a long-term basis.

Gains and losses from involuntary conversions (e.g., condemnations) that do not meet the definition of casualty or theft are not included in this netting category. Such gains and losses are IRC Sec. 1231 gains or losses.

If the business and investment casualty and theft losses exceed gains, the net loss is treated as an ordinary loss, deductible in determining adjusted gross income. As a result, these losses achieve ordinary loss treatment without having to survive the IRC Sec. 1231 netting and do not reduce IRC Sec. 1231 gains.

If business and investment casualty and theft gains exceed losses, the net gain is combined with other IRC Sec. 1231 gains and losses. These procedures maximize the possibility the taxpayer will end up with either ordinary loss treatment or long-term capital gains and the most favorable possible tax treatment.

Example 3: Assume the same taxpayer, Martha Quinn, experienced three business and investment casualties or thefts during the year. First, her warehouse, rented to a local business for several years, was completely destroyed by fire. The warehouse had an adjusted basis of $52,000 (all depreciation taken was straight-line) and she received $56,500 in insurance proceeds. Second, a press used in her business (acquired in 1988) was destroyed by fire. The press had an adjusted basis of $4,000 and she received $1,800 from her insurance coverage. Third, a computer used in her business (acquired in 1989) was also destroyed by fire. The computer was not insured and had an adjusted basis of $800. The business casualties net to a $1,500 gain ($4,500­$2,200­$800). Following the best of all possible tax worlds principle, the net gain is treated as an IRC Sec. 1231 gain and is combined with other Sec. 1231 transactions. This netting is incorporated into the comprehensive example of Figure 2.

Example 4: Assume, by contrast, the loss on the press was much larger, amounting to $6,200 rather than $2,200. Substituting a $6,200 loss for the $2,200 loss results in a net $2,500 loss. Again, under the best of all possible tax worlds, the loss is treated as an ordinary loss and is transferred to the ordinary income column. This loss is a deduction for AGI since the loss property was used in the taxpayer's trade or business. This netting is incorporated into the comprehensive example of Figure 3.

The IRC Sec. 1231 netting is completed once the business and investment casualty and theft gains are determined and any net gain is included in IRC Sec. 1231 transactions. IRC Sec. 1231 includes gains and losses on the sale or exchange of property used in a trade or business or held for investment. Such property must have been held on a long-term basis. Also included in the IRC Sec. 1231 netting are gains and losses from condemnation (or threat thereof) of trade or business and investment properties held on a long-term basis. IRC Sec. 1231 gains do not include depreciation recapture (i.e., IRC Secs. 291, 1239, 1245, or 1250) that are posted directly to the ordinary income column.

Example 5: Martha Quinn sold equipment used in her business for five years at a total gain of $7,100, of which $800 must be reported as ordinary income under IRC Sec. 1245. In addition, Martha sold land used in her business for five years at a loss of $7,400. These two transactions are reported in the IRC Sec. 1231 columns of Figures 2 and 3. Note, however, that only $6,300 of the gain on the sale of the equipment is included in the column for IRC Sec. 1231 gains and losses. The remaining $800 of recapture is posted directly to the ordinary income column.

If IRC Sec. 1231 losses exceed IRC Sec. 1231 gains, the net loss is treated as an ordinary loss. If IRC Sec. 1231 gains exceed IRC Sec. 1231 losses, the excess is treated as capital gain. Since IRC Sec. 1231 includes only properties held more than a year, excess gains are long-term capital gains.

Example 6: In Figure 2, the IRC Sec. 1231 transactions include the $6,300 gain and the $7,400 loss described in Example 5, and the $1,500 net gain transferred from business casualties and thefts. The final IRC Sec. 1231 result is a net $400 gain. Under the best of all possible tax worlds principle, the net gain is closed out to the long-term capital gains column.

Figure 2 demonstrates the consistently favorable treatment of net gains. The figure is, in one respect, not complete. IRC Sec. 1231 net gains may, in some circumstances, be denied long-term capital gains treatment. IRC Sec. 1231 gains are treated as ordinary income to the extent that IRC Sec. 1231 losses have been permitted ordinary loss treatment during the prior five years and have not been previously recaptured. To the extent recaptured, net IRC Sec. 1231 gains will be posted to the ordinary income column. Thus, we can say that net IRC Sec. 1231 losses will always achieve favorable ordinary loss tax treatment and net IRC Sec. 1231 gains will usually achieve favorable long-term capital gain treatment.

Example 7: In Figure 3, the IRC Sec. 1231 transactions include only the $6,300 gain and the $7,400 loss described in Example 5. Here, the IRC Sec. 1231 transactions net to a $1,100 loss and are closed to ordinary income.

The result of netting the IRC Sec. 1231 column in Figure 3 is distinctly different from Figure 2, both in amount and in tax consequence. This difference in result is caused by the change in the net business casualty loss assumptions (Figure 3 includes the $2,500 business casualty and theft net loss of Example 4 rather than the $1,500 net gain of Example 3). That $2,500 loss is closed to ordinary income and there is no effect on the IRC Sec. 1231 column (as there was in Figure 2). This net loss is a fully deductible loss in determining AGI since the property was used in the taxpayer's trade or business.

Capital Asset. The final step in the property-transactions netting process is to combine all gains and losses from sales or exchanges of capital assets. This procedure simply involves the determination of a net short-term and a net long-term result, and then combining that result with ordinary income. For both Figures 2 and 3, normal short- and long-term capital gain transactions have been included for illustrative purposes.

As Figure 2 demonstrates, at this point in the process, net IRC Sec. 1231 gains have been included in long-term capital gains. Moreover, net IRC Sec. 1231 gains will include any net gains from business or investment casualty or theft. In addition, if personal casualty and theft gains exceed losses, each gain and loss is treated as a capital gain or loss, and is split into short- and long-term elements.

In Figure 2, the long-term capital transactions include the $2,100 net personal casualty gain and the $400 net IRC Sec 1231 gain (which includes the $1,500 net business casualty gain). The short-term capital transactions net to a $3,600 loss, and the long-term capital transactions net to a $1,100 gain. Since the results have opposite signs, they are combined, and the net result is a $2,500 loss, deductible against ordinary income.

In Figure 3, the revised personal and business casualty loss assumptions cause both to net to a loss; therefore, neither affects the capital gain and loss netting. As a result, the long-term capital transactions now net to a $1,400 loss, and when combined with the $3,600 short-term loss result totals $5,000. Only $3,000, the maximum offset to ordinary income, may be deducted (short-term losses first) in the current year with the balance carried forward indefinitely.

Ordinary Income. The final step in the netting process is to close the ordinary income column. Before that step can be completed, AGI and the 10% of AGI reduction in net personal casualty and theft losses must be calculated. Only the loss remaining after reduction is available as an itemized deduction. This is the case in Figure 3, where the $6,900 net losses are reduced by $4,340 (10% of $43,400), resulting in an itemized deduction of $2,560.

The fifth column of the spreadsheet can be used to compute final taxable income by including all other sources of income and deduction. In Figures 2 and 3, Schedule C income of $52,000, interest income of $1,400, a deduction for AGI of $4,200, an exemption deduction of $2,450 (1994), and itemized deductions of $8,200 (exclusive of personal casualty and theft losses) are incorporated so that a final taxable income figure can be computed.

Relationship of Spreadsheet to Reporting Procedures

The five netting processes described above are incorporated on Forms 4684 and 4797, Schedule D, Schedule A, and the basic Form 1040 or 1120 for reporting purposes. Figure 1 references netting results to these forms. The spreadsheet may be used as a quick and accurate method of determining the final results of a number of property transactions and may serve as a check on the results reported on the various tax forms.

Planning Spreadsheet Template

It is a simple matter to construct an electronic spreadsheet to automate the netting procedure described. The key is to use a series of "@IF" statements for each place where a previous column result could be transferred. For example, a cell in the IRC Sec. 1231 column would ask if the business and investment casualty and theft netting resulted in a net gain; if the answer is yes, the result should be transferred to this cell. On the other hand, if the answer is no, the value of the cell should be zero.

The template is available upon request of The CPA Journal‹

The CPA Journal

530 Fifth Avenue

New York NY 10036-5101

e-mail: cpaj@luca.com

William A. Duncan, PhD, CPA, is an associate professor of accounting at Arizona State University­West Campus and John O. Everett, PhD, CPA, a professor of accounting at Virginia Commonwealth University.

AUGUST 1996 / THE CPA JOURNAL