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 '2008+2012 only' 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
AMORTIZECASHFLOWS    new! Generate a schedule of discounted cash flow values (table-valued function)
EFV Enhanced Future Value
EMDIETZ - 2008 Enhanced Modified Dietz    2008+2012 only
ENPV Enhanced Net Present Value
ENPV_q Enhanced Net Present Value
ENPV - 2008 Enhanced Net Present Value    2008+2012 only
EPV Enhanced Present Value
FV Future Value
FVGA Future Value of a Growing Annuity
FVSCHEDULE Future Value based on Compound Rates
GTWRR - 2008 Generalized time-weighted rate of return    2008+2012 only
IRR Internal Rate of Return
IRR_q Internal Rate of Return
IRR - 2008 Internal Rate of Return    2008+2012 only
LMDIETZ - 2008 Linked Modified Dietz    2008+2012 only
MDIETZ Modified Dietz
MDIETZ_q Modified Dietz
MIRR Modified Internal Rate of Return
MIRR_q Modified Internal Rate of Return
MIRR - 2008 Modified Internal Rate of Return    2008+2012 only
MOIC Multiple of Invested Capital
NFV - 2008 Net Future Value    2008+2012 only
NOMINAL Annual Nominal Interest Rate
NPER Number of Periods
NPERGA Number of Periods of a Growing Annuity
NPV Net Present Value
NPV_q Net Present Value
NPV - 2008 Net Present Value    2008+2012 only
PMTGA Initial Payment of a Growing Annuity
PV Present Value
PVGA Present Value of a Growing Annuity
RATE Interest Rate of an Annuity
TWROR - 2008 Time-weighted rate of return with market value indicators    2008+2012 only
TWRR - 2008 Time Weighted Rate of Return    2008+2012 only
XDCF - 2008 Discounted cash flows value of a series of irregular cash flows    2008+2012 only
XFV Future Value of a Cashflow between two dates
XIRR Internal Rate of Return with non-periodic cashflows
XIRR_q Internal Rate of Return with non-periodic cashflows
XIRR - 2008 Internal Rate of Return with non-periodic cashflows    2008+2012 only
XMIRR - 2008 Modified Internal Rate of Return with non-periodic cashflows    2008+2012 only
XNFV - 2008 Net Future Value for non-periodic cashflows    2008+2012 only
XNPV Net Present Value for non-periodic cashflows
XNPV_q Net Present Value for non-periodic cashflows
XNPV - 2008 Net Present Value for non-periodic cashflows    2008+2012 only
XPV Discounted Value of a Cashflow between two dates
   
CAPM - CAPITAL ASSET PRICING MODEL
EQALPHA - 2008 Intercept of the security characteristic line between an asset and a specified benchmark    2008+2012 only
EQBETA - 2008 Correlated volatility (beta) between an asset and a specified benchmark    2008+2012 only
EQVOLATILITY - 2008    new! Calculate historical volatility based upon price or valuation data    2008+2012 only
INFORATIO - 2008 Information ratio based upon return data    2008+2012 only
INFORATIO2 - 2008 Information ratio based upon price or valuation data    2008+2012 only
SHARPE - 2008 Sharpe ratio based upon return data    2008+2012 only
SHARPE2 - 2008 Sharpe ratio based upon price or valuation data    2008+2012 only
SORTINO - 2008 Sortino ratio based upon return data    2008+2012 only
SORTINO2 - 2008 Calculate the Sortino ratio based upon price data    2008+2012 only
TREYNOR - 2008 Treynor ratio based upon return data    2008+2012 only
TREYNOR2 - 2008 Treynor ratio based upon price or valuation data    2008+2012 only
   
BOND FIGURATION
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
ACCRINT Accrued Interest
ACCRINTM Accrued Interest at Maturity
BONDAMORT Amortization Schedule of a Bond (table-valued function)
BONDINT Accrued Interest on a Bond
CFCONVEXITY - 2008 Convexity of a series of cash flows    2008+2012 only
CFDURATION - 2008 Duration of a series of cash flows    2008+2012 only
CFMDURATION - 2008 Modified duration of a series of cash flows    2008+2012 only
COMPINT    new! Accrued interest for a security where interest is compounded periodically and paid at maturity.
CONVEXITY Calculate the convexity of an option free bond
COUPDAYS Coupon Days
COUPDAYBS Coupon Days - beginning to settlement
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
DISC Discount Rate
DURATION Annual Duration of a Security
INTRATE Interest Rate of a Security
MDURATION Macauley Duration
ODDCOMPINT    new! Accrued interest for a security with an odd first or odd last coupon period
ODDFINT Odd First Interest
ODDFPRICE Odd First Period Price
ODDFYIELD Odd First Period Yield
ODDLINT Odd Last Interest
ODDLPRICE Odd Last Period Price
ODDLYIELD Odd Last Period Yield
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
PRICE Price of a Security
PRICEDISC Price of a Discounted Security
PRICEMAT Price at Maturity
PRICESTEP Price of a security with step-up rates
RECEIVED Amount Received at Maturity
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
YIELDDISC Yield of a Discounted Security
YIELDMAT Yield with Interest at Maturity
YIELDSTEP Yield of a security with step-up rates
   
LOANS
AMORTRATE Constant daily effective rate for bond/loan amortization
AMORTSCHED Amortization Schedule of a loan (table-valued function)
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 ballon payment and other parameters (table-valued function)
LPPMT Principal Payment of a loan
LRATE Interest rate for an annuity with an odd first period
NPD Next payment date of a loan
NPNO Next payment number of a loan
NUMPMTS    new! Total number of payments over the life of the loan
ODDPV Present value of an annuity with an odd first period
PMT Payment of an Annuity
PERIODRATE Adjust the nominal rate of a loan
PMTSCHED Payment Schedule of a loan (table-valued function)
PPD Previous Payment date of a loan
PPMT Principal Payment
PPNO Previous Payment number of a loan
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
TOTALINT Total interest amount of a loan
UNEQUALLOANPAYMENTS    new! Payment schedule for a loan where interest and principal payment frequencies differ(table-valued function)
   
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 CURVE CONSTRUCTION
DFINTERP Calculate interpolated discount factor
DFINTERP - 2008 Calculate interpolated discount factor    2008+2012 only
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)
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)
TENOR2DATE Convert an alphanumeric expression ('tenor') to a swaps or money market maturity date
ZEROCOUPON Calculate an interpolated zero-coupon rate from a series of cash, futures, or swaps rates
   
BUSINESS DAYS CALCULATIONS
BUSDAYS Calculate number of Business Days
BUSINESSDATE Calculate a Business Date from an offset
BUSINESSDATEWE Calculate a Business Date from an offset and specified weekend days
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
DAYSNL Calculate number of days excluding Leap Years
EASTER    new! Calculate date of Western Easter for a given year
EDATE Calculate Exact Date n months from specified date
ED_FUTYF Calculate futures contract time in years
ED_FUT2DATE Convert a Eurodollar futures delivery code into a delivery date
EOMONTH Last Day of Month
FIRSTWEEKDAY First specified day of the week in any calendar month
ISREGULARPAY    new! 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
TENOR2DATE Convert an alphanumeric expression ('tenor') to a swaps or money market maturity date
YEARFRAC Fraction of Year
   
   
OTHER
DOLLARDE Dollar - fraction to Decimal
DOLLARFR Dollar - decimal to Fraction
ED_FUT_CONV_ADJ_HL Convert Eurodollars futures price to forward rate using Ho Lee convexity adjustment
   
XLDB_FINANCIAL_VERSION Version Information

 



 |  View Topic History  |
Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service