SQLFinancials

Beyond Excel
As the use of Microsoft Excel for financial analysis is so widespread, the SQLFinancial functions have been developed so that they faithfully reproduce the same results for the same set of input values in virtually all cases, enabling a smoother migration of existing spreadsheet-based systems into the more powerful, structured and manageable world of SQL Server. However there are also some improvements in the SQLFinancial functions versus their Excel counterparts:

• The precision of calculations in Excel is limited to 13 decimal places. In SQLFinancials accuracy is to 17 decimal places.
• SQLFinancials functions generally accept a wider possible range of input values, that in Excel would just lead to a #NUM result.
• For securities SQLFinancials recognises coupon frequencies of 6 and 12 (bi-monthly and monthly) as well as 1, 2 and 4.
• There are a couple of additional functions not found in Excel, namely DAYCOUNT and DAYSINYEAR.
• There is vastly greater capacity and data processing power of SQL Server behind them.

Native Look and Feel
All TotallySQL Transact-SQL extensions are implemented using familiar programming objects within SQL (scalar and table-valued functions, aggregate functions, user-defined types, stored procedures) , keeping the syntax clean and making them as fast and easy to use as the built-in native math functions such as AVG, SUM etc.

Multi-Valued Aggregates - even in SQL2005
Six of the 50+ SQLFinancials functions (IRR, MIRR, XIRR, NPV, XNPV, FVSCHEDULE) are in the form of aggregate functions that take multiple input values. This works fine for SQL2008 but is commonly supposed to be impossible in SQL2005 - since you can supply only one input parameter to an aggregate function. TotallySQL have, however, devised a very simple but smart way of doing this in SQL2005 using SQLTuples: you simply combine the input parameters (of any type) into a SQLTuples object on-the-fly as part of the function call. Only a very minor syntax change is needed and there is no measurable performance penalty even when used with truly enormous data sets.

SQLFinancials Features

• Accrued interest on bonds that pay interest periodically or at maturity 
• Calculation of effective coupon days, days per period & days since last / before next coupon date  
• Effective days between two dates, days in a year & year fraction for the given day count convention 
• Calculation of price & yield for securities paying interest periodically or at maturity
• Calculation of price, yield and discount rate for a discounted security  
• Calculation of price & yield for a security with an odd first or last period  
• Calculation of the interest rate & the amount payable at maturity for a fully-invested security   
• Calculation of the Macaulay duration and the modified Macaulay duration for a security    
• Support for coupon frequencies of 6 per year and 12 per year in addition to 1, 2 and 4 per year on securities.
• Calculation of the price, yield and bond-equivalent yield for a US Treasury bill   
• Depreciation using  declining, double-declining or variable declining balance methods 
• Depreciation using straight line or sum-of-year's digits methods  
• Depreciation for the French accounting system using depreciation coefficients  
• Calculation of interest, principal and total payments on a loan for a given period or range of periods  
• Calculation of nominal rate, effective annual rate & periodic rate of interest for a loan    
• Calculation of future value, present value & net present value of an investment or loan     
• Calculation of the number of periods required for a loan or investment to reach a specified value   
• Internal rate of return or modified internal rate of return for a series of regular or dated cashflows  
• Conversion of dollar prices between fractions and decimal representations