Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

Home


XLeratorDB/financial

Use XLeratorDB/financial for a wide variety of financial calculations. The feature-rich XLeratorDB function library lets you include calculations in any T-SQL statement including SELECT, INSERT, UPDATE, DELETE, CREATE VIEW as well as in CTEs, stored procedures, user-defined functions, and computed columns.

XLeratorDB/financial is available in two packages - XLeratorDB/financial which requires SQL Server 2005 or later, and XLeratorDB/financial 2008 which requires SQL Server 2008 or later. 

Functions denoted with 'Not available for SQL2005' are only available in the XLeratorDB/financial 2008 package and will not install or run  in SQL Server 2005. XLeratorDB 2008 packages contain functions which leverage SQL CLR features that are only available in SQL Server 2008 or later, namely 'multi-input aggregates' which provide a much easier calling syntax and typically run much faster than their scalar counterparts.

FUNCTION REFERENCE - FINANCIAL FUNCTIONS
RATES OF RETURN
Annuity Calculations
CUMODDFIPMT Cumulative interest on the periodic annuity payments between a start period and an end period
CUMODDFPPMT Cumulative principal on the periodic annuity payments between a start period and an end period
FV Future Value
FVGA Future Value of a Growing Annuity
FVSCHEDULE Future Value based on Compound Rates
NOMINAL Annual Nominal Interest Rate
NPER Number of Periods
NPERGA Number of Periods of a Growing Annuity
ODDFIPMT Interest portion of a periodic payment for an annuity with an odd first period
ODDFPMT Periodic payment for an annuity with an odd first period
ODDFPMTSCHED Generate Amortization schedule for an annuity with odd first period (table-valued function)
ODDFPPMT Principal portion of a periodic payment for an annuity with an odd first period
ODDFPV Present value of an annuity with an odd first period
ODDFRATE Periodic interest rate for an annuity where the first period is longer or shorter than the other periods
ODDPV Present value of an annuity with an odd first period
PMTGA Initial Payment of a Growing Annuity
PV Present Value
PVGA Present Value of a Growing Annuity
RATE Interest Rate of an Annuity
   
Internal Rates of Return
AMORTIZECASHFLOWS Generate a schedule of discounted cash flow values   (table-valued function)
IRR Internal Rate of Return
IRR_q Internal Rate of Return  
MIRR Modified Internal Rate of Return
MIRR_q Modified Internal Rate of Return  
XIRR Internal Rate of Return with non-periodic cashflows
XIRR_q Internal Rate of Return with non-periodic cashflows  
XIRR30360    new! Internal rate of return for irregular cash flows using a 30/360 day-count convention    Not available for SQL2005
XIRRT    new! Internal rate of return for cash flows discounted using XNPVT    Not available for SQL2005
XMIRR Modified Internal Rate of Return with non-periodic cashflows    Not available for SQL2005
   
Net Present Value
EFV Enhanced Future Value
ENPV Enhanced Net Present Value
ENPV_q Enhanced Net Present Value  
EPV Enhanced Present Value
NFV Net Future Value    Not available for SQL2005
NPV Net Present Value
NPV_q Net Present Value  
XDCF Discounted cash flows value of a series of irregular cash flows    Not available for SQL2005
XFV Future Value of a Cashflow between two dates
XNFV Net Future Value for non-periodic cashflows    Not available for SQL2005
XNPV Net Present Value for non-periodic cashflows
XNPV_q Net Present Value for non-periodic cashflows  
XNPV30360    new! Net Present Value for irregular cash flows using a 30/360 day-count convention    Not available for SQL2005
XNPVT    new! Net Present Value for cash flows with irregular time periods    Not available for SQL2005
XPV Discounted Value of a Cashflow between two dates
   
Time Weighted Rate of Return
EMDIETZ Enhanced Modified Dietz    Not available for SQL2005
GTWRR Generalized time-weighted rate of return    Not available for SQL2005
LMDIETZ Linked Modified Dietz    Not available for SQL2005
MDIETZ Modified Dietz  
MDIETZ_q Modified Dietz  
TWROR Time-weighted rate of return with market value indicators    Not available for SQL2005
TWRR Time Weighted Rate of Return    Not available for SQL2005
   
CAPM - CAPITAL ASSET PRICING MODEL
EQALPHA Intercept of the security characteristic line between an asset and a specified benchmark    Not available for SQL2005
EQBETA Correlated volatility (beta) between an asset and a specified benchmark    Not available for SQL2005
EQVOLATILITY Calculate historical volatility based upon price or valuation data    Not available for SQL2005
INFORATIO Information ratio based upon return data    Not available for SQL2005
INFORATIO2 Information ratio based upon price or valuation data    Not available for SQL2005
MAXDD Calculate the maximum drawdown based on net asset or portfolio values    Not available for SQL2005
MAXDD2 Calculate the maximum drawdown based on net asset or portfolio returns    Not available for SQL2005
MOIC Multiple of Invested Capital
SHARPE Sharpe ratio based upon return data    Not available for SQL2005
SHARPE2 Sharpe ratio based upon price or valuation data    Not available for SQL2005
SORTINO Sortino ratio based upon return data    Not available for SQL2005
SORTINO2 Calculate the Sortino ratio based upon price data    Not available for SQL2005
TREYNOR Treynor ratio based upon return data    Not available for SQL2005
TREYNOR2 Treynor ratio based upon price or valuation data    Not available for SQL2005
   
BOND FIGURATION
Accrued Interest
ACCINTACT Accrued interest where coupon amounts are based on number of days in the coupon period
ACCRINT Accrued Interest
ACCRINTM Accrued Interest at Maturity
AIFACTOR Accrued Interest Factor
AIFACTOR_IAM Accrued Interest Factor, Interest at Maturity
AIFACTOR_OFC Accrued Interest Factor, Odd First Coupon
AIFACTOR_OLC Accrued Interest Factor, Odd Last Coupon
AIFACTOR_RPI Accrued Interest Factor, Regular Periodic Interest
BONDINT Accrued Interest on a Bond
COMPINT Accrued interest for a security where interest is compounded periodically and paid at maturity.
ODDCOMPINT Accrued interest for a security with an odd first or odd last coupon period
   
Annuity Calculations
INTRATE Interest Rate of a Security
ODDFINT Odd First Interest
ODDFYIELD Odd First Period Yield
RECEIVED Amount Received at Maturity
   
Bond Amortization
AMORTRATE Constant daily effective rate for bond/loan amortization
BONDAMORT Amortization Schedule of a Bond (table-valued function)
   
Bond Figuration
BONDCF Cash flows for a bond paying regular periodic interest (table-valued function)
CFCONVEXITY Convexity of a series of cash flows    Not available for SQL2005
CFDURATION Duration of a series of cash flows    Not available for SQL2005
CFMDURATION Modified duration of a series of cash flows    Not available for SQL2005
CONVEXITY Calculate the convexity of an option free bond
COUPDAYBS Coupon Days - beginning to settlement
COUPDAYS Coupon Days
COUPDAYSNC Coupon Days - settlement to next coupon
COUPNCD Coupon Days - next coupon date
COUPNUM Number of Coupons from settlement to maturity
COUPPCD Previous Coupon Date
DIRTYPRICE Calculate the dirty price of a bond
DIRTYYIELD Calculate the yield of a bond from the dirty price
DIS Price, discount rate, and/or yield of a discount security
DISC Discount Rate
DISFACTORS Factors for the price calculation of a discount security (table-valued function)
DURATION Annual Duration of a Security
IAM Price and/or yield of a security paying interest at maturity
IAMFACTORS Factors for the price calculation of a security paying interest at maturity (table-valued function)
MDURATION Macauley Duration
ODDFPRICE Odd First Period Price
ODDLINT Odd Last Interest
ODDLPRICE Odd Last Period Price
ODDLYIELD Odd Last Period Yield
OFC Calculate the price and/or yield of a bond with an odd first coupon using the ODDFPRICE equation
OFCCONVEXITY Convexity of a bond with and odd first coupon
OFCDURATION Duration of a bond with an odd first coupon
OFCFACTORS Returns the components of the ODDFPRICE equation (table-valued function)
OFCMDURATION Modified duration of a bond with an add first coupon
OFL Calculate the price and/or yield of a bond with an odd first and an odd last coupon using the OFLPRICE equation
OFLCONVEXITY Convexity of a bond with an odd first and odd last coupon
OFLDURATION Duration of a bond with an odd first and odd last coupon
OFLFACTORS Returns the components of the OFLPRICE equation (table-valued function)
OFLMDURATION Modified duration of a bond with an add first and odd last coupon
OFLPRICE Calculate the price of a security with an odd first and odd last period
OFLYIELD Calculate the yield of a security with an odd first and odd last period
OLC Calculate the price and/or yield of a bond with an odd last coupon using the ODDLPRICE equation
OLCCONVEXITY Convexity of a bond with and odd last coupon
OLCDURATION Duration of a bond with an odd last coupon
OLCFACTORS Returns the components of the ODDLPRICE equation (table-valued function)
OLCMDURATION Modified duration of a bond with an add last coupon
PRICE Price of a Security
PRICEACT Price of a bond where coupon amounts are based on number of days in the coupon period
PRICEACTV Cash flows and Discount factors for a bond where coupon amounts are based on number of days in the coupon period (table-valued function)
PRICEDISC Price of a Discounted Security
PRICEFR Price of a bond with forced redemptions
PRICEMAT Price at Maturity
PRICESTEP Price of a security with step-up rates  
RPI Calculate the price and/or yield of a bond with regular periodic coupons
RPICONVEXITY Convexity of a bond paying regular periodic interest
RPIDURATION Duration of a bond paying regular periodic interest
RPIFACTORS Factors for the calculation of the price of a bond that pays regular periodic interest (table-valued function)
RPIMDURATION Modified duration of a bond paying regular periodic interest
STEPACCINT Accrued interest of a stepped-coupon bond  
STEPCONVEXITY Convexity of a stepped-coupon bond  
STEPDURATION Duration of a stepped-coupon bond  
STEPMDURATION Modified duration of a stepped-coupon bond  
TBILLEQ Bond Equivalent Yield of a Treasury Bill
TBILLPRICE Price of a Treasury Bill
TBILLYIELD Yield of a Treasury Bill
YIELD Yield of a Security
YIELDACT Yield of a bond where coupon amounts are based on number of days in the coupon period
YIELDDISC Yield of a Discounted Security
YIELDFR Yield of a bond with forced redemptions
YIELDMAT Yield with Interest at Maturity
YIELDSTEP Yield of a security with step-up rates  
   
LOANS
Annuity Calculations
CUMIPMT Cumulative Interest paid
CUMLIPMT Cumulative Interest payments of a loan
CUMLPPMT Cumulative Principal payments of a loan
CUMPRINC Cumulative Principal paid
EFFECT Maturity and Due dates
IPMT Interest Payment based on Constant Rate
LIPMT Interest Payment of a loan
LPMT Periodic Payment of a loan
LPMTSCHED Generate Loan Amortization with balloon payment and other parameters (table-valued function)
LPPMT Principal Payment of a loan
LRATE Interest rate for an annuity with an odd first period
NUMPMTS Total number of payments over the life of the loan
PMT Payment of an Annuity
PMTSCHED Payment Schedule of a loan (table-valued function)
PPMT Principal Payment
TOTALINT Total interest amount of a loan
   
Loan Amortization
AMORTRATE Constant daily effective rate for bond/loan amortization
AMORTSCHED Generate Amortization Schedule of a loan (table-valued function)
Balloon Generate loan schedule with periodic interest payments and principal repaid at maturity (table-valued function)
Bullet Generate loan schedule with single interest and principal payment at maturity (table-valued function)
ConstantCashFlow Generate annuity loan schedule with equal periodic cash flows (table-valued function)
ConstantCashFlowFR Generate cash flow schedule for a loan with a fixed maturity date and annuity-style payments (table-valued function)    Not available for SQL2005
ConstantPaymentAmount Generate loan schedule with no maturity with fixed periodic payment amount (table-valued function)
CONSTPRINAMORT Generate Amortization schedule of a loan with a fixed principal repayment (table-valued function)
ConstantPrincipal Generate loan schedule with fixed maturity date where the periodic principal payment is calculated on a straight-line basis (table-valued function)
ConstantPrincipalAmount Generate loan schedule with no fixed maturity with a fixed periodic principal payment (table-valued function)
ConstantPrincipalRate Generate loan schedule with no fixed maturity where a fixed percentage principal payment (table-valued function)
NPD Next payment date of a loan
NPNO Next payment number of a loan
PAYMENTPERIODS Calculate number of months until first payment date, start of grace period, end of grace period, and total number payments for a loan (table-valued function)
PERIODRATE Adjust the nominal rate of a loan
PPD Previous Payment date of a loan
PPNO Previous Payment number of a loan
UNEQUALLOANPAYMENTS Payment schedule for a loan where interest and principal payment frequencies differ(table-valued function)
   
Rule-of-78
R78IPMT Interest Payment of a loan using Rule of 78
R78PAYOFF Payment amount of a loan using Rule of 78
R78PPMT Principal Payment of a loan using Rule of 78
R78REBATE Rebate amount of a loan using Rule of 78
   
DEPRECIATION
DB Declining Balance
DDB Double Declining Balance
SLN Straight Line Depreciation
SYD Sum-of-Year's-Digits Depreciation
VDB Depreciation using Declining Balance
   
YIELD CURVES
Yield Curve Construction
DFINTERP Calculate interpolated discount factor
ED_FUT_CONV_ADJ_HL Convert Eurodollars futures price to forward rate using Ho Lee convexity adjustment
INTERPDFACT Calculate interpolated discount factors for a range of dates   (table-valued function)
SWAPCURVE Calculate discount factors from a series of cash, futures, and swaps rates   (table-valued function)
ZEROCOUPON Calculate an interpolated zero-coupon rate from a series of cash, futures, or swaps rates  
   
Nelson Siegel
NELSONSIEGEL Calculate the zero coupon rate using Nelson Siegel formula
NSCOEF Calculate the Nelson Siegel coefficients for a zero coupon curve   (table-valued function)
NSCOEF2 Calculate the Nelson Siegel coefficients for a zero coupon curve   (table-valued function)
   
Date Calculations for Yield Curves
ED_FUTYF Calculate futures contract time in years
ED_FUT2DATE Convert a Eurodollar futures delivery code into a delivery date
TENOR2DATE Convert an alphanumeric expression ('tenor') to a swaps or money market maturity date
   
BUSINESS DAYS CALCULATIONS
Businesss Day Calculations
BUSDAYS Calculate number of Business Days
BUSDAYSWE Calculate number of Business Days using specified weekend days
BUSINESSDATE Calculate a Business Date from an offset
BUSINESSDATEWE Calculate a Business Date from an offset and specified weekend days
T360    new! Calculate the number of periods (fractional part included) from a cash flow date to a settlement date    Not available for SQL2005
   
Date Functions
CALCDATE Convert MDY to date
DATEFLOAT Convert MDY to float
DATEINT Convert MDY to int
DAYS360 Calculate number of days using 30/360 day count conventions
DAYSINMONTH Calculate number of number of days in the month of the specified date
DAYSINYEAR Calculate number of number of days in the year of the specified date
DAYSNL Calculate number of days excluding Leap Years
EASTER Calculate date of Western Easter for a given year
EDATE Calculate Exact Date n months from specified date
EOMONTH Last Day of Month
FIRSTWEEKDAY First specified day of the week in any calendar month
ISREGULARPAY Determine if a date is a regular payment date for a loan
LASTWEEKDAY Last specified day of the week in any calendar month
NBD Convert a series of dates to flat csv string in YYYYMMDD format
NUMMONTHS Calculate number of months between two dates
YEARFRAC Fraction of Year
   
Date Functions for Yield Curves
ED_FUTYF Calculate futures contract time in years
ED_FUT2DATE Convert a Eurodollar futures delivery code into a delivery date
TENOR2DATE Convert an alphanumeric expression ('tenor') to a swaps or money market maturity date
   
MISC FUNCTIONS
DOLLARDE Dollar - fraction to Decimal
DOLLARFR Dollar - decimal to Fraction
RelativeError Calculate the relative error between two values
   
XLDB_FINANCIAL_VERSION Version Information

This function cannot reference data from SQL Server 2014 memory-optimized tables

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service