Cost Basis Reporting Is Tricky
One of my pet peeves in building portfolio management software was tracking cost basis. Why? Because in the many systems I built, I don’t think I ever designed it right. A lot of my confusion stemmed from the fact that it took ten years for me to realize there are only two real methods of calculating cost basis: average and specific lot.
But what about LIFO and FIFO you ask. Highest and Lowest? Well, my friends, they’re all derivatives on specific lot accounting. So if you can first design your system so it tracks average and specific lot, then you’re done. You may want to add some power tools to allocate the lots using FIFO, LIFO, etc., but once your system can handle specific lots you’re essentially finished. Most people don’t understand this–I tried to explain it a couple years ago in a financial planning class and only one person (a CPA) understood what I was talking about.
Most of the portfolio management systems I’ve seen implement everything else but save specific lot for last; this indicates cost is being tracked wrong. It means they’re storing the calculation type (FIFO, LIFO, etc.) and not storing the specific lot allocations. Techfi did this and I believe dbCAMS and Centerpiece do the same thing. It ends up being a huge pain to work with because you essentially have to recalculate the cost every time you need it. It’s a headache from a development standpoint and slows your system when printing reports.
The right way to approach this is to build your specific lot cost *first*, including the edit screens and storage tables for your lot mappings. It’s harder, but once that’s done, you can strap the other methods onto it as an afterthought. Ah, hindsight…
This rant was triggered by something interesting I read today on Investment News:
The president’s fiscal 2008 budget proposal, presented to Congress last Monday, includes a legislative proposal that would require financial institutions to track investors’ cost bases in stocks, bonds and mutual funds. It also would require them to transfer this information when customers moved their accounts to new firms and to report the cost bases to the customer and the Internal Revenue Service.
This will certainly make life easier for all the portfolio management vendors out there. I think portfolio management systems would still have to calculate cost, but one of the big headaches for advisors is clients transferring accounts from one custodian to the other. There isn’t a great way to make this happen in a portfolio management system and it ends up requiring a lot of manual data entry to move the cost over correctly. Having the *old* cost download from the *new* custodian will eliminate the headache.
There’s the additional problem of the custodian not having all the information they need to calculate cost correctly:
“There could be situations where brokers have to rely on information provided by shareholders,” such as when securities are given as gifts to others, he said. “There are a lot of issues here that need to be sorted out.”
Despite the problems, the measure will probably pass because it means an additional $6.7 billion in tax revenue over the next 10 years. Lucky us, I suppose.
Bill Ramsay said,
February 13, 2007 at 11:14 pm
Funny that I just looked at that same news item in Investment News 10 secs before coming to Wealthfly today.
You are so right about the lack of understanding with cost basis particularly with the PM vendors. I tried to explain it to Dusty at DBCams, but they still did a horrible job of impletmenting it (he actually said it wasn’t important because he wasn’t getting requests for better tax accounting from users). With PortfolioCenter, I thought they would have redesigned tax lot accounting when they changed to SQL, but from what I can tell they migrated their design flaws right on over.
The folks at Poweradvisor did a pretty good job on the basic design- the best of any of the systems I’ve seen. As you point out there are a lot of little tools and processes to make it friendly, which even PA is short on.
If custodians are required to keep that info, there will still be some needed pieces in a portfolio management system, for one to deal with lot specification when trades are made. Ideally for us, we want to be able to see the gain/loss impact of recommended changes, and then have the lots we picked communicated in the trade communication to the custodian, since the IRS says its FIFO unless communicated to the broker at time of execution.
You also have to have a “lock down” when average is used, since you can never go back with a fund once you’ve used average.
You could also solve the problem of cost basis transferring when assets move from one custodian to another, or when gifted, if positions are not one to one with accounts. Instead, if you use something like a location table with date in/date out, trade lots would be associated correctly with the position rather than account. They also wouldn’t blink out of existence during transfer.
Matt Abar said,
February 14, 2007 at 1:35 am
I’ll have to take a look at PowerAdvisor. I’m not surprised most PMS systems aren’t making changes to the cost basis. Until you get into the mindset of “everything is specific lot” with the lots being assigned at the time the transactions are created, it’s one of the scarier pieces of code. To add a new cost method essentially means re-writing it from scratch and it’s a pretty big project.
At Techfi definitely, we were scared to go poking around in the cost module. Not only was it originally written by me (scary enough), but we didn’t have it locked down with unit tests, so we had no idea whether a change we made would cause something else to break. And if you break cost basis, every one of your users knows. Well, every one who looks at their reports at any rate.