Westclintech

Bond Figuration

ACCINTACT

Accrued interest on a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year.

ACCRINT

Calculate the accrued interest for a security that pays periodic interest.

ACCRINTM

Calculate the accrued interest for a security that pays interest at maturity.

ADJCURRYIELD

Calculate the adjusted current yield.

AIFACTOR

Calculate the Accrued Interest Factor

AIFACTOR_IAM

Calculate the Accrued Interest Factor for an Interest-at-Maturity security

AIFACTOR_OFC

Calculate the Accrued Interest Factor for a bond during its odd first coupon period

AIFACTOR_OLC

Calculate the Accrued Interest Factor for a bond during its odd last coupon period

AIFACTOR_RPI

Calculate the Accrued Interest Factor for a Regular Periodic Interest period

AMORTRATE

Constant daily effective rate for bond/loan amortization

BONDAMORT

Bond amortization schedule using constant effective daily interest method

BONDCF

Cash flows for a bond paying regular periodic interest

BONDINT

Accrued interest on a bond paying regular, periodic interest

CFCONVEXITY

Calculate the convexity of a series of cash flows.

CFDURATION

Calculate the duration of a series of cash flows

CFMDURATION

Calculate the modified duration of a series of cash flows.

COMPINT

Calculate the accrued interest for a security where interest is compounded periodically and paid at maturity

CONVEXITY

Convexity of a bond

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

Calculate the immediately previous coupon date before the settlement date.

DIRTYPRICE

Dirty price of a bond

DIRTYYIELD

Calculate the yield of a bond from its dirty price.

DIS

Price, discount rate, and/or yield of a discount security

DISC

Discount rate of a discount security

DISFACTORS

Factors for the price calculation of a discount security

DURATION

Duration of a bond paying regular, periodic interest

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

INTRATE

Calculate the interest rate for a fully invested security.

MDURATION

Modified duration on a bond paying regular, periodic interest

ODDCOMPINT

Calculate the accrued interest in first coupon period for a bond with an odd first coupon and a par value of 100

ODDFINT

Accrued interest for a bond with an odd first coupon

ODDFPRICE

Price of a security with an odd first coupon

ODDFYIELD

Calculate the YIELD with an odd first period

ODDLINT

Accrued interest for a bond with an odd last coupon

ODDLPRICE

Price of a bond with an odd last coupon

ODDLYIELD

Calculate the YIELD with an odd last period

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

OFCMDURATION

Modified duration of a bond with an odd 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

OFLMDURATION

Modified duration of a bond with an odd first and odd last coupon

OFLPRICE

Price of a security with an odd last coupon.

OFLYIELD

Yield of a bond with an odd first and an odd last coupon

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

OLCMDURATION

Modified duration of a bond with an odd last coupon

PRICE

Price of a bond paying regular periodic interest

PRICEACT

Price of a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year.

PRICEACTTV

Generate the cash flows and discount factors for a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year

PRICEDISC

Price of a discount security

PRICEFR

Price of a bond with forced redemptions

PRICEMAT

Price of an interest-at-maturity security

PRICESTEP

Calculate the Price of a security with step-up rates

RECEIVED

Calculate the amount received at maturity for a fully invested security.

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

RPIMDURATION

Modified duration of a bond paying regular period interest

STEPACCINT

Accrued interest of a stepped-coupon bond

STEPCF

Calculate the cash flows of a stepped-rate bond.

STEPCONVEXITY

Convexity of a stepped-coupon bond

STEPDURATION

Duration of a stepped-coupon bond

STEPMDURATION

Modified duration of a stepped coupon bond

TBILLEQ

Calculate the bond-equivalent yield for a Treasury bill.

TBILLPRICE

Calculate the price per 100 face value for a Treasury bill.

TBILLYIELD

Yield of a US Treasury Bill

YIELD

Yield of a bond paying regular periodic coupon

YIELDACT

Yield on a bond where the coupon amounts are calculated as the actual number of days in the coupon period divided by the number of days in the year

YIELDDISC

Discount rate of a discount security

YIELDFR

Yield of a bond with forced redemptions

YIELDMAT

Calculate the YIELD of an Interest-at-Maturity Security

YIELDSTEP

Calculate the Yield of a security with step-up rates

Internal Rates Of Return

AMORTIZECASHFLOWS

Discounted cash flow schedule

CDRCashflowIRR

Calculate the internal rate of return on cash flows produced using the CDRCASHFLOWinputs.

IRR

Internal rate of return for irregular periodic cash flows

IRR_q

Calculate an internal rate of return for a series of cash flows.

MIRR

Modified internal rate of return for periodic irregular cash flows

MIRR_q

Calculate the modified internal rate of return, where positive and negative cash flows are financed at different rates.

XIRR

Internal rate of return for irregular cash flows

XIRR_q

Calculate an internal rate of return for a series of cash flows on different dates.

XIRR30360

Internal rate of return for irregular cash flows using a 30/360 day-count convention

XIRRT

Internal rate of return for cash flows discounted using XNPVT

XMIRR

Calculate the modified internal rate of return, where positive and negative cash flows are financed at different rates and where the cash flows occur irregularly and are specified by date.

Loans

AMORTSCHED

Amortization schedule for a loan with constant periodic payments

Balloon

Schedule with periodic interest payments and principal repaid at maturity

Bullet

Schedule with single interest and principal payment at maturity

CDRCashflow

Loan amortization model with conditional prepayment and constant default rates

CDRRate

Calculate the CDR Rate given the loan default amount.

ConstantCashFlow

Schedule with equal periodic cash flows

ConstantCashFlowFR

Schedule for a loan with a fixed maturity date and annuity-style payments

ConstantPaymentAmount

Schedule with no maturity with fixed periodic payment amount

ConstantPrincipal

Schedule with fixed maturity date where the periodic principal payment is calculated on a straight-line basis

ConstantPrincipalAmount

Schedule with no fixed maturity with a fixed periodic principal payment

ConstantPrincipalRate

Schedule with no fixed maturity where a fixed percentage principal payment

CONSTPRINAMORT

Amortization schedule of a loan with a fixed principal repayment

CUMLIPMT

Calculate the cumulative interest payments for a loan with an odd first period

CUMLPPMT

Calculate the cumulative principal payments for a loan with an odd first period

EFFECT

Calculate the effective annual interest rate.

FVSCHEDULE

Calculate the future value of an initial investment using a series of compound rates.

LIPMT

Calculate the interest payment on a loan with an odd first period

LPMT

periodic payment of an annuity with an odd first period

LPMTSCHED

Amortization schedule for a loan with constant periodic payemnts and an odd first period where interest is accrued using the US rule

LPPMT

principal payment on a loan with an odd first period

LRATE

annual interest rate for an annuity with an odd first period

NOMINAL

Calculate the annual nominal interest rate.

NPD

Next payment date of a loan

NPNO

Next payment number of a loan

NUMPMTS

Calculate the number of payments from the first interest payment date to the last payment date; in other words, the total number of payments over the life of the 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

PPD

Previous payment date of a loan

PPNO

Previous payment number of a loan

R78IPMT

Interest payment calculated using the Rule-of-78

R78PAYOFF

Calculate the payoff amount for a loan or lease using the Rule-of-78.

R78PPMT

principal portion of an annuity payment using the Rule-of-78

R78REBATE

Calculate the rebate amount for a loan or lease using the Rule-of-78.

SimpleAccrual

Calculate the daily interest accruals over a range of dates for a single cash flow or a series of cash flows, using a single rate or a series of rates.

SMMAmort

Calculate a cash-flow schedule for a loan with a fixed periodic payment with Conditional Prepayment Rates (CPR) and Constant Default Rates (CDR) applied.

TOTALINT

total of interest payments over the life of an annuity

UNEQUALLOANPAYMENTS

Calculate a payment schedule for a loan where the interest payment frequency and the principal payment frequency are different, or the loan starts with an interest only schedule with principal repayments commencing after the first interest payment date.

Capital Asset Pricing Model

BetaCoKurt

Calculate the beta-cokurtosis of an asset return and a benchmark return

BetaCoSkew

calculate the beta-coskewness of an asset return and a benchmark return

BetaCoVar

Calculate the beta-covariance of an asset return and a benchmark return

DownsideDeviation

Calculate the downside deviation of asset returns

DownsideFrequency

Calculate the downside frequency of asset returns

DownsidePotential

Calculate the downside potential of asset returns

EQALPHA

the intercept of the security characteristic line (SCL), between an asset and a specified benchmark

EQBETA

the slope of the security characteristic line (SCL), between an asset and a specified benchmark

EQVOLATILITY

Calculate the historical volatility based upon price or valuation data.

FinCoKurt

Calculate the cokurtosis of an asset return and a benchmark return

FinCoSkew

Calculate coskewness of an asset return and a benchmark return.

INFORATIO

Information ratio based upon return data

INFORATIO2

Information ratio based upon price or valuation data

MAXDD

Maximum drawdown based on net asset or portfolio values

MAXDD2

Calculate the maximum drawdown based on net asset or portfolio returns.

MOIC

Calculate the multiple of invested capital.

Omega

Calculate the Omega of asset returns

OmegaExcessReturn

Calculate the Omega Excess Return

OmegaSharpeRatio

Calculate the Omega-Sharpe ratio of asset returns

SemiDeviation

Calculate the semi-deviation of asset returns

SemiVariance

Calculate the semi-variance of asset returns.

SHARPE

Sharpe ratio based upon return data

SHARPE2

Sharpe ratio based upon price or valuation data

SORTINO

Sortino ratio based upon return data

SORTINO2

Sortino ratio based upon price or valuation data

SpecificRisk

Calculate Specific Risk, the standard deviation of the error term in the regression equation

SystematicRisk

Calculate the Systematic Risk

TotalRisk

Calculate Total Risk.

TREYNOR

TREYNOR ratio based upon return data

TREYNOR2

Calculate the Treynor ratio based upon price or valuation data.

UpsideFrequency

Calculate the upside frequency of asset returns

UpsidePotentialRatio

Calculate the Upside Potential Ratio

UpsideRisk

Calculate the Upside Risk, Upside Variance or Upside Deviation.

Spread Pricing

BondPriceFromZeroes

Bond pricing from the zero coupon curve

CMTCurve

Constant Maturity Treasury curve

LogNormalIRLattice

LogNormal Interest Rate Lattice

OAC

Option Adjusted Convexity

OAD

Calculate the option-adjusted duration on a bond.

OAS

Option Adjusted Spread

PriceFromIRLattice

Bond Pricing using Option Adjusted Spread

PRICEFROMZEROESTVF

Zero Volatility spread details

ZSPREAD

Calculate the zero-volatility or static spread on a bond.

Business Days Calculations

BUSDAYS

Calculate the number of business days between two dates

BUSDAYSWE

Number of business days between 2 dates with specified weekend days

BUSINESSDATE

Calculate a Business Date from an offset

BUSINESSDATEWE

Calculate a Business Date from an offset and specified weekend days

CALCDATE

Calculate a datetime value for a specified Year, Month, and Day.

DATEFLOAT

Calculate a float value for a specified Year, Month, and Day.

DATEINT

Calculate an integer value for a specified Year, Month, and Day.

DAYS360

Number of days using 30/360 day-count conventions

DAYSINMONTH

Number of days in the month of the specified date

DAYSINYEAR

Number of days in the year of the specified date

DAYSNL

Number of days excluding leap years

EASTER

Calculate the date of Western Easter for the specified year.

EDATE

Calculate the date that is the indicated number of months before or after a specified date (the start date).

EOMONTH

Calculate the date for the last day of the month that is the indicated number of months before or after the start date.

FIRSTWEEKDAY

Calculate the last occurrence of a weekday in a given month

ISREGULARPAY

Calculate if a date is a regular payment date for a loan given the first payment date, the issue date, and the number of payments per year.

LASTWEEKDAY

Calculate the first occurrence of a weekday in a given month

NBD

Create holiday string

NUMMONTHS

Number of months between two dates

T360

Calculate the number of periods (fractional part included) from a cash flow date to a settlement date.

YEARFRAC

Calculate the difference between two dates as a fraction of a year

Net Present Value

CDRCashflowDCF

Calculate the discounted cash flow value for a loan with a fixed periodic payment with Conditional Prepayment Rates (CPR) and Constant Default Rates (CDR) applied.

EFV

Calculate the future value of a cash flow between two periods.

ENPV

Net Present Value for irregular periodic cash flows

ENPV_q

Calculate the net present value of an investment based on a series of periodic cash flows and a discount rate.

EPV

Calculate the discounted value of a cash flow between two periods.

NFV

Calculate the net future value of an investment based on a series of periodic cash flows and a rate.

NPV

Net Present Value for irregular periodic cash flows using the Excel definition

NPV_q

Calculate the net present value of an investment based on a series of periodic cash flows and a discount rate.

XDCF

Discounted cash flow value

XFV

Calculate the future value of a cash flow between two dates.

XNFV

Net Future Value for irregular cash flows

XNPV

Net Present Value for irregular cash flows

XNPV_q

Calculate the net present value of a series of irregular cash flows—cash flows of varying amounts occurring on various dates.

XNPV30360

Net Present Value for irregular cash flows using a 30/360 day-count convention

XNPVT

Calculate the net present value for a series of cash flows with irregular time periods—cash flows of varying amount occurring at various points in time.

XPV

Calculate the discounted value of a cash flow between two dates.

Annuity Calculations

CUMIPMT

Cumulative interest payments for 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 payments for an annuity

FV

Calculate future value of an annuity based on periodic, constant payments and a constant interest rate.

FVGA

Calculate the future value of a growing annuity.

IPMT

Calculate the interest portion of a periodic payment

NPER

number of periods in an annuity

NPERGA

Calculate the number of whole periods for a growing annuity to reach a future value.

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

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

Rate of an annuity with an odd first period given number of periods, periodic payment, present value, and future value.

ODDFSCHED

Calculate an annuity-like payment schedule where the first period is a different length of the time than all subsequent periods and those subsequent periods are assumed to be of equal length.

ODDPV

Calculate the present value of an annuity with an odd first period

PDURATION

Calculate the number of periods required by an investment to reach a specified value.

PMT

Calculate the periodic payment for an annuity

PMTGA

Calculate the initial payment for a growing annuity, given the future value.

PMTSCHED

Calculate an amortization schedule for a loan with no odd periods.

PPMT

principal portion of an annuity

PV

Present value of an annuity

PVGA

Calculate the present value of a growing annuity.

RATE

Rate of an annuity given number of periods, periodic payment, present value, and future value

RRI

Calculate an equivalent interest rate for the growth of an investment.

Depreciation

DB

Calculate the depreciation of an asset for a specified period using the fixed-declining balance method.

DDB

Calculate the depreciation of an asset for a specified period using the double-declining balance method or some other user-specified method.

SLN

Calculate the straight-line depreciation of an asset for one period.

SYD

Calculate the sum-of-years' digits depreciation of an asset for a specified period.

VDB

Calculate the depreciation of an asset for a specified or partial period by using a declining balance method.

Yield Curves

DFINTERP

Calculate the interpolated discount factor given a date.

ED_FUT_CONV_ADJ_HL

Calculate a Eurodollars futures price into a forward rate using the Ho Lee convexity adjustment formula.

ED_FUT2DATE

Calculate a Eurodollar futures delivery code into a delivery date.

ED_FUTYF

Calculate the amount of time (in years) from a start date to the delivery date of a futures contract.

INTERPDFACT

Calculate interpolated discount factors for a range of dates.

NELSONSIEGEL

Calculate the zero coupon rate for a date from the supplied parameters.

NSCOEF

Calculate the Nelson Siegel coefficients for a zero coupon curve.

NSCOEF2

Calculate the Nelson Siegel coefficients for a zero coupon curve.

SWAPCURVE

Calculate discount factors, zero-coupon rates, and continuously compounded zero-coupon rates from a series of cash rates, futures prices, or swaps rates.