Thoughts about what it takes to keep up with the demands of investing in credit from a technology/software perspective.

Friday, November 14, 2008

SQL 2008 Excel-Like RATE Function

Keeping the standard financial calcs close to your data for batch processing is important for large data imports where calculating yields and spreads is necessary for future analytical processing. Present value and future value calculations are straightforward, but what about calculating yields? Rate calculations (and IRR) are tougher because they are based on guessing and iteration. This is abstracted from you in Excel and the VisualBasic .NET library. This leaves few options for replicating the same functionality in T-SQL. SQL 2008 (and 2005) makes it a bit easier with the introduction .NET stored procs, but that adds a layer of complexity that may not be necessary if you understand how the calculation is performed.I have put together a quick function that will work out of the box in SQL 2008. Some of the new T-SQL language features (e.g. in-line variable initialization) will need to be accounted for in lower versions. Take a look and get back with questions:

I'm attempting to use this function, but not sure what to do with the 4th-8th parms as I want to only use the first 3 like the Excel RATE() function. Can someone please tell me if they got this working like Excel and, if so, what to do with the remaining parameters? Thank you!