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

## 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

## Calculate the interest payment amount on a pay-in-advance loan

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

## Calculate the cumulative interest payments for a loan with a balloon payment

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

## Calculate the price of a bond with an odd last coupon before the last coupon period

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

## Calculate a PMT amount when there is an odd first period

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

## Calculating Logarithmic Trendline Values in SQL Server

By Charles Flock on 8/19/2011 7:58 PM
In this posting we demonstrate how to replicate the EXCEL Logarithmic Trendline in SQL Server using XLeratorDB.

## Calculating percentiles in SQL Server

By Charles Flock on 7/15/2011 5:21 PM

Using the XLeratorDB/statistics 2008 PERCENTILE and PERCENTRANK functions in SQL Server 2008.

## Calculating Internal Rates of Return in SQL Server for multiple periods and multiple holdings

By Charles Flock on 5/19/2011 6:37 PM
In the article we will look at how we can use the XLeratorDB XIRR function to calculate the internal rate of return for individual securities in a portfolio as well as for all the securities in a portfolio, across multiple time horizons.

## Calculating Exponential Trendline Values in SQL Server

By Charles Flock on 5/14/2011 1:22 PM

In this posting we demonstrate how to replicate the EXCEL Exponential Trendline in SQL Server using XLeratorDB.

## Calculating Trendline Values in SQL Server Using XLeratorDB

By Charles Flock on 4/26/2011 5:33 PM

In this posting we demonstrate how to replicate the EXCEL Polynomial Trendline in SQL Server.

## New SQL Server Interpolation Functions in XLeratorDB

By Charles Flock on 4/20/2011 5:02 PM

A look at the new interpolation functions in XLeratorDB/math2008 written specifically for SQL Server 2008.

## Calculating Pascal’s Triangle in SQL Server

By Charles Flock on 3/15/2011 7:07 PM

We demonstrate the kind of things that you can do in SQL Server using the XLeratorDB function library. Would you ever really need to build Pascal’s triangle in T-SQL? Probably not. But you really don’t need to build it in any programming language, yet when I search on ‘programming Pascal’s Triangle’ in Google I get about 139,000 results. Now there’s one more.

## What’s new in XLeratorDB/statistics 2008 v1.08

By Charles Flock on 3/7/2011 10:10 PM

A look at the 32 new aggregate functions in XLeratorDB/statistics2008 v1.08.

## How about a date (function)?

By Charles Flock on 2/14/2011 9:33 PM
We added 3 new date functions to XLeratorDB/financial that should make date calculations and date manipulations much easier and more EXCEL-like, and show you how to use them to calculate things like date of Easter.

## Growing Annuities

By Charles Flock on 2/13/2011 3:43 PM

We added 4 new functions to deal with growing annuities, where the initial payment increases by a fixed percentage each period, making it far simpler to answer questions like how much do I need to save for retirement?

## Enhanced Present and Future Value Calculations

By Charles Flock on 2/13/2011 3:42 PM
In addition to the new multi-input aggregates available in XLeratorDB/financial 2008, we have added some new scalar functions that provide even greater flexibility for time-value-of-money calculations.

## Powerful new aggregate functions in XLeratorDB/finance 2008

By Charles Flock on 2/13/2011 3:41 PM
With the release of XLeratorDB/finance2008, the XLeratorDB/financial package makes innovative use of the multi-aggregate input capabilities of SQL Server 2008, improving performance in some calculations over 50-fold. In addition, there are some new functions that EXCEL wishes it had.

## Backing up and Restoring XLeratorDB

By Charles Flock on 2/5/2011 10:12 PM

If, like me, you make a backup of your production database and restore that backup on a different machine for reporting, testing, development or any other reason, you need to make sure that your XLeratorDB licenses are properly synchronized.

## Multi-input Aggregates in SQL Server 2005

By Charles Flock on 1/8/2011 1:37 PM
Despite what Books-on-line and other Microsoft documentation tells you, it is possible to implement multi-input aggregates in SQL Server 2005, albeit with some restrictions.

## The Art of the Function

By Charles Flock on 12/13/2010 11:01 PM
A look at the PV calculation and why it’s easier to use a function even when we can replicate the calculation in T-SQL.

## Time Value of Money Problems in EXCEL

By Charles Flock on 11/18/2010 6:09 PM
How a simple table-valued function led to a deep dive into the time value of money functions in EXCEL where we discover that the EXCEL IPMT and PPMT functions are broken for payments made at the beginning of the period—even in EXCEL 2010.

## Finance 1.06 is available

By Charles Flock on 11/14/2010 7:33 PM
Finance version 1.06, with 19 new functions (not found in EXCEL) is now available.

## Time-Weighted Rate of Return

By Charles Flock on 10/14/2010 5:35 PM
We frequently get inquiries about whether or not XLeratorDB can perform a time weighted return calculation. The Modified Dietz function (MDIETZ and MDIETZ_q) is just such a calculation. However, we have come up with another technique using the RATE and FVSCHEDULE functions that performs a daily time-weighted return calculation that you may find useful.

## Introducing XLeratorDB/statistics for SQL Server 2008

By Charles Flock on 10/8/2010 8:20 AM
We have changed 14 scalar functions to take advantage of the multi-input aggregate capabilities of SQL Server 2008 and improved performance more than twenty-five-fold.

## One pass or two?

By Charles Flock on 9/29/2010 5:18 PM
A look at the numerical accuracy of SQL Server’s aggregate functions, the limits of floating-point math, and other interesting stuff we discovered while implementing multi-input aggregate functions in SQL Server 2008.

## New Statistics Package

By Charles Flock on 9/6/2010 3:24 PM

Information about the release of XLeratorDB/statistics version 1.06

## The case against Treasury Bonds

By Charles Flock on 9/3/2010 8:13 AM

Are Treasury Bonds really a risk free investment?

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < April 2013 >
SunMonTueWedThuFriSat
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011
Monthly
Go