The Tax Spreadsheet

Updated 07/25/08

FastTrack will create a spreadsheet for any issue in the database that shows,

• long-term capital gains,
• super Long-term cap gains,
• short-term capital gains,
• dividend earned,
• the reinvestment of distributions,
• splits,
• distributions of shares in other companies.
• average share price of funds.
• specific share prices for stocks.

Only $150

Call (866) 295-0166 for quick delivery via e-mail (allow time for credit card processing).

The tax spreadsheet automates the devilishly task of computing your taxes on long held positions that have split, spun off shares in other companies, or in which you have reinvested distributions. FT's computations follow the guidelines of IRS form 550,   this publication is available on line.

Many investors overpay the IRS thousands of dollars . . . and because the computation and information collecting can be impossible without FT. This is ESPECIALLY true for long-term stock investors. The temptation is to use the most recent purchase price as the cost basis as if it were the price 10+ years ago. This results in dramatic over payment. Just as bad, using the original  shares price as the basis for all shares, generally, produces a dramatic underpayment and whopping penalties. You cannot rely on your CPA or the government catching the over/under payment. Worse, the IRS may claim you underpaid without any evidence AND require you to prove your deductions.

With FastTrack's Tax Spreadsheet you can at least get a good approximation of taxes due as a sanity check against your broker or CPA's figures. Also, the FT Tax Spreadsheet provides proof of substantial effort on your part to calculate taxes properly.

The tax spreadsheet is written to your hard disk drive in the file:

C:\FT\FT4WIN\TEMP\TAX.CSV

The file is written each time you move the dashed pole to the extreme right-most edge of the chart. A small capital gains summary appears on the screen. The file is continuously overwritten, therefore, load and save spreadsheet under a different name if  you wish to keep it.

Your Excel must be set up in reference style A1 (standard, default style), not R1C1 style. Change the style in the Tools/Options/General Tab. Uncheck the R1C1 check box if it is selected.

Disclaimer

This is a new feature as of August 1999. I devised it for my own purposes based on information from several tax advice sources. The spreadsheet provides you with the basic information needed to figure out your taxes on long-terms holdings, but does require customization to get to the final tax figure.

Feedback from experienced Tax Planners is welcome. I claim no special expertise or qualification in this area, as will be making changes based on your comments and support questions to paul@fasttrack.net. I will refine this help section as questions arise with the goal of making the comments understandable to competent tax preparers to save time, money, and aggravation in the preparation. I cannot undertake to give you specific tax advice, but can explain by e-mail and in this help how the spreadsheet data and computations were done.

Best returns, Paul Charbonnet

Tax Computation for FMAGX Fidelity Magellan/021 for 09/01/1988 - 08/04/1999 on 08-05-1999

Many distribution lines were removed from this example for brevity.

The "Avg Share Cost" is equivalent to the "Adjusted Basis" described in IRS Publication 550. The basis of stocks or bonds you own generally is the purchase price plus the costs of purchase, such as commissions and recording or transfer fees. You will have to enter these additional costs, if any into the FastTrack Tax Spreadsheet in the total cost column. If you acquired stock or bonds other than by purchase, your basis is usually determined by fair market value or the previous owner's adjusted basis. You will have to make that change the the FT Tax Spreadsheet.

The "Total_Costs" is the "Cost Basis" described in Chapter 4 of IRS publication 550. You must add commissions and other costs to this amount. The basis of securities you buy is usually its cost. The cost is the amount you pay in cash, debt obligations, or other property or services.

This example does not show adjustment for stock splits, however, the FT tax SpreadSheet adjusts the basis for splits and certain events that occur after purchase. FastTrack also reduces the basis when for certain nontaxable distributions considered to be a return of capital.

Tax Reporting

There are two ways to sell long-term shares. You can sell shares based on an average price, or you can sell shares based on actual cost. The spreadsheet provides basic information to assist in this process. HOWEVER, the spreadsheet in NOT the ultimate answer to the problem. Since you added to or reduced your original position over the years, the spreadsheet will not provide exactly the right answer, but it is a good start. If you are modestly spreadsheet capable, you can modify the FastTrack Tax Spreadsheet with you own entries to obtain the correct answer. See the Modifying the Spreadsheet discussions below. This is not difficult, and be broken down into cookbook steps. You must be able to enter the right constants. You do not need to write custom formulas.

The Concept of a Tax Lot

When you buy shares of a new fund, these shares and subsequent reinvested distributions form a "tax lot". The Tax Lot is completed the first time you sell shares from the lot using the Average Share Price Method. The Tax Lot can remain active, although with a new starting point, when you sell shares using the "Specific Shares method".

FastTrack does NOTHING to keep track of your tax lots. FastTrack will generate spreadsheets from/to any starting/ending point. You enter the appropriate starting numbers into the FastTrack-created spreadsheet and the save the spreadsheet under a name you choose to constitute the tax lot.

Stocks and Bonds

The basis of stocks or bonds you own generally is the purchase price plus the costs of purchase, such as commissions and recording or transfer fees. If you acquired stock or bonds other than by purchase, your basis is usually determined by fair market value or the previous owner's adjusted basis as discussed earlier under Basis Other Than Cost.

The basis of stock must be adjusted for certain events that occur after purchase. For example, if you receive more stock from nontaxable stock dividends or stock splits, you must reduce the basis of your original stock. You must also reduce your basis when you receive nontaxable distributions, because these are a return of capital.

Specific Shares of Stock Method of Selling

Stock cost basis calculation is different from mutual fund cost basis calculation. See the section below for mutual funds. The consequences of poor record keeping and difficult calculating the cost basis are MUCH MORE SEVERE for long-term holders of stocks than for fund holders.

If you can adequately identify the shares of stock you sold

The  basis is the cost or other basis of the particular shares of stock or bonds. This is not too difficult if you have your certificates delivered to you, but for active investors that is not a reasonable way to do business. For shares held by the broker, the broker's statements along with the FastTrack Tax Spreadsheet will provide that information.

When calling your broker, specify the number of shares to sell and ask for the proper address to send a written notice regarding specific shares to the broker. The broker DOES NOT file this information with the IRS, but they do keep it to support your case if the IRS challenges your deductions.

You do not need to know the details of which shares your are selling at the moment you sell. but you should follow up with the written notice within a few weeks. The notice must includes the sell date, sell price, the purchase date and price. Keep a copy of the letter to support your income tax return , but do not include the letter with your 1040.

If you buy and sell securities at various times in varying quantities and you cannot adequately identify the shares you sell

The basis of the securities you sell is the basis of the securities you acquired first. This method for computing costs is a TAX DISASTER when you have been continually acquiring shares of a rapidly growing company. You will have to take the maximum capital gains on the sales based on the earliest purchases. Instead, if you identify the shares with the highest cost basis and sell those, you will be able to later gift or pass on the high capital gain shares tax free as gift to children or as assets of your estate.

The spreadsheet will give you guidance regarding the number of shares you have been buying and holding all those years so that you can specify which shares to sell

Adequate identification.

You will make an adequate identification if you show that certificates representing shares of stock from a lot that you bought on a certain date or for a certain price were delivered to your broker or other agent.

Broker holds stock. If you have left the stock certificates with your broker or other agent, you will make an adequate identification if you:

1. Tell your broker or other agent the particular stock to be sold or transferred at the time of the sale or transfer, and
2. Receive a written confirmation of this from your broker or other agent within a reasonable time.

For Mutual Funds Use the Average Share Cost Method of Selling

This method is described in IRS publication 564. This is easy to do although you cannot control your taxes as easily as when selling specific shares. Report the average cost of the shares from the FT Tax Spreadsheet "Avg Share Cost" column, "Last" row. This average cost will not change and you can continue to report this value for years providing that you STOP reinvesting distributions immediately. Reinvestment will change the average price.

If you decide to resume reinvesting/buying more shares in the future, then these purchases are a new "tax lot". Do a separate spreadsheet for these shares and DO NOT include these shares in calculations for the old tax lot.

The IRS will NOT allow you to change your method of reporting share cost in the old tax lot once you have started using the average cost.

If the investment has gained and is likely to continue gaining value over the years and if your retirement deductions will likely reduce your total assets over the years as you sell, use Average Share Cost. This will shift share costs (tax deductions) into the present (early retirement) while you still may have other income. In late retirement, with less other income, you will need fewer deductions.

Modifying the Spreadsheet

Do not skip around making changes in the spreadsheet through all the columns. Modify only the columns noted below.

The spreadsheet will not exactly agree with your brokerage statements.

To be exact, modify the SpreadSheet Start line:

1. The "Amount" should be the price paid for each new share.
2. The Shares_Purchased must be the total number of shares purchased
3. The "Total_Cost" is the "Total Cost" must include all sums paid including commissions.
4. The "Total_Assets" must be the value of the assets after purchase cost and expenses.
5. For each distribution, the Date, Amount, and Close Price (i.e. the purchase  price) columns on the sheet may need be modified to agree with your brokerage statement. For funds, FT's values will be very close. For stocks, the purchase price and reinvestment date will be different. Although FT's information will likely be acceptable.

The spreadsheet makes no provision for trading

The easy way to modify the spreadsheet is to create a new sheet after trading shares. Create a new spreadsheet with the starting date of the recent transaction. Change the "START" row of the new sheet:

1. The "Amount" should be the price paid for the new shares.
2. The Shares_Purchased must be the total number of shares in the tax lot after the most recent transaction. This is a simple addition problem.
3. The "Total_Cost" is the "Total Cost" from the "Last" row of the previous spreadsheet plus the cost of the recent transaction. This is a simple addition problem.

Distribution of Shares in the stock of another company

FastTrack calculates the value of the shares using one of several  appropriate methods and reinvests the "fair market value" of the new shares in the shares of the original company. Depending on your handling of the distribution, you may have to set the "Shares Purchased" to 0 and reduce "Total Assets" column. These spun off shares would constitute a new tax lot for a separate spreadsheet.

Fair market value. This is the price at which the property would change hands between a buyer and a seller, neither being forced to buy or sell and both having reasonable knowledge of all the relevant facts. FastTrack uses the closing prices of the spun off company shares, around the date they were issued, in figuring fair market value

Tax Reform of 2001

This law introduced the concept of "Super" Long-Term Capital gains. Some FT dividends paid by mutual funds are of that type and are listed separately in the tax spreadsheet. Also, when the buy is after 1/1/2001 and the sell is more than 5 years later, then the gains from the sale are listed separately.

The rate for most investors was reduced from 20% to 18% for assets purchased after January 1,2001 and held for five years. There is also a provision for converting assets held before January 1,2001. Also, note that the tax rate is lower still for investors in lower income brackets. Consult your tax advisor. 

A Final Word of Advice

Your IRS auditor may be kind to children and small animals, but he is not a tax advisor, and he isn't your friend. Don't take a FT Tax Spreadsheet into an IRS audit. Don't put it on the table in front of him. Be sure you fully understand if the sheet supports your position and is correctly calculated considering your circumstances . . . NOTHING beats the representation of a competent tax advisor in an audit situation.