Automating Capital Gains Tax Reporting at a Major Wealth Management Firm
Eliminating a recurring manual burden through an automated reporting system.
Note: Case studies shown are a combination of Northern Analysis engagements and professional projects from our founding team’s careers. No confidential, proprietary, or sensitive information is disclosed. Certain details may be generalized or omitted to protect client, employer, and stakeholder confidentiality.
Summary
A wealth management group managing a $3 billion portfolio was spending approximately 160 hours per year, or 40 hours per quarter, manually producing and reviewing capital gains tax reports for hundreds of clients. The reporting would be done one client at a time, with a comprehensive peer review process required to catch errors before distribution. The firm’s secure banking environment ruled out third-party tools or new system deployments. Arnav developed an automated report generation system using a combination of macros and mail merge, keyed to each client’s taxpayer identification number. The system used an algorithm which aggregated and generated accurate, individually addressed reports for every tax entity in the portfolio. It eliminated the manual production process, removed the conditions that created reporting errors, and saved 120 hours of team resources annually.
Situation
Each quarter, the wealth management team was required to deliver capital gains tax reporting to clients and their CPAs. This was accomplished individually, across hundreds of clients and thousands of accounts spanning individuals, trusts, foundations, and other tax entities. The process required pulling data for each taxpayer, manually compiling the relevant figures, producing a formatted report, and routing it through a peer review process before distribution. With the size of the client base, this was a significant quarterly burden. It consumed significant team bandwidth and carried the risk of errors.
The banking environment offered no native solution. The secure banking infrastructure restricted the deployment of outside tools or new systems. There was no existing platform that could be connected to automate the workflow. Any solution had to be built within the constraints of what was already available, which were basic data exports and Microsoft office products.
Solution
Arnav developed a solution using aggregate data exports from the banking system as the base. The previous system — processing one client at a time –- was reduced to a few steps for hundreds of clients, with no possibility of error.
Using macros and mail merge, an algorithm processed the data into the necessary fields, sorted by tax identification number (TIN). Capital gains tax data for each TIN were calculated. Then, the calculation was linked to name, client, and CPA contact information. That was then used to generate automatic reports and emails.
This also removed the possibility of manual error in the chain, requiring no transcription or manual input. Any mistake would have resulted in an incorrect tax payment, which would reduce the group’s credibility and require extra payments from the client during tax filing.
Results
The system saved 120 hours of team resources annually, removing one of the group’s most labor-intensive obligations. Previously, the team’s analysts and associates would have to report one client at a time, continuously double checking each step of work, each quarter. This replaced the prior method with an automated output that could be reviewed and distributed as a batch.
It also removed manual error by replacing the prior step-by-step per-client workflow. These reports were used to determine the client’s tax payments. Any mistakes would result in an incorrect payment, and an unexpected financial obligation at tax filing time. Accuracy was essential to protect clients from unexpected tax obligations and to maintain the group’s credibility.
