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
BOND FIGURATION
Date Calculations
COUPDAYBS
 
Number of days from previous coupon to settlement date
 
COUPDAYS
 
Number of days in a coupon period
 
COUPDAYSNC
 
Number of days from settlement to next coupon date
 
COUPNCD
 
Next coupon date
 
COUPNUM
 
Number of coupons from settlement to maturity

 
COUPPCD
 
Previous coupon date
 
 
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
 
AMORTRATE
 
Constant daily effective rate for bond/loan amortization
 
BONDAMORT
 
Amortization schedule of a bond
 (table-valued function)
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
 
ODDFINT
 
Accrued interest for a bond with an odd first coupon
 
ODDLINT
 
Accrued interest for a bond with an odd last coupon
 
STEPACCINT
 
Accrued interest of a stepped-coupon bond  
 
 
Duration & Convexity
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
 
Convexity of an option free bond
 
DURATION
 
Duration of a security
 
MDURATION
 
Macauley duration
 
OFCCONVEXITY
 
Convexity of a bond with and odd first coupon
 
OFCDURATION
 
Duration of a bond with an odd first coupon
 
OFCMDURATION
 
Modified duration of a bond with an odd first coupon
 
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
 
OFLMDURATION
 
Modified duration of a bond with an odd first and odd last coupon
 
OLCCONVEXITY
 
Convexity of a bond with an odd last coupon
 
OLCDURATION
 
Duration of a bond with an odd last coupon
 
OLCMDURATION
 
Modified duration of a bond with an odd last coupon
 
RPICONVEXITY
 
Convexity of a bond paying regular periodic interest
 
RPIDURATION
 
Duration of a bond paying regular periodic interest
 
RPIMDURATION
 
Modified duration of a bond paying regular periodic interest
 
STEPCONVEXITY
 
Convexity of a stepped-coupon bond  
 
STEPDURATION
 
Duration of a stepped-coupon bond  
 
STEPMDURATION
 
Modified duration of a stepped-coupon bond  
 
 
Price & Yield
BONDCF
 
Cash flows for a bond paying regular periodic interest
 (table-valued function)
DIRTYPRICE
 
Dirty price of a bond
 
DIRTYYIELD
 
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)
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)
INTRATE
 
Equivalent discount rate for a discount security
 
ODDFPRICE
 
Price of a bond with an odd first coupon
 
ODDFYIELD
 
Yield of a bond with an odd first coupon
 
ODDLPRICE
 
Price of a bond with an odd last coupon
 
ODDLYIELD
 
Yield of a bond with an odd last coupon
 
OFC
 
Price and/or yield of a bond with an odd first coupon using the ODDFPRICE equation
 
OFCFACTORS
 
Factors of the ODDFPRICE equation
 (table-valued function)
OFL
 
Price and/or yield of a bond with an odd first and an odd last coupon using the OFLPRICE equation
 
OFLFACTORS
 
Factors of the OFLPRICE equation
 (table-valued function)
OFLPRICE
 
Price of a security with an odd first and odd last period
 
OFLYIELD
 
Yield of a security with an odd first and odd last period
 
OLC
 
Price and/or yield of a bond with an odd last coupon using the ODDLPRICE equation
 
OLCFACTORS
 
Factors of the ODDLPRICE equation
 (table-valued function)
PRICE
 
Price of a security paying regular periodic interest
 
PRICEACT
 
Price of a bond where coupon amounts are based on number of days in the coupon period
 
PRICEACTTV
 
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 of an interest-at-maturity security
 
PRICESTEP
 
Price of a security with step-up rates  
 
RECEIVED
 
Maturity proceeds for a discount security
 
RPI
 
Price and/or yield of a bond with regular periodic coupons
 
RPIFACTORS
 
Factors for the calculation of the price of a bond that pays regular periodic interest
 (table-valued function)
STEPCF
new!
Cash flows for a step rate bond
 (table-valued function)
TBILLEQ
 
Bond equivalent yield of a Treasury Bill
 
TBILLPRICE
 
Price of a Treasury Bill
 
TBILLYIELD
 
Yield of a Treasury Bill
 
YIELD
 
Yield of a bond paying regular periodic interest
 
YIELDACT
 
Yield of a bond where coupon amounts are based on number of days in the coupon period
 
YIELDDISC
 
Yield on a discount security
 
YIELDFR
 
Yield of a bond with forced redemptions
 
YIELDMAT
 
Yield on an interest-at-maturity security
 
YIELDSTEP
 
Yield of a security with step-up rates  
 
 
ANNUITY CALCULATIONS
CUMIPMT
 
Cumulative interest paid on an annuity
 
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
 
CUMPRINC
 
Cumulative principal paid on an annuity
 
FV
 
Future Value
 
FVGA
 
Future value of a growing annuity
 
IPMT
 
Interest portion of an annuity payment
 
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
 
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
 
PMT
 
Annuity periodic payment
 
PMTGA
 
Initial payment of a growing annuity
 
PMTSCHED
 
Payment Schedule of a loan
 (table-valued function)
PPMT
 
Principal portion of an annuity payment
 
PV
 
Present value
 
PVGA
 
Present value of a growing annuity
 
RATE
 
Interest rate of an annuity
 
 
INTERNAL RATES OF RETURN
AMORTIZECASHFLOWS
 
Schedule of discounted cash flow values  
 (table-valued function)
CDRCASHFLOWIRR
new!
Internal rate of return on cash flows produced using the CDRCASHFLOW inputs
 
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 cash flows
 
XIRR_q
 
Internal rate of return with non-periodic cash flows  
 
XIRR30360
 
Internal rate of return for irregular cash flows using a 30/360 day-count convention
Not available for SQL2005
XIRRT
 
Internal rate of return for cash flows discounted using XNPVT
Not available for SQL2005
XMIRR
 
Modified internal rate of return with non-periodic cash flows
Not available for SQL2005
 
NET PRESENT VALUE
CDRCASHFLOWDCF
new!
Discounted cash flow value for a loan with a fixed periodic payment
 
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 cash flow between two dates
 
XNFV
 
Net future value for non-periodic cash flows
Not available for SQL2005
XNPV
 
Net present value for non-periodic cash flows
 
XNPV_q
 
Net present value for non-periodic cash flows  
 
XNPV30360
 
Net present value for irregular cash flows using a 30/360 day-count convention
Not available for SQL2005
XNPVT
 
Net present value for cash flows with irregular time periods
Not available for SQL2005
XPV
 
Discounted value of a cash flow 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
 
CAPITAL ASSET PRICING MODEL
BetaCoKurt
new!
Beta-cokurtosis of an asset return and a benchmark return
Not available for SQL2005
BetaCoSkew
new!
Beta-coskewness of an asset return and a benchmark return
Not available for SQL2005
BetaCoVar
new!
Beta-covariance of an asset return and a benchmark return
Not available for SQL2005
DownsideDeviation
new!
Downside deviation of asset returns
Not available for SQL2005
DownsideFrequency
new!
Downside frequency of asset returns
Not available for SQL2005
DownsidePotential
new!
Downside potential of asset returns
Not available for SQL2005
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
 
Historical volatility based upon price or valuation data
Not available for SQL2005
FinCoKurt
new!
Cokurtosis of an asset return and a benchmark return
Not available for SQL2005
FinCoSkew
new!
Coskewness of an asset return and a benchmark return
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
 
Maximum drawdown based on net asset or portfolio values
Not available for SQL2005
MAXDD2
 
Maximum drawdown based on net asset or portfolio returns
Not available for SQL2005
MOIC
 
Multiple of invested capital
 
Omega
new!
Omega of asset returns
Not available for SQL2005
OmegaExcessReturn
new!
Omega excess return
Not available for SQL2005
OmegaSharpeRatio
new!
Omega-Sharpe ratio of asset returns
Not available for SQL2005
SemiDeviation
new!
Semi-deviation of asset returns
Not available for SQL2005
SemiVariance
new!
Semi-variance of asset returns
Not available for SQL2005
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
 
Sortino ratio based upon price data
Not available for SQL2005
SpecificRisk
new!
Specific risk, the standard deviation of the error term in the regression equation
Not available for SQL2005
SystematicRisk
new!
Systematic risk
Not available for SQL2005
TotalRisk
new!
Total risk
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
UpsideFrequency
new!
Upside frequency of asset returns
Not available for SQL2005
UpsidePotentialRatio
new!
Upside potential ratio
Not available for SQL2005
UpsideRisk
new!
Upside risk, upside variance or upside deviation
Not available for SQL2005
 
LOANS
Payment Calculations
CUMLIPMT
 
Cumulative interest payments of a loan
 
CUMLPPMT
 
Cumulative principal payments of a loan
 
EFFECT
 
Effective annual interest rate
 
FVSCHEDULE
 
Future value based on compound rates
 
LIPMT
 
Interest portion of a loan payment
 
LPMT
 
Periodic payment of a loan
 
LPMTSCHED
 
Generate loan amortization with balloon payment and other parameters
  (table-valued function)
LPPMT
 
Principal portion of a loan payment
 
LRATE
 
Interest rate for an annuity with an odd first period
 
NOMINAL
 
Annual nominal interest rate
 
NUMPMTS
 
Total number of payments over the life of the loan
 
TOTALINT
 
Total interest amount of a loan
 
 
Loan Amortization
AMORTSCHED
 
Generate amortization schedule of a loan
  (table-valued function)
Balloon
 
Schedule with periodic interest payments and principal repaid at maturity
 (table-valued function)
Bullet
 
Schedule with single interest and principal payment at maturity
 (table-valued function)
CDRCASHFLOW
new!
Cash flow schedule for a loan with a fixed periodic payment with Conditional Prepayment Rates (CPR) and Constant Default Rates (CDR) applied
 (table-valued function)
ConstantCashFlow
 
Schedule with equal periodic cash flows
 (table-valued function)
ConstantCashFlowFR
 
Schedule for a loan with a fixed maturity date and annuity-style payments
 (table-valued function)Not available for SQL2005
ConstantPaymentAmount
 
Schedule with no maturity with fixed periodic payment amount
 (table-valued function)
ConstantPrincipal
 
Schedule with fixed maturity date where the periodic principal payment is calculated on a straight-line basis
 (table-valued function)
ConstantPrincipalAmount
 
Schedule with no fixed maturity with a fixed periodic principal payment
 (table-valued function)
ConstantPrincipalRate
 
Schedule with no fixed maturity where a fixed percentage principal payment
 (table-valued function)
CONSTPRINAMORT
 
Schedule of a loan with a fixed principal repayment
  (table-valued function)
NPD
 
Next payment date of a loan
 
NPNO
 
Next payment number of a loan
 
PPD
 
Previous payment date of a loan
 
PAYMENTPERIODS
 
Number of months until first payment date, start of grace period, end of grace period, and total number payments for a loan
 
PERIODRATE
 
Adjust the nominal rate of a loan
 
PPNO
 
Previous payment number of a loan
 
SMMAmort
new!
Schedule of a loan with fixed periodic payment and Conditional Prepayment Rates (CPR) and Constant Default Rates (CDR) applied.
 (table-valued function)
UNEQUALLOANPAYMENTS
 
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
 
Interpolated discount factor
 
ED_FUT_CONV_ADJ_HL
 
Convert Eurodollars futures price to forward rate using Ho Lee convexity adjustment
 
INTERPDFACT
 
Interpolated discount factors for a range of dates  
  (table-valued function)
SWAPCURVE
 
Discount factors from a series of cash, futures, and swaps rates  
  (table-valued function)
ZEROCOUPON
 
Interpolated zero-coupon rate from a series of cash, futures, or swaps rates  
 
 
Nelson Siegel
NELSONSIEGEL
 
Zero coupon rate using Nelson Siegel formula
 
NSCOEF
 
Nelson Siegel coefficients for a zero coupon curve  
  (table-valued function)
NSCOEF2
 
Nelson Siegel coefficients for a zero coupon curve  
  (table-valued function)
 
Date Calculations for Yield Curves
ED_FUTYF
 
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
 
Number of business days between two dates
 
BUSDAYSWE
 
Number of business days using specified weekend days
 
BUSINESSDATE
 
Business date from an offset
 
BUSINESSDATEWE
 
Business date from an offset and specified weekend days
 
T360
 
Number of periods (fractional) 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
 
Number of days using 30/360 day count conventions
 
DAYSINMONTH
 
Number of days in the month of the specified date
 
DAYSINYEAR
 
Number of number of days in the year of the specified date
 
DAYSNL
 
Number of days excluding Leap Years
 
EASTER
 
Date of Western Easter for a given year
 
EDATE
 
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
 
Number of months between two dates
 
YEARFRAC
 
Fraction of a year between two dates
 
 
MISC FUNCTIONS
DOLLARDE
 
Dollar - fraction to Decimal
DOLLARFR
 
Dollar - decimal to Fraction
 
RelativeError
 
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