Tracking investment Cost Basis (ACB)
Typically investment cost basis can be calculated using one of three methods: FIFO (first in first out), LIFO (last in first out) or Average. Portfolio Slicer currently calculates Cost Basis using "Average" method. With this method for each sale transaction we calculate average cost basis per share using information about all previous buys, sells and distributions that affected cost basis.
The best way to see how Portfolio Slicer calculates Cost Basis is to review "TransInfo" report. This report will show how each transaction impacts Cost Basis.
Different transaction types have different impact on cost basis:
- Transactions types that increase cost basis are: "Buy", "BuyTA", "DRIP", "NotionalDistrib" and "SymbolTransferIn". For these transaction types CostBasisImpact formula is very simple – it is equal to transaction total amount (column TotalAmnt).
- Transactions types that decrease cost basis are: "ReturnOfCapital", "Sell", "SellTA", "SymbolTransferOut" and "SymbolTransferOutAsSale". Cost basis formula for transaction type "ReturnOfCapital" is equal to total transaction amount (column TotalAmnt) signed negative (because this decreased cost basis). Formula for other 4 "sales" transaction types is more complicated and is described below.
- Transaction type "Split" does not change total cost basis, but because it changes number of shares/units held, it can increase or decrease cost basis per share/unit, depending on the quantity sign.
- Other transaction types have no impact on cost basis (example regular dividends). In such case CostBasisImpact column value will be empty or 0.
When calculating Cost Basis impact for sales transactions Portfolio Slicer use following routine:
- First Portfolio Slicer calculates how many shares of that symbol in that account we have for current transaction date. For this calculation we check all buy and sales transactions prior current transaction date and then we add all buys for current transaction day. When you have multiple buy/sell transactions on the same day, all of the "sale" transactions of that day will have the same cost basis per unit. This calculation is stored in column QtyHeldBeforeSale (column could be hidden).
- Then Portfolio Slicer calculates total Cost Basis Amount by adding all CostBasisImpact values for that symbol in that account that have transaction date less than current sales transaction date and also adds all CostBasisImpact values for that symbol in that account that have transaction date the same as current sales transaction date just for "Buy" transaction types. Please note that all these calculations are done by first converting amounts into reporting currency. Dividing Cost Basis Amount by QtyHeldBeforeSale calculated in 1st step gives us Cost Basis Impact per Unit value. And multiplying Cost Basis Impact per Unit value by number of shares/units sold gives us total CostBasisImpact value for current sales transaction.
Overriding Portfolio Slicer Cost Basis calculations
In Transaction table there is column “CostBasisOverride” that could be used to tell Portfolio Slicer to ignore its formulas and use your specified value. Column CostBasisOverride is usually used for following cases:
- When you are using transaction type “SymbolTransferIn” to transfer symbols between accounts. In such case in the new account you would want to specify original cost basis that was in old account.
- When you are using your own cost basis calculation, but still want to use Portfolio Slicer for reporting
- When in the past you already reported your capital gains based on other method and now want to use “Average” method (we are not sure if that is legally allowed for tax purpose, but you have technical possibility of doing this in Portfolio Slicer).
Please note that if for transactions you will specify values in fields ExchRateRpt1Override, ExchRateRpt2Override or ExchRateRpt3Override, then these exchange rates will be used when calculating Cost Basis in that Reporting Currency. There are cases when you would want to enter your own exchange rate instead of using Portfolio Slicer provided exchange rate, for example:
- Bank/Investment institution provided you with exact exchange rate of your transaction that you can use. In such case you might prefer to use that exchange rate instead of Portfolio Slicers "end of the day" exchange rate.
- You added transaction that should use year average exchange rate, instead of "End of the Day" exchange rate. For example for the last day of the year you might add "ReturnOfCapital" distribution and use year average exchange rate for that.
- You use Portfolio Slicer just for Cost of Basis tracking and your Excel workbook is not properly configured to receive exchange rates from cloud database.
Portfolio Slicer limitations when calculating Cost Basis
Average Cost basis calculation requires iterative calculations that are not supported by PowerPivot DAX language. To go around this limitation we had to create calculations that have limitations. We believe that very few Portfolio Slicer users could be affected by this limitation and if they do, then simple fix is use CostBasisOverride to specify your own Cost Basis for affected sales transactions. You will know that you hit this limitation if calculated cost basis will be equal to 10mln - we picked large number on purpose so that it would be instantly visible in your reports. Portfolio Slicer will return this value when specific symbol was sold partially (!) more than 5 times since last complete sale. Partial sale here refers to sale when after sale you still have number of shares left. Complete sale here refers to sale when after that sale you have 0 shares left. We expect that very few (if any) Portfolio Slicer users would be affected by this.