The Hypergeometric Distribution in SQL Server

Urn problems deal with problems that can be constructed in the following way. Imagine that you have an urn and it is filled with a known number of balls. The balls come in only 2 colors and in addition to knowing the number of balls we also know how many of each color are in… read more

Calculating Percentiles in SQL Server: an update

Calculating percentiles requires two things: an ordered set of values and the desired percentile, which, for our purposes, is expressed as P/100 (so the 95th percentile would be 0.95). In 2011 we introduced the PERCENTILE and PERCENTILE_EXC aggregate functions for SQL Server 2008 (and 2012 and 2014). These functions are the equivalent of the Excel… read more

Internal Rate of Return versus Total Return

Internal rate of return is a money-weighted return meaning that the amount and the timing of cash movements affect the result. An internal rate of return is calculated using the XLeratorDB XIRR function. XIRR calculates a discount rate such that the XNPV of the cash flows is approximately zero. Generally, there is no closed-form… read more

Comparing the Excel XIRR function to Google and XLeratorDB

XIRR calculates a value for discount rate such that the net present value of the cash flows is approximately equal to zero. Using the example from the Excel documentation demonstrates that pretty clearly: This spreadsheet should look like this: What is not necessarily evident, however, is that the discount… read more

Calculating a Correlation Matrix in SQL Server

Correlation is measured on a scale from -1, meaning that whatever is being measured is perfectly negatively correlated, and 1, meaning perfectly correlated. What the values between -1 and 1 mean is subject to interpretation depending on the context. However, when constructing a correlation matrix we are more concerned with comparing the correlation coefficients than… read more

Logistic Regression in SQL Server

Given a set of independent variables X and dichotomous outcomes {0, 1} Y, you can use the logistic regression to calculate the probability that Y = 1. Let's look at an example of what this means from Applied Logistic Regression, Third Edition by David W. Hosmer, Jr., Stanley and Rodney X. Sturdivant. Their… read more

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

We have previously written about calculating price, yield, and accrued interest for bonds paying regular periodic interest as well as for bonds with an odd first coupon period. In this article we look at the price, yield and accrued interest calculations for bonds with an odd last coupon period and compare the XLeratorDB functions to… read more

Calculating yield-to-call on preferred shares

Preferred shares are equity securities that may combine the characteristics of a bond with the characteristics of equity ownership. Generally, preferred shares have a preference with respect to dividends in that the preferred shareholders are to be paid a fixed amount per share before any dividends are paid to the common shareholders. In many ways,… read more