AssetBook had a major headache. AssetBook is the developer of Radar which is portfolio management software for registered investment advisers and they were spending too much valuable development time de-bugging and supporting their own version of XIRR, an important calculation in measuring money-weighted rate of return (more on the challenges of coding XIRR below). AssetBook found their ‘aspirin’ in XLeratorDB from Westclintech which produced rock solid numbers quickly and was easier to support. Plus Westclintech had two separate XLeratorDB products which offered AssetBook the flexibility of either running XIRR natively on SQL Server or as a .NET DLL. Once AssetBook implemented XLeratorDB’s XIRR function they could focus valuable development time on growing and supporting their product. This case study provides further details on AssetBook’s decision to purchase XLeratorDB.
AssetBook’s Radar product is portfolio management software designed for the demanding and competitive world of registered investment advisors. The Radar platform includes personalized dashboards to help the advisor’s customers visualize their data. The flexibility of the Radar platform demands efficient processing, including on-the-fly calculations of client’s internal rate of return. Radar runs exclusively as a SaaS portfolio management solution on servers hosted in the Microsoft Azure cloud.
Challenges of coding XIRR
You can face many cascading challenges when coding XIRR to calculate internal rate of return or annualized yield for a schedule of cash flows occurring at irregular intervals. There is no closed-form solution for calculating an XIRR, so you must implement some root-finding technique. If you choose bisection, for example, you might find that it is too slow and it can hop over a solution. Generally the Newton-Raphson method is a reliable technique though you need to handle its limitations. The Newton-Raphson solution is directly affected by its starting point and there is really no good way to come up with a rule of thumb for the initial guess. Based on the guess, you might actually iterate away from a solution towards infinity or negative infinity. Or, because of the limitations of floating point math you could oscillate around a solution without ever resolving to a solution within the error tolerances.
It's also possible that based on the starting point the resolution can be quite slow (perhaps thousands of iterations). Further, it's also impossible to make any assumptions about the signs of the cash flows (for example, you cannot require that the first cash flow be negative) or the sign of the slope of the underlying function (XNPV) in the region in which you are searching for a solution.
These are just some of the challenges in coming up with an XIRR function that’s not only accurate but capable of processing millions of cash flows in just a few seconds. Westclintech has done the hard work and its XLeratorDB product is easy to implement, produces rock-solid results, and can process millions of rows of cash flows in just seconds.
