Author: Created: 9/30/2008 8:59 PM Commentary from the WestClinTech function designers.

## In-database Analytics in SQL Server

By Charles Flock on 1/28/2016 5:52 PM

## Using XLeratorDB in your own SQL Server functions

By Charles Flock on 12/15/2015 11:26 AM
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.

## The Hypergeometric Distribution in SQL Server

By Charles Flock on 10/6/2015 3:33 PM
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.

## Calculating Percentiles in SQL Server: an update

By Charles Flock on 8/7/2015 2:33 PM
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.

## Calculating XIRR for multiple portfolios and multiple time periods in SQL Server

By Charles Flock on 5/13/2015 3:27 PM
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.

## Internal Rate of Return versus Total Return

By Charles Flock on 4/23/2015 3:18 PM
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.

## Comparing the Excel XIRR function to Google and XLeratorDB

By Charles Flock on 4/10/2015 12:42 PM

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.

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.

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.

## Calculating a Correlation Matrix in SQL Server

By Charles Flock on 3/25/2015 10:24 AM
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.

## Calculating internal rates of return using 30/360 day-count conventions

By Charles Flock on 3/17/2015 2:41 PM
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.

## Logistic Regression in SQL Server

By Charles Flock on 3/4/2015 10:08 AM
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.

## Calculating the price of a bond with an odd last coupon in SQL Server

By Charles Flock on 11/24/2014 1:11 PM
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.

## Calculating yield-to-call on preferred shares

By Charles Flock on 11/18/2014 11:15 AM
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.

## New SQL Server loan functions in XLeratorDB/financial 1.13

By Charles Flock on 9/25/2014 5:34 PM
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.

## Calculating the price of a bond with an odd first period in SQL Server

By Charles Flock on 7/29/2014 10:32 AM
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.

## Calculating the price of a bond in SQL Server

By Charles Flock on 7/1/2014 2:36 PM
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.

## Should you be using the Excel ACCRINT function?

By Charles Flock on 6/9/2014 1:54 PM
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.

## Calculating a Correlation Matrix in SQL Server

By Charles Flock on 5/8/2014 10:51 AM
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.

## Calculating a Covariance Matrix in SQL Server

By Charles Flock on 4/15/2014 4:58 PM
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.

## What's new in XLeratorDB/Math 1.09

By Charles Flock on 4/3/2014 2:10 PM
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.

## Numerical Integration in SQL Server, Part 2

By Charles Flock on 4/2/2014 3:23 PM
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.

## Numerical Integration in SQL Server, Part 1

By Charles Flock on 3/31/2014 4:48 PM
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.

## Fast Multiple Logarithmic Regression in SQL Server

By Charles Flock on 10/23/2013 10:55 AM
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.

## Fast Multiple Linear Regression in SQL Server

By Charles Flock on 10/7/2013 11:16 AM
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.

## Monotone Piecewise Cubic Interpolation in SQL Server

By Charles Flock on 8/22/2013 4:39 PM
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.

## New SQL Server Statistical Functions in XLeratorDB/statistics 1.12

By Charles Flock on 8/6/2013 1:50 PM
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.

## New windowing functions in XLeratorDB

By Charles Flock on 6/29/2013 4:46 PM
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.

## Calculating Discount Factors in SQL Server for Yield Curve Construction

By Charles Flock on 4/1/2013 3:32 PM
A demonstration of how to use the latest XLeratorDB / financial functions to perform yield curve construction in SQL Server.

## What’s new in XLeratorDB / finance 1.11

By Charles Flock on 3/29/2013 4:53 PM
The latest release of XLeratorDB / finance contains a bunch of new functions for yield curve construction, 2 new bond pricing functions, and an addition to the list of Capital Asset Pricing model formulas.

## Calculating FIFO balances in SQL Server

By Charles Flock on 2/26/2013 6:27 PM
With the release of XLeratorDB/windowing 1.01 we introduce three new functions for calculating inventory values: FIFO; LIFO; and WAC. These functions calculate the quantity-on-hand, cost-of-goods sold, gross margin, and inventory value using the First In, First Out (FIFO), Last In, First Out (LIFO), or Weighted Average Cost (WAC) method. In this article we talk about how those calculations work and how you can incorporate these calculations into T-SQL statements without having to do a self-join.

## Calculating the beta of a stock or portfolio in SQL Server

By Charles Flock on 1/23/2013 11:57 AM
In this article we look at how moving a traditional spreadsheet calculation into SQL Server simplifies the calculation while also allowing you to analyze more data in more different ways than you would think possible

## What’s new in XLeratorDB/financial ver 1.10

By Charles Flock on 12/28/2012 1:52 PM
XLeratorDB / financial 1.10 contains 19 new functions: 3 functions for calculating time-weighted rate of return; 9 new functions for calculating some popular Capital Asset Pricing Model (CAPM) ratios; 3 new functions for calculating the interest rate sensitivity of a series of irregular cash flows; 2 new functions for calculating dates; a function for calculating multiple of invested capital; and a new function for calculating discounted cash flows.

## Window Functions for SQL Server 2005 and SQL Server 2008

By Charles Flock on 11/8/2012 4:59 PM
SQL Server 2012 added more complete support for window functions, including enhanced support for window aggregate functions, as well as support for window offset and window distribution functions. In our latest function library, XLeratorDB / windowing, we make many of the same capabilities available for SQL Server 2005 and SQL Server 2008 users, as well as providing a bunch of functions that aren’t available in SQL Server 2012, but are available in Oracle.

## Calculating a time-weighted rate of return using modified Dietz in SQL Server

By Charles Flock on 10/24/2012 1:24 PM
The modified Dietz calculation produces a result which measures the performance of an investment portfolio based on time-weighted cash flows. Today, we will look at two XLeratorDB aggregate functions, EMDIETZ and FVSCHEDULE, which calculate the modified Dietz value for each period and then link the results together to come up with a time-weighted rate of return value.

## New functions for pricing American and European options in SQL Server

By Charles Flock on 9/17/2012 8:21 PM
With the release of our newest library of functions, XLeratorDB/financial-options, you have the ability to calculate the price and Greeks for American and European options in SQL Server 2005, 2008, and 2012. This release includes the Black-Scholes-Merton pricing formula, the Bjerksund & Stensland 2002 American approximation, and binomial trees for American and European options. It also includes calculation of the implied volatility and some table-valued functions and stored procedures for analyzing the price and P&L impacts of changes in the underlying asset price, the volatility, the risk free rate, and time decay.

## New t-Test functions in XLeratorDB/statistics 1.11

By Charles Flock on 7/18/2012 4:56 PM
We have a new functionality for the t-Test allowing you to perform sophisticated analysis of your SQL Server data directly on the database using TSQL.

## Using XLeratorDB with MySQL and other RDBMS’s

By Charles Flock on 6/7/2012 9:01 AM
XLeratorDB can be used with data sources other than SQL Server, such as MySQL and Oracle. This article illustrates how to call an XLeratorDB function against a MySQL database.

## Calculating business days in SQL Server

By Charles Flock on 5/30/2012 4:42 PM
Release 1.09 of XLeratorDB / financial and XLeratorDB / financial 2008 contains 5 new date functions: BUSDAYS, BUSINESSDATE, BUSINESSDATEWE, DAYS360 and DAYSNL. In this article, we explore how these functions work and the kinds of calculations that can now easily be done in your SQL Server database.

## What’s new in Finance 1.09

By Charles Flock on 5/21/2012 2:26 PM
We introduce seventeen useful new functions which help analyze accrued interest on bonds, create payments schedules for commercial loans and leases, calculate dates and the number of days between two dates incorporating weekends, holidays, and day-count conventions, and perform time value of money calculations when there is an odd first period.

## Creating a Bond Amortization Schedule in SQL Server

By Charles Flock on 4/5/2012 8:55 PM
A look at different techniques for generating schedules to account for the premium or discount associated with the issuance or purchase of a bond using the XLeratorDB functions COUPDAYSNC, COUPNUM, DAYS360, EDATE, IRR, PRICE, PV, RATE, SeriesDate, SeriesInt, XIRR, and YIELD.

## Calculating the odds of winning Mega Millions using Transact SQL

By Charles Flock on 3/29/2012 4:51 PM
A look at the COMBIN function in SQL Server and how to turn what is normally a complicated series of spreadsheet calculations into a simple set operation on the database.

## Multiple linear regression in SQL Server

By Charles Flock on 3/1/2012 3:33 PM
Using the LINEST function in SQL Server to do an Ordinary Least Squares calculation and the TRENDMX function to estimate housing prices.

## What’s new in XLeratorDB Math 1.06

By Charles Flock on 2/20/2012 2:59 PM
We have added some performance enhancements to the pseudo-random number generators, as well as a new function to generate random normal numbers.
We created scalar versions of the table-valued matrix functions, making the SQL much simpler and easier to follow. We also added a couple of new matrix functions for forward substitution and back substitution.

## Calculating the Time-Weighted Rate of Return in SQL Server

By Charles Flock on 11/25/2011 1:51 PM
With the release of the TWRR multi-input aggregate function, XLeratorDB users can now calculate the time-weighted rate of return directly in a T-SQL statement.

## 11 financial calculations that you can’t do in EXCEL

By Charles Flock on 10/18/2011 8:16 PM
Here is a list of 11 very simple financial calculations that you can’t do in EXCEL, either because the EXCEL design of a function doesn’t support the input data, or because EXCEL produces a result that is wrong  or unreliable. Of course, we used EXCEL 2010 and, of course, you can do them in SQL Server using XLeratorDB.
Part 1 of 11 - Enter a negative yield to calculate the price of a bond

## Enter a negative rate into the XNPV function

By Charles Flock on 10/18/2011 8:15 PM
11 Financial Calculations that you can’t do in EXCEL - Part 2 of 11

## Rely on the results of the XIRR function

By Charles Flock on 10/18/2011 8:14 PM
11 Financial Calculations that you can’t do in EXCEL - Part 3 of 11

## Correctly calculate the accrued interest on a bond with an odd first period

By Charles Flock on 10/18/2011 8:13 PM
11 Financial Calculations that you can’t do in EXCEL - Part 5 of 11

## Calculate the price (or yield) of a bond on its issue date when it has an odd first period

By Charles Flock on 10/18/2011 8:13 PM
11 Financial Calculations that you can’t do in EXCEL - Part 4 of 11

## Correctly calculate the price of a bond with an odd long first period

By Charles Flock on 10/18/2011 8:12 PM
11 Financial Calculations that you can’t do in EXCEL - Part 6 of 11

## Calculate the PRICE of a bond with a monthly coupon

By Charles Flock on 10/18/2011 8:11 PM
11 Financial Calculations that you can’t do in EXCEL - Part 7 of 11

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < January 2016 >
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456
Monthly
Go