Tax Lots

If you have tax lot information and current prices, you can optimize by tax lot. The Optimizer tracks the impact of gains and losses and applies them as an additional adjustment to the return of the portfolio, like a transaction cost. The difference is that the tax lots can have a positive effect on the return of the portfolio given that there are sufficient losses. If you do not apply capital gains constraints, the Optimizer sells losers and holds on to winners. If you want to hold some losses for later, utilize the capital gains constraints to limit the losses taken at this time. Be aware that tax lots and the capital gains constraints act in concert with any other constraints you may apply, so be careful to watch for conflicting constraints. (For an example, access the Vanguard Tax case in the samples directory.)

Overview of Optimizing with Tax Lots

1. Begin by enabling Tax Lots in the Taxes Menu.

2. Create a tax lots CSV file.

3. Import your tax lots CSV file.

4. Review the Tax Lots Worksheet.

5. Optimize.

6. Review the results.

Create a Tax Lot CSV File

The only way to enter tax lots information is to create a CSV file and import it.

1. Open a spreadsheet.

2. Enter the column headings from the table below.

3. Complete the spreadsheet with tax lots information.

4. Include all assets in the investment universe.

   • To include price information for assets not included in the initial portfolio, add an entry with a valid price, but no shares or basis. For example, if the initial portfolio contains no Japanese stock, but you included Japanese stock in the optimization problem, you should include a tax lot for Japanese stock in order to import the current share price and tax rate.  

   • Alternately, wait for the Optimizer to create blank tax lots for these assets during optimization. The Optimizer automatically assigns a price of $1/share for assets excluded from the tax lots file. You may then export the tax lots information, change the prices and tax rates, and re-import the tax lots file.

5. Save the spreadsheet as a *.CSV file.

Import a Tax Lots CSV File

Select the Import Tax Lots Option from the Taxes Menu.  The Import Tax Lots from CSV Window appears.  Navigate to where you saved the CSV file.  Click the Open Button.  The tax lots file populates the Tax Lots Worksheet.  

• The Optimizer creates tax lots with $1 prices and 0% tax rates for every asset in the investment universe that was not included in the CSV file.  If this occurs, the Optimizer displays a warning.  We recommend exporting the tax lots file, filling in the missing information, and re-importing.

• The Optimizer assumes that the tax lot listing is complete, so importing a tax lot file overwrites the initial portfolio.  Therefore reviewing the initial portfolio is an excellent method for checking the completeness of your tax lot information.  

• Tax lot files must match the current investment universe.  The Optimizer will not accept CSV files that include assets outside of the investment universe.

• If you have importation problems, try opening your CSV file in Notepad.  This simple viewing method can identify oddities in how your spreadsheet saved as a CSV file.

• After importation, the Optimizer displays the CSV data on the Tax Lots Window in the following columns and fields:

• • Asset Name --  AssetID from the imported file

  • Asset Description -- populates according to the AssetIDs

  • Tax Lot ID -- LotName from the imported file

  • Tax Lot Purchase Date -- BuyDate from the imported file

  • Tax Lot Description -- LotDescription from the imported file

  • Current Asset Price -- Price from the imported file

  • Initial Tax Lot Shares -- Shares from the imported file

  • Initial Portfolio Basis -- sum of the basis reported in the imported file

  • Initial Tax Lot Basis -- Basis from the imported file

  • Initial Portfolio Value -- sum of the Initial Tax Lot Value column

  • Initial Tax Lot Value -- product of Initial Tax Lot Shares and Current Asset Price

  • Unrealized Gain Loss -- the difference between the initial portfolio basis and the initial portfolio value

  • Unrealized Tax Lot Gain Loss -- the difference between the initial tax lot basis and the initial tax lot value columns

  • Percent Unrealized -- the unrealized tax lot gain/loss as a percentage

  • Embedded Total Tax -- the unrealized tax on the entire portfolio, the product of the tax rate and the gain/loss

  • Embedded Tax -- the unrealized tax, the product of the tax rate and the unrealized gain/loss

  • Effective Tax Rate -- TaxRate from imported file

Capital Gains Tax Bounds

Capital Gains constraints restrict the amount of realized capital gains tax to within the set range.  For example, if you want to pay between $1000 and $2000 in capital gains taxes for the year, enter "1000" in the Capital Gain Tax Lower Bound Field and "2000" in the Capital Gain Tax Upper Bound Field.  

Optimizing with Tax Lots

After reviewing the Tax Lots Worksheet for completeness and setting the rest of your investment problem, optimize.  After optimization, you can slide through the optimal portfolios in the top left of the Tax Lots Worksheet in order to view the tax implications.  

Results of Optimizing with Tax Lots

After optimization, new lots appear for each recommended buy, and the following fields populate with information concerning the tax implications of the currently selected optimal portfolio:

• Optimal Shares -- the number of shares from each tax lot contained in the currently selected optimal portfolio

• Shares Traded -- the difference in shares per tax lot between the initial portfolio and the currently selected optimal portfolio

• Optimal Value -- the optimal number of shares of the portfolio or tax lot multiplied by the current price

• Capital Gain Tax -- the amount of tax that would be paid on the optimal portfolio

• New Basis -- the basis of the portfolio or tax lot after the trades that would result in the optimal portfolio take place  

Export a Tax Lots CSV File

Exporting a tax lot file allows you to make changes such as updating the prices, move tax lots into a different Optimizer case, or save tax lot information for a future Optimizer case.  (When you save a complete case, tax lots are included, but you may wish to save tax lots separately.)  Select the Export Tax Lots Option from the Taxes Menu.  The Export Tax Lots to CSV Window appears.  Navigate to where you wish to save the CSV file.  Click the Save Button.  The Optimizer creates a CSV file that includes all assets, even those that were removed from the investment universe in the Asset Selector.  

Feedback

This is a beta version of the tax lots feature.  We welcome your feedback.  Please contact your relationship manager or e-mail support@newfrontieradvisors.com.