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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
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
|
By Charles Flock on
10/18/2011 8:15 PM
11 Financial Calculations that you can’t do in EXCEL - Part 2 of 11
|
By Charles Flock on
10/18/2011 8:14 PM
11 Financial Calculations that you can’t do in EXCEL - Part 3 of 11
|
By Charles Flock on
10/18/2011 8:13 PM
11 Financial Calculations that you can’t do in EXCEL - Part 5 of 11
|
By Charles Flock on
10/18/2011 8:13 PM
11 Financial Calculations that you can’t do in EXCEL - Part 4 of 11
|
By Charles Flock on
10/18/2011 8:12 PM
11 Financial Calculations that you can’t do in EXCEL - Part 6 of 11
|
By Charles Flock on
10/18/2011 8:11 PM
11 Financial Calculations that you can’t do in EXCEL - Part 7 of 11
|
By Charles Flock on
10/18/2011 8:10 PM
11 Financial Calculations that you can’t do in EXCEL - Part 9 of 11
|
By Charles Flock on
10/18/2011 8:10 PM
11 Financial Calculations that you can’t do in EXCEL - Part 8 of 11
|
By Charles Flock on
10/18/2011 8:09 PM
11 Financial Calculations that you can’t do in EXCEL - Part 10 of 11
|
By Charles Flock on
10/18/2011 8:08 PM
11 Financial Calculations that you can’t do in EXCEL - Part 11 of 11
|
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.
|
By Charles Flock on
7/15/2011 5:21 PM
Using the XLeratorDB/statistics 2008 PERCENTILE and PERCENTRANK functions in SQL Server 2008.
|
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.
|
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.
|
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.
|
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.
|
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.
|
By Charles Flock on
3/7/2011 10:10 PM
A look at the 32 new aggregate functions in XLeratorDB/statistics2008 v1.08.
|
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.
|
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?
|
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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
By Charles Flock on
9/6/2010 3:24 PM
Information about the release of XLeratorDB/statistics version 1.06
|
By Charles Flock on
9/3/2010 8:13 AM
Are Treasury Bonds really a risk free investment?
|
By Charles Flock on
7/7/2010 5:12 PM
How do you know if a function is accurate?
|
By Charles Flock on
6/27/2010 2:37 PM
We are pleased to announce the availability of XLeratorDB/developer at the startling price of $149.99
|
By Charles Flock on
6/8/2010 5:04 PM
Using the SeriesInt function to create a Monte Carlo simulation analyzing a classic probability problem.
|
By Charles Flock on
5/18/2010 8:19 PM
When is it time to admit that your favorite baseball team is not going to play in the post-season?
|
By Charles Flock on
5/10/2010 5:59 PM
EXCEL 2010 [beta] documentation uses the phrase ‘the following functions have improved accuracy.’ But what does that actually mean in relation to the IPMT, PPMT, CUMPRINC, and CUMIPMT functions?
|
By Charles Flock on
5/2/2010 4:12 PM
XLeratorDB/statistics version 1.05 contains eight new chi-squared functions that give you the flexibility to enter the expected results.
|
By Charles Flock on
4/26/2010 5:22 PM
XIRR in EXCEL 2010 is greatly improved over 2007, but you won’t find it mentioned in the literature.
|
By Charles Flock on
4/19/2010 5:49 PM
We’ve added monthly coupons to the bond functions, a new bond interest function, and some tweaks to XIRR
|
By Charles Flock on
4/7/2010 6:23 PM
Wherein we delve deep into EXCEL’s YIELD function and discover the inconsistencies between the YIELD function and the PRICE function in EXCEL.
|
By Charles Flock on
3/5/2010 9:09 PM
Using the FV function to calculate the value of your 401(k) when you retire.
|