XLeratorDLL/financial is now available
Aug
21
Written by:
Charles Flock
8/21/2016 3:26 PM
XLeratorDB was developed to provide the best possible in-database analytics capability for SQL Server and we continue to believe that the best platform for doing sophisticated analytics on large datasets is SQL Server with XLeratorDB. Over the last 8 years, however, non-SQL Server users were also interested in having many of these functions and even some SQL Servers preferred the XLeratorDB functions in non-database applications. We are happy to announce the availability of XLeratorDLL/financial and in this article talk about the advantages of using this library in your .NET code and why should really never use Microsoft.Office.Interop.Excel,Worksheetfunction for financial calculations.
While we still think that in-database analytics using XLeratorDB is the best way to perform these types of calculations, if you are not a SQL Server user you wouldn't have access to a library with many of these functions. XLeratorDLL/financial liberates all the functions that you loved in XLeratorDB/financial from your SQL Server database. XLeratorDLL/financial (XLDLL) can be called from any .NET program. XLDLL contains 249 sophisticated financial functions for calculating internal rates of return, time value of money, bond figuration, yield curve construction, etc.
You might ask yourself why is using XLeratorDLL_Financial better than calling Excel functions using Microsoft.Office.Interop.Excel, especially since Microsoft.Office.Interop.Excel comes with .NET? There are a whole bunch of technical reasons why you might prefer one technology over the other but those are arguments for someone more technically-inclined to make. I think that the real benefits comes down to these:
1. XLeratorDLL_Financial is much faster than Microsoft.Office.Interop.Excel.
2. XLeratorDLL_Financial is much more reliable than Microsoft.Office.Interop.Excel.
3. XLeratorDLL_Financial better reflects industry practices for financial calculations.
4. XLeratorDLL_Financial has way more functions which permit you to do more complex financial calculations than the Microsoft.Office.Interop.Excel
In the these related blogs we have compared the XLeratorDLL (XLDLL) functions to Excel functions in some detail to these three Excel function: XIRR; YIELD; and XNPV....
Speed
We found that:
· XLDLL XIRR runs 50 times faster than the equivalent WorksheetFunction method;
· XLDLL XNPV runs 50 times faster than the equivalent WorksheetFunction method;
and
· XLDLL YIELD runs 18 times faster than the equivalent WorksheetFunction method.
Reliability
We found the following problems with the WorksheetFunction XIRR method.
· First, in many cases it returns zero as a solution when XLDLL returns the correct solution. It is highly unlikely that the XIRR for a series of cash flows is zero unless the sum of the cash flows is zero, yet we have no problem getting the Excel version to return a zero when the sum of the cash flows is zero.
· Second, we get different answers in the Excel version if we change the order in which the cash flows are passed into the function.
· Third, in some case when we use the Worksheet XNPV method to create a cash flow, we cannot get the XIRR WorksheetFunction method to return the discount rate used in XNPV.
For the XNPV method we actually found that the Worksheetfunction method is implemented with the wrong signature; instead of having the three input parameters that you find in Excel, the Worksheetfunction method only has 2 parameters.
· Thus you need to use the Evaluate method to call XNPV increasing the coding complexity as well as requiring instantiating workbooks and worksheets in your .NET code if you have more than 255 cash flows.
· We also found the you cannot pass a negative discount rate into XNPV (which might seem like a big deal until you have to do it) and that the cash flows actually have to be in date order (requiring you to sort the input data).
Finally, for the Worksheetfunction method YIELD what we discovered is that it is not, in fact, a Worksheetfunction method. As with Worksheetfunction method XNPV you needs to create string and call the Evaluate method.
· Worksheetfunction method YIELD will not accept a negative coupon rate (tough luck if you want to price those Bank of Japan or European Central Bank issues).
· Interestingly, the Excel implementation of YIELD will return a negative value, which if you then try to use in the Worksheetfunction method PRICE causes an error.
· In some cases, the Excel implementation of YIELD will return a value that when passed into Worksheetfunction.PRICE does not return the price value originally passed into YIELD
· We also found that the error handling for WorksheetFunction method YIELD is completely different than the error handling for XIRR and XNPV.
Of course, none of these limitations apply to XLeratorDLL.
Industry Practice
XLeratorDLL reflects our continued investment in making sure that you have the algorithms that you need for 21st century financial products. If you want to calculate internal rates of return using a 30/360 basis or an Actual/Actual Basis, or pretty much any variation you can think of, XLDLL provides that capability. Of course, the same flexibility exists in the XNPV calculations. If you want to calculate the price or yield for bonds with monthly coupons, or coupons that pay interest every 28 days, or bonds that have an odd first and odd last coupon period you can do those calculations easily using in XLDLL. If you are using the Worksheetfunction methods; good luck. Those methods just don't exist and now you are forcing your developers to have an MBA in finance.
More Functions
XLeratorDLL/financial has 249 very useful financial functions; Excel has a little more than 50 (including functions like AMORDEGRC and AMORLINC which have something to do with French amortization and depreciation, BAHTTEXT which converts a number to Thai text and adds a suffix of "Baht", and ISPMT which is maintained for consistency with Lotus 1-2-3; in other words 50 financial functions is a generous estimate).
If you want to do things like calculate the continuously compounded zeroes from the swap curve XLDLL has a function for that; calculate the periodic payment for an annuity with an odd first period; XLDLL has a function for that; create an amortization schedule for bond a loan using the interest method; XLDLL has a function for that. You get the idea.
It comes with programming samples in C# and VB.NET for each of the functions and only takes about a minute to download and about a minute to install. The documentation for the functions can be found on the website and it won't take more than 15 minutes to get an example up and running in your environment.
If you have any problems or questions or if there is something you would like to see added to the library, send us an e-mail at support@westclintech.com.
