The WestClinTech BlogCommentary from the WestClinTech function designers.
http://westclintech.com/Blog/BlogId/3
en-USjstampf@westclintech.comWed, 07 Dec 2022 21:36:35 GMTWed, 07 Dec 2022 21:36:35 GMThttp://backend.userland.com/rssBlog RSS Generator Version 4.1.0.0Creating amortization schedules for variable rate loans in SQL Server
http://westclintech.com/Blog/EntryId/146/Creating-amortization-schedules-for-variable-rate-loans-in-SQL-Server
<i><span class="MsoSubtleEmphasis"><span style="font-size: 11.0pt;line-height:107%;font-family:"Calibri",sans-serif;mso-ascii-theme-font: minor-latin;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin; mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;mso-ansi-language:EN-US;mso-fareast-language: EN-US;mso-bidi-language:AR-SA">Because there are never enough ways to generate amortization schedules. A look into the XLeratorDB PMTSCHED function, some time-value of money math, nested common table expressions (CTE), windowing functions and a CROSS APPLY.</span></span></i>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/146/Creating-amortization-schedules-for-variable-rate-loans-in-SQL-ServerFri, 04 Jun 2021 14:26:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=146Cellular Automata in SQL Server
http://westclintech.com/Blog/EntryId/145/Cellular-Automata-in-SQL-Server
<div style="margin: 0in 0in 10pt"><span color="#808080" style="font-size: 12pt; font-family: Calibri"><em>Based on Stephen Wolfram’s elementary cellular automata we demonstrate a very simple, straightforward approach to generating the cells for all 256 rules.</em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/145/Cellular-Automata-in-SQL-ServerFri, 28 Feb 2020 21:44:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=145Dice Poker in SQL Server
http://westclintech.com/Blog/EntryId/144/Dice-Poker-in-SQL-Server
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
Today we look at 2 different ways of calculating the probabilities for Dice Poker. The first approach requires knowledge of combinatorics which requires a fair amount of thinking and very little coding. The second approach, which is really a brute force approach just requires enough SQL that you know how to use the PIVOT and UNPIVOT commands.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/144/Dice-Poker-in-SQL-ServerThu, 22 Aug 2019 03:25:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=144Underdetermined Systems and Ordinary Least Squares
http://westclintech.com/Blog/EntryId/143/Underdetermined-Systems-and-Ordinary-Least-Squares
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
Multiple Linear Regression (aka Ordinary Least Squares) usually calculates the best fit for a series of independent x-value and dependent y-values. Most of these linear systems have more rows than columns which will generate a unique solution. Those systems are called overdetermined systems. In this article, we look at underdetermined systems (where the number of rows is less than the number columns) and compare how they are handled in XLeratorDB, Excel, Google Sheets, and R. You will be surprised at what we discovered.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/143/Underdetermined-Systems-and-Ordinary-Least-SquaresSun, 14 Apr 2019 14:47:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=143Fitting a gamma distribution in SQL Server
http://westclintech.com/Blog/EntryId/142/Fitting-a-gamma-distribution-in-SQL-Server
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
Based on the paper ‘<a target="_blank" href="http://westclintech.com/Portals/0/documents/Estimating-a-Gamma-distribution.pdf">Estimating a Gamma Distribution</a>’ by Thomas P. Minka we look at a very quick technique in SQL Server to estimate the alpha and beta parameters of a gamma distribution based on sample data.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/142/Fitting-a-gamma-distribution-in-SQL-ServerMon, 30 Apr 2018 14:13:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=142XLeratorDB for SQL Server 2017
http://westclintech.com/Blog/EntryId/141/XLeratorDB-for-SQL-Server-2017
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
We are happy to announce the latest releases of XLeratorDB which are compatible with SQL Server 2017.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/141/XLeratorDB-for-SQL-Server-2017Fri, 02 Feb 2018 03:56:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=141Stop using Excel
http://westclintech.com/Blog/EntryId/140/Stop-using-Excel
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
On November 24th, 2017 The Wall Street Journal published an article by Tatyana Shumsky entitled <b>Stop Using Excel, Finance Chiefs Tell Staffs</b>. On November 30th, 2017 Ms. Shumsky followed up with <b>The Finance Pros Say You’ll Have to Pry Excel Out of Their Cold, Dead Hands</b>. We thought it was probably time to weigh in.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/140/Stop-using-ExcelFri, 01 Dec 2017 04:25:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=140Exploring the Time-weighted Rate of Return calculation in SQL Server
http://westclintech.com/Blog/EntryId/139/Exploring-the-Time-weighted-Rate-of-Return-calculation-in-SQL-Server
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In this article we take a deeper look into the XLeratorDB TWRR function which is a very easy-to-use aggregate function for calculating time-weighted rate-of-return in SQL Server. But, easy-to-use doesn’t necessarily mean easy to understand. In this article we take a deeper look into the mechanics of the calculation.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/139/Exploring-the-Time-weighted-Rate-of-Return-calculation-in-SQL-ServerFri, 06 Oct 2017 02:56:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=139More Birthday Coincidences
http://westclintech.com/Blog/EntryId/138/More-Birthday-Coincidences
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
A follow-up to our article on the General Birthday Problem, we look at some more interesting calculations relating to birthday ‘coincidences’.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/138/More-Birthday-CoincidencesWed, 06 Sep 2017 02:09:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=138Generalized Birthday Problem
http://westclintech.com/Blog/EntryId/137/Generalized-Birthday-Problem
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In November 2008, we wrote about the birthday paradox. In this article, we talk about techniques to solve birthday problems where we want to calculate the likelihood of 3, 4, 5, or more people in a room having the same birthday. We particularly focus on the techniques in E.H. McKinney’s 1966 paper and show easily SQL lends itself to exact solutions for these types of problems.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/137/Generalized-Birthday-ProblemWed, 14 Jun 2017 21:47:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=137Inventory Calculations in SQL Server
http://westclintech.com/Blog/EntryId/136/Inventory-Calculations-in-SQL-Server
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In 2013 we introduced 3 XLeratorDB/windowing functions for inventory calculations: FIFO; LIFO; and WAC. In March of 2017 we added new table-valued functions to XLeratorDB/fin08 to do the same calculations as well as a few new functions to provide more detail about the ending inventory balances. In this article we go through some examples to explore the details of inventory valuation techniques and discuss the perils of triangular joins and why you probably don't want to try these calculations in native SQL.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/136/Inventory-Calculations-in-SQL-ServerWed, 10 May 2017 19:34:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=136New Case Study: Miles Software
http://westclintech.com/Blog/EntryId/135/New-Case-Study-Miles-Software
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
You should check out the <a target="_blank" href="http://westclintech.com/Case-Studies/miles-software">new case study</a> on the case study section of the site.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/135/New-Case-Study-Miles-SoftwareWed, 19 Apr 2017 14:58:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=135New Case Study: Safety National
http://westclintech.com/Blog/EntryId/134/New-Case-Study-Safety-National
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
You should check out the <a target="_blank" href="http://westclintech.com/Case-Studies/safety-national">new case study</a> on the case study section of the site.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/134/New-Case-Study-Safety-NationalTue, 04 Apr 2017 19:10:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=134XLeratorDB Case Studies
http://westclintech.com/Blog/EntryId/133/XLeratorDB-Case-Studies
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
We have added a new Case Studies section to westclintech.com.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/133/XLeratorDB-Case-StudiesFri, 17 Mar 2017 21:01:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=133Calculating Option-Adjusted Spread (OAS) in SQL Server using XLeratorDB
http://westclintech.com/Blog/EntryId/132/Calculating-Option-Adjusted-Spread-OAS-in-SQL-Server-using-XLeratorDB
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In this overview we discuss the mechanics of the calculations of option-adjusted spread for corporate bonds. This overview is not a replacement for finance texts which give a far more detailed explanation of the theory behind the calculations. It is also not a substitute for reading the documentation about the individual functions, but is designed to give a somewhat higher and more integrated view of the how the functions work together.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/132/Calculating-Option-Adjusted-Spread-OAS-in-SQL-Server-using-XLeratorDBThu, 02 Feb 2017 17:01:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=132Deconstructing the Yield Curve in SQL Server
http://westclintech.com/Blog/EntryId/131/Deconstructing-the-Yield-Curve-in-SQL-Server
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In our previous article we took a peek behind the curtain of the SWAPCURVE function gaining insight into the mechanics of turning cash rates, Eurodollar futures prices, and swaps rates into discount factors and continuously compounded zero coupon rates. In this article we attempt to do a little reverse engineering to see if it's possible to take those discount factors and turn them back into the supplied inputs or their associated spot rates.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/131/Deconstructing-the-Yield-Curve-in-SQL-ServerThu, 13 Oct 2016 21:00:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=131Constructing a Yield Curve in SQL Server
http://westclintech.com/Blog/EntryId/130/Constructing-a-Yield-Curve-in-SQL-Server
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
The XLeratorDB table-valued function SWAPCURVE takes cash, futures, and swaps rates as input and returns discount factors and continuously compounded zero coupon rates which can then be used in a variety of sophisticated financial calculations. In this article we take an in-depth look at the mechanics of SWAPCURVE calculation using SQL and other XLeratorDB functions to explain where the numbers come from. These alternative calculations are designed to be explanatory and not as a substitute for the SWAPCURVE function which will be, by far, the fastest and easiest way to generate the curve values.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/130/Constructing-a-Yield-Curve-in-SQL-ServerMon, 26 Sep 2016 18:48:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=130XLeratorDLL vs WorksheetFunction: XNPV
http://westclintech.com/Blog/EntryId/129/XLeratorDLL-vs-WorksheetFunction-XNPV
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In this article we compare the XLeratorDLL/financial XNPV function to WorksheetFunction XNPV where we encounter some aspects of the WorksheetFunction that might give you pause.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/129/XLeratorDLL-vs-WorksheetFunction-XNPVSun, 21 Aug 2016 20:30:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=129XLeratorDLL vs Worksheetfunction: XIRR
http://westclintech.com/Blog/EntryId/128/XLeratorDLL-vs-Worksheetfunction-XIRR
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In this article we take a detailed look at the XLeratorDLL function XIRR and Worksheetfunction.XIRR. You might be surprised about some of the quirks in Worksheetfunction.XIRR
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/EntryId/128/XLeratorDLL-vs-Worksheetfunction-XIRRSun, 21 Aug 2016 20:29:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=128XLeratorDLL vs WorksheetFunction: YIELD
http://westclintech.com/Blog/tabid/132/EntryId/127/XLeratorDLL-vs-WorksheetFunction-YIELD.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
In this article we compare the XLeratorDLL/financial YIELD function to Worksheetfunction YIELD where we encounter a few surprises and some things that made me scratch my head.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/127/XLeratorDLL-vs-WorksheetFunction-YIELD.aspxSun, 21 Aug 2016 20:28:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=127XLeratorDLL/financial is now available
http://westclintech.com/Blog/tabid/132/EntryId/126/XLeratorDLL-financial-is-now-available.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
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.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/126/XLeratorDLL-financial-is-now-available.aspxSun, 21 Aug 2016 20:26:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=126Is there always an IRR?
http://westclintech.com/Blog/tabid/132/EntryId/125/Is-there-always-an-IRR.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
XIRR is one of our most popular functions. In this article we look at some of the reasons why XIRR returns a NULL and why that's OK.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/125/Is-there-always-an-IRR.aspxTue, 26 Jul 2016 17:26:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=125CECL reporting in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/124/CECL-reporting-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
Current Expected Credit Loss (CECL) is coming soon. In this article we talk about new functionality that we have added to XLeratorDB to makes loan level cash flow modeling simple. Using our new table-valued function CDRCASHFLOW in SQL Server you can create extremely sophisticated models using some very simple SQL which scales up for hundreds of thousands if not millions of individual loans. If your data already resides in SQL Server, you can implement this functionality without making any changes to your existing applications.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/124/CECL-reporting-in-SQL-Server.aspxTue, 26 Apr 2016 18:06:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=124In-database Analytics in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/123/In-database-Analytics-in-SQL-Server.aspx
<br>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/123/In-database-Analytics-in-SQL-Server.aspxThu, 28 Jan 2016 22:52:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=123Using XLeratorDB in your own SQL Server functions
http://westclintech.com/Blog/tabid/132/EntryId/122/Using-XLeratorDB-in-your-own-SQL-Server-functions.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
The XLeratorDB financial library contains more than three dozen functions related to the calculation of price, yield, and accrued interest on bonds, bills, and notes. Recently, one of our customers came to us to help him solve for the yield on a bond which incorporated a tax rate. As with most requirements for financial calculations, he attached a very complicated spreadsheet which contained the underlying math used in the local market to calculate the price from yield for the bond, including the tax rate.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/122/Using-XLeratorDB-in-your-own-SQL-Server-functions.aspxTue, 15 Dec 2015 16:26:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=122The Hypergeometric Distribution in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/121/The-Hypergeometric-Distribution-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
The hypergeometric distribution and its cousin the negative hypergeometric distribution are categorized as urn problems in statistics. In this article we look at some of the math behind urn problems.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/121/The-Hypergeometric-Distribution-in-SQL-Server.aspxTue, 06 Oct 2015 20:33:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=121Calculating Percentiles in SQL Server: an update
http://westclintech.com/Blog/tabid/132/EntryId/120/Calculating-Percentiles-in-SQL-Server-an-update.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
Our original blog on Calculating percentiles in SQL Server, published in 2011, is one of the most popular pages at westclintech.com. In this article, we talk about a table-valued approach to calculating percentiles which in some situations may improve performance dramatically over the PERCENTILE aggregate function.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/120/Calculating-Percentiles-in-SQL-Server-an-update.aspxFri, 07 Aug 2015 19:33:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=120Calculating XIRR for multiple portfolios and multiple time periods in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/119/Calculating-XIRR-for-multiple-portfolios-and-multiple-time-periods-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
Calculating internal rates of return for many accounts or portfolios across many different time horizons is a laborious and error-prone task in a spreadsheet and the calculations are easily broken when new cash flows are added. In this article we look at how doing this calculation in SQL Server with just a few lines of SQL lets you calculate internal rates of return for millions of cash flows across all of your accounts or portfolios across multiple time horizons in just a few seconds.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/119/Calculating-XIRR-for-multiple-portfolios-and-multiple-time-periods-in-SQL-Server.aspxWed, 13 May 2015 20:27:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=119Internal Rate of Return versus Total Return
http://westclintech.com/Blog/tabid/132/EntryId/118/Internal-Rate-of-Return-versus-Total-Return.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
There are a lot of financial calculations that compute 'returns'. There are internal rates of return, return on investment, time-weighted rate of return, multiples of invested capital, compound annual growth rate, and many more. The good news is that you can perform all these calculations in SQL Server using XLeratorDB.
In this article we explore the difference between the two return calculations that we get the most questions about: internal rate of return and total return. You won't need an MBA in Finance to understand these examples; just follow along with SQL Server Management Studio and you will very quickly see how these calculations differ.
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/118/Internal-Rate-of-Return-versus-Total-Return.aspxThu, 23 Apr 2015 20:18:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=118Comparing the Excel XIRR function to Google and XLeratorDB
http://westclintech.com/Blog/tabid/132/EntryId/117/Comparing-the-Excel-XIRR-function-to-Google-and-XLeratorDB.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">
<p>XIRR is one of XLeratorDB's most popular financial functions. At Westclintech, we get more inquiries about XIRR than all of the other 200+ financial functions combined. To create a reliable version of XIRR for SQL Server we had to create, quite literally, millions of different test cases which were then tested against Excel.</p>
<p>One technique for creating test cases was to generate all the cash flows except the first one, then make the first cash flow equal and opposite in sign to the discounted cash flow value, or net present value, of all the other cash flows at some discount rate and check to if the internal rate of return, as calculated by the Excel XIRR function, is equal to the discount rate.</p>
<p>Mathematically that technique is absolutely sound, yet we discovered that Excel was not at all reliable in producing the expected result. In this article we share with you what we found.</p>
</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/117/Comparing-the-Excel-XIRR-function-to-Google-and-XLeratorDB.aspxFri, 10 Apr 2015 17:42:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=117Calculating a Correlation Matrix in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/116/Calculating-a-Correlation-Matrix-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">Diversification is the basis for any sound investment strategy and the heart of diversification is finding uncorrelated risk in different asset classes. In this article we show you how to do that using the XLeratorDB table-valued function CORRM.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/116/Calculating-a-Correlation-Matrix-in-SQL-Server.aspxWed, 25 Mar 2015 15:24:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=116Calculating internal rates of return using 30/360 day-count conventions
http://westclintech.com/Blog/tabid/132/EntryId/115/Calculating-internal-rates-of-return-using-30-360-day-count-conventions.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">We have added 5 new functions to help you calculate internal rates of return (IRR) for a variety of day-count methods. Used in conjunction with our other day-counting functions, you can calculate IRR using actual/actual, actual/360, actual/364, business days / 252, no-leap-year / 365 and others as well as for 30/360.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/115/Calculating-internal-rates-of-return-using-30-360-day-count-conventions.aspxTue, 17 Mar 2015 19:41:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=115Logistic Regression in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/114/Logistic-Regression-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">We have added 5 new functions in the latest release of XLeratorDB / statistics 2008. Four of these functions, LOGIT, LOGITSUM, LOGITPRED, and LOGITPROB are SQL Server implementations of the logistic regression, often referred to as the logit regression. The remaining function, VIF, estimates the variance inflation factor, tolerance, and R2 for a set of independent variables allowing you to test for collinearity.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/114/Logistic-Regression-in-SQL-Server.aspxWed, 04 Mar 2015 15:08:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=114Calculating the price of a bond with an odd last coupon in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/113/Calculating-the-price-of-a-bond-with-an-odd-last-coupon-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">Bonds with an odd last coupon will arise infrequently in any portfolio. With XLeratorDB you can be confident that when they do show up in your portfolio you will be able to do all the necessary calculations just as easily as you can for bonds that pay regular periodic interest.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/113/Calculating-the-price-of-a-bond-with-an-odd-last-coupon-in-SQL-Server.aspxMon, 24 Nov 2014 18:11:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=113Calculating yield-to-call on preferred shares
http://westclintech.com/Blog/tabid/132/EntryId/112/Calculating-yield-to-call-on-preferred-shares.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">In this article we look at shares which pay a quarterly dividend to the preferred shareholders and which are callable at some future date and discuss various techniques for calculating a yield-to-call and explain why using the YIELD function isn't one of them.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/112/Calculating-yield-to-call-on-preferred-shares.aspxTue, 18 Nov 2014 16:15:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=112New SQL Server loan functions in XLeratorDB/financial 1.13
http://westclintech.com/Blog/tabid/132/EntryId/111/New-SQL-Server-loan-functions-in-XLeratorDB-financial-1-13.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">We added 7 new table-valued functions to XLeratorDB/financial and XLeratorDB/financial 2008 that generate principal and interest payment schedules for different types of loans. This article provides insight into some of the calculations used in these new functions.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/111/New-SQL-Server-loan-functions-in-XLeratorDB-financial-1-13.aspxThu, 25 Sep 2014 22:34:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=111Calculating the price of a bond with an odd first period in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/110/Calculating-the-price-of-a-bond-with-an-odd-first-period-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">We have previously written about calculating price, yield, and accrued interest for bonds that have regular periodic coupons. However, not all bonds fit that description. Some bonds have an odd first period, meaning that the first coupon period is either shorter or longer than all the other coupons. In this article we will discuss how that affects the calculation of price, yield, and accrued interest and why you should not use the Excel functions for these types of calculations.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/110/Calculating-the-price-of-a-bond-with-an-odd-first-period-in-SQL-Server.aspxTue, 29 Jul 2014 15:32:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=110Calculating the price of a bond in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/109/Calculating-the-price-of-a-bond-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">We look at some of the math behind the calculation of the price of a bond with regular periodic coupons, talk about how yield and accrued interest are calculated, and make some comparisons between the Excel functions and the XLeratorDB functions. We also talk about 2 new function that we have added to XLeratorDB that make it easier to see how PRICE and YIELD functions came up with their results.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/109/Calculating-the-price-of-a-bond-in-SQL-Server.aspxTue, 01 Jul 2014 19:36:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=109Should you be using the Excel ACCRINT function?
http://westclintech.com/Blog/tabid/132/EntryId/108/Should-you-be-using-the-Excel-ACCRINT-function.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">In this article we look at the Excel ACCRINT function, exploring the results returned by the function when it is used as documented, as well as looking at some ways of manipulating the input data so that you can get the result that you want, or at least a result that you can understand, and why you should be afraid of that data manipulation.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/108/Should-you-be-using-the-Excel-ACCRINT-function.aspxMon, 09 Jun 2014 18:54:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=108Calculating a Correlation Matrix in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/107/Calculating-a-Correlation-Matrix-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">As a follow up to our article on calculating a variance – covariance matrix in SQL Server, we look at a similar calculation to produce a correlation matrix and provide 2 relatively straightforward methods for calculating the correlation matrix in SQL Server, using the XLeratorDB function library, and compare them to the calculation provided in the Excel Data Analysis tool.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/107/Calculating-a-Correlation-Matrix-in-SQL-Server.aspxThu, 08 May 2014 15:51:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=107Calculating a Covariance Matrix in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/106/Calculating-a-Covariance-Matrix-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">In this article we look at 2 relatively straightforward methods for calculating a variance-covariance matrix in SQL Server, using the XLeratorDB function library, and compare them to the calculation provided in the Excel Data Analysis tool. </font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/106/Calculating-a-Covariance-Matrix-in-SQL-Server.aspxTue, 15 Apr 2014 21:58:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=106What's new in XLeratorDB/Math 1.09
http://westclintech.com/Blog/tabid/132/EntryId/105/Whats-new-in-XLeratorDB-Math-1-09.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">The latest release of XLeratroDB/math contains 5 new functions for numerical integration, 12 new functions for the generation of non-uniform random numbers and, for users of SQL Server 2008 and above, 2 new functions for calculating the nth MIN or nth MAX of a data set.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/105/Whats-new-in-XLeratorDB-Math-1-09.aspxThu, 03 Apr 2014 19:10:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=105Numerical Integration in SQL Server, Part 2
http://westclintech.com/Blog/tabid/132/EntryId/104/Numerical-Integration-in-SQL-Server-Part-2.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">In the latest release of our math library (1.09) we introduce 5 new functions for numerical integration in SQL Server. In this second of two articles we follow up on some of the concepts behind numerical integration focusing specifically on integration over semi-infinite and infinite intervals. We will explain some of the general concepts behind numerical integration, also called quadrature, using Simpson's rule, and then look at functions for Gaussian and Double Exponential quadrature.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/104/Numerical-Integration-in-SQL-Server-Part-2.aspxWed, 02 Apr 2014 20:23:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=104Numerical Integration in SQL Server, Part 1
http://westclintech.com/Blog/tabid/132/EntryId/103/Numerical-Integration-in-SQL-Server-Part-1.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">In the latest release of our math library (1.09) we introduce 5 new functions for numerical integration in SQL Server. In this article we explore some of the concepts behind numerical integration and discuss the different techniques used in the new functions, focusing specifically on integration over finite intervals. We will explain some of the general concepts behind numerical integration, also called quadrature, using Simpson's rule, and then look at functions for Gauss-Kronrod, and Tanh-Sinh quadrature. In Part 2, we will look at integration over semi-infinite and infinite intervals.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/103/Numerical-Integration-in-SQL-Server-Part-1.aspxMon, 31 Mar 2014 21:48:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=103Fast Multiple Logarithmic Regression in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/102/Fast-Multiple-Logarithmic-Regression-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">In this article we talk about the XLeratorDB GROWTHMX and LOGEST functions, compare them with the TRENDMX and LINEST functions, and describe some techniques to turbo-charge your predictive analytics in SQL Server.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/102/Fast-Multiple-Logarithmic-Regression-in-SQL-Server.aspx#Comments0http://westclintech.com/Blog/tabid/132/EntryId/102/Fast-Multiple-Logarithmic-Regression-in-SQL-Server.aspxWed, 23 Oct 2013 15:55:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=102Fast Multiple Linear Regression in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/101/Fast-Multiple-Linear-Regression-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">In this article we talk about the XLeratorDB TRENDMX and LINEST functions and describe some techniques to turbo-charge your predictive analytics in SQL Server.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/101/Fast-Multiple-Linear-Regression-in-SQL-Server.aspxMon, 07 Oct 2013 16:16:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=101Monotone Piecewise Cubic Interpolation in SQL Server
http://westclintech.com/Blog/tabid/132/EntryId/100/Monotone-Piecewise-Cubic-Interpolation-in-SQL-Server.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">We have added a new function, MONOSPLINE, to the XLeratorDB/math 2008 library based on "Monotone Piecewise Cubic Interpolation," by Fritsch, F. N. and R. E. Carlson, SIAM J. Numerical Analysis, Vol. 17, 1980, pp.238-246. In this article we look at how the MONOSPLINE function works on monotonic data and compare it to linear, natural cubic spline, and polynomial interpolation in SQL Server.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/100/Monotone-Piecewise-Cubic-Interpolation-in-SQL-Server.aspxThu, 22 Aug 2013 21:39:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=100New SQL Server Statistical Functions in XLeratorDB/statistics 1.12
http://westclintech.com/Blog/tabid/132/EntryId/99/New-SQL-Server-Statistical-Functions-in-XLeratorDB-statistics-1-12.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">We have added new functions for calculating the statistics of inter-observer agreement, as well as enhancing existing aggregate functions which calculate descriptive statistics like variance, standard deviation, covariance, etc.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/99/New-SQL-Server-Statistical-Functions-in-XLeratorDB-statistics-1-12.aspxTue, 06 Aug 2013 18:50:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=99New windowing functions in XLeratorDB
http://westclintech.com/Blog/tabid/132/EntryId/98/New-windowing-functions-in-XLeratorDB.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">With the release of SQL Server 2012, SQL Server users were finally able to use aggregate functions in a window, enabling calculations like running sums and moving averages. XLeratorDB/windowing put these capabilities into SQL Server 2005 and SQL Server 2008 as well as providing dozens of calculations that are not available in SQL Server 2012. With our latest release of XLeratorDB/windowing we add 14 new functions to the 38 existing functions. </font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/98/New-windowing-functions-in-XLeratorDB.aspxSat, 29 Jun 2013 21:46:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=98Calculating Discount Factors in SQL Server for Yield Curve Construction
http://westclintech.com/Blog/tabid/132/EntryId/97/Calculating-Discount-Factors-in-SQL-Server-for-Yield-Curve-Construction.aspx
<div style="margin: 0in 0in 10pt"><span style="font-size: 12pt; font-family: Calibri"><em><font color="#808080">A demonstration of how to use the latest XLeratorDB / financial functions to perform yield curve construction in SQL Server.</font></em></span></div>jstampf@westclintech.comhttp://westclintech.com/Blog/tabid/132/EntryId/97/Calculating-Discount-Factors-in-SQL-Server-for-Yield-Curve-Construction.aspxMon, 01 Apr 2013 20:32:00 GMThttp://westclintech.comDesktopModules/BlogTrackback.aspx?id=97