Finance

issue: date of issue

first_interest: date of first interest payment

settlement: settlement date

rate: nominal annual interest rate

par: par value, defaults to $1000

frequency: number of interest payments per year

basis: calendar basis, defaults to 0

calc_method: calculation method, defaults to TRUE

If first_interest < settlement and calc_method is TRUE, then ACCRINT returns the sum of the interest accrued in all coupon periods from issue date until settlement date.

If first_interest < settlement and calc_method is FALSE, then ACCRINT returns the sum of the interest accrued in all coupon periods from first_interest date until settlement date.

Otherwise ACCRINT returns the sum of the interest accrued in all coupon periods from issue date until settlement date.

frequency must be one of 1, 2 or 4, but the exact value does not affect the result. issue must precede both first_interest and settlement. frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

ACCRINTM.

issue: date of issue

maturity: maturity date

rate: nominal annual interest rate

par: par value

basis: calendar basis

ACCRINTM calculates the accrued interest from issue to maturity.

par defaults to $1000. If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

ACCRINT.

cost: initial cost of asset

purchase_date: date of purchase

first_period: end of first period

salvage: value after depreciation

period: subject period

rate: depreciation rate

basis: calendar basis

AMORDEGRC calculates the depreciation of an asset using French accounting conventions. Assets purchased in the middle of a period take prorated depreciation into account. This is similar to AMORLINC, except that a depreciation coefficient is applied in the calculation depending on the life of the assets.

The depreciation coefficient used is:

1.0 for an expected lifetime less than 3 years,

1.5 for an expected lifetime of at least 3 years but less than 5 years,

2.0 for an expected lifetime of at least 5 years but at most 6 years,

2.5 for an expected lifetime of more than 6 years.

Special depreciation rules are applied for the last two periods resulting in a possible total depreciation exceeding the difference of cost - salvage. Named for AMORtissement DEGRessif Comptabilite. If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

AMORLINC.

cost: initial cost of asset

purchase_date: date of purchase

first_period: end of first period

salvage: value after depreciation

period: subject period

rate: depreciation rate

basis: calendar basis

AMORLINC calculates the depreciation of an asset using French accounting conventions. Assets purchased in the middle of a period take prorated depreciation into account.

Named for AMORtissement LINeaire Comptabilite. If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

AMORDEGRC.

settlement: settlement date

maturity: maturity date

frequency: number of interest payments per year

basis: calendar basis

eom: end-of-month flag

COUPDAYBS calculates the number of days from the beginning of the coupon period to the settlement date.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

COUPDAYS.

settlement: settlement date

maturity: maturity date

frequency: number of interest payments per year

basis: calendar basis

eom: end-of-month flag

COUPDAYS calculates the number of days in the coupon period of the settlement date.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

COUPDAYBS, COUPDAYSNC.

settlement: settlement date

maturity: maturity date

frequency: number of interest payments per year

basis: calendar basis

eom: end-of-month flag

COUPDAYSNC calculates number of days from the settlement date to the next coupon period.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

COUPDAYS, COUPDAYBS.

settlement: settlement date

maturity: maturity date

frequency: number of interest payments per year

basis: calendar basis

eom: end-of-month flag

COUPNCD calculates the coupon date following settlement.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

COUPPCD, COUPDAYS, COUPDAYBS.

settlement: settlement date

maturity: maturity date

frequency: number of interest payments per year

basis: calendar basis

eom: end-of-month flag

COUPNUM calculates the number of coupons to be paid between the settlement and maturity dates, rounded up.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

COUPNCD, COUPPCD.

settlement: settlement date

maturity: maturity date

frequency: number of interest payments per year

basis: calendar basis

eom: end-of-month flag

COUPPCD calculates the coupon date preceding settlement.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

COUPNCD, COUPDAYS, COUPDAYBS.

a: limit for first random variable

b: limit for second random variable

rho: correlation of the two random variables

CUM_BIV_NORM_DIST calculates the probability that two standard normal distributed random variables with correlation rho are respectively each less than a and b.

rate: interest rate per period

nper: number of periods

pv: present value

start_period: first period to accumulate for

end_period: last period to accumulate for

type: payment type

CUMIPMT calculates the cumulative interest paid on a loan from start_period to end_period.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

IPMT.

rate: interest rate per period

nper: number of periods

pv: present value

start_period: first period to accumulate for

end_period: last period to accumulate for

type: payment type

CUMPRINC calculates the cumulative principal paid on a loan from start_period to end_period.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

PPMT.

cost: initial cost of asset

salvage: value after depreciation

life: number of periods

period: subject period

month: number of months in first year of depreciation

DB calculates the depreciation of an asset for a given period using the fixed-declining balance method.

DDB, SLN, SYD.

cost: initial cost of asset

salvage: value after depreciation

life: number of periods

period: subject period

factor: factor at which the balance declines

DDB calculates the depreciation of an asset for a given period using the double-declining balance method.

DB, SLN, SYD.

settlement: settlement date

maturity: maturity date

par: price per $100 face value

redemption: amount received at maturity

basis: calendar basis

DISC calculates the discount rate for a security.

redemption is the redemption value per $100 face value. If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

PRICEMAT.

fractional_dollar: amount to convert

fraction: denominator

DOLLARDE converts a fractional dollar amount into a decimal amount. This is the inverse of the DOLLARFR function.

DOLLARFR.

decimal_dollar: amount to convert

fraction: denominator

DOLLARFR converts a decimal dollar amount into a fractional amount which is represented as the digits after the decimal point. For example, 2/8 would be represented as .2 while 3/16 would be represented as .03. This is the inverse of the DOLLARDE function.

DOLLARDE.

settlement: settlement date

maturity: maturity date

coupon: annual coupon rate

yield: annual yield of security

frequency: number of interest payments per year

basis: calendar basis

DURATION calculates the (Macaulay) duration of a security.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

MDURATION, G_DURATION.

rate: nominal annual interest rate

nper: number of periods used for compounding

EFFECT calculates the effective interest rate using the formula (1+rate/nper)^nper-1.

NOMINAL.

currency: three-letter currency code

EURO calculates the national currency amount corresponding to 1 EUR for any of the national currencies that were replaced by the Euro on its introduction.

currency must be one of ATS (Austria), BEF (Belgium), CYP (Cyprus), DEM (Germany), EEK (Estonia), ESP (Spain), EUR (Euro), FIM (Finland), FRF (France), GRD (Greece), IEP (Ireland), ITL (Italy), LUF (Luxembourg), MTL (Malta), NLG (The Netherlands), PTE (Portugal), SIT (Slovenia), or SKK (Slovakia). This function is not likely to be useful anymore.

EUROCONVERT.

n: amount

source: three-letter source currency code

target: three-letter target currency code

full_precision: whether to provide the full precision; defaults to false

triangulation_precision: number of digits (at least 3) to be rounded to after conversion of the source currency to euro; defaults to no rounding

EUROCONVERT converts n units of currency source to currency target. The rates used are the official ones used on the introduction of the Euro.

If full_precision is true, the result is not rounded; if it false the result is rounded to 0 or 2 decimals depending on the target currency; defaults to false. source and target must be one of the currencies listed for the EURO function. This function is not likely to be useful anymore.

EURO.

rate: effective interest rate per period

nper: number of periods

pmt: payment at each period

pv: present value

type: payment type

FV calculates the future value of pv moved nper periods into the future, assuming a periodic payment of pmt and an interest rate of rate per period.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

PV.

principal: initial value

schedule: range of interest rates

FVSCHEDULE calculates the future value of principal after applying a range of interest rates with compounding.

FV.

rate: effective annual interest rate

pv: present value

fv: future value

G_DURATION calculates the number of periods needed for an investment to attain a desired value.

G_DURATION is the OpenFormula function PDURATION.

FV, PV, DURATION, MDURATION.

settlement: settlement date

maturity: maturity date

investment: amount paid on settlement

redemption: amount received at maturity

basis: calendar basis

INTRATE calculates the interest of a fully vested security.

If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

RECEIVED.

rate: effective annual interest rate

per: period number

nper: number of periods

pv: present value

fv: future value

type: payment type

IPMT calculates the interest part of an annuity's payment for period number per.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

PPMT.

values: cash flow

guess: an estimate of what the result should be

IRR calculates the internal rate of return of a cash flow with periodic payments. values lists the payments (negative values) and receipts (positive values) for each period.

The optional guess is needed because there can be more than one valid result. It defaults to 10%.

XIRR.

rate: effective annual interest rate

per: period number

nper: number of periods

pv: present value

ISPMT calculates the interest payment for period number per.

PV.

settlement: settlement date

maturity: maturity date

coupon: annual coupon rate

yield: annual yield of security

frequency: number of interest payments per year

basis: calendar basis

MDURATION calculates the modified (Macaulay) duration of a security.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

DURATION, G_DURATION.

values: cash flow

finance_rate: interest rate for financing cost

reinvest_rate: interest rate for reinvestments

MIRR calculates the modified internal rate of return of a periodic cash flow.

IRR, XIRR.

rate: effective annual interest rate

nper: number of periods used for compounding

NOMINAL calculates the nominal interest rate from the effective rate.

EFFECT.

rate: effective annual interest rate

pmt: payment at each period

pv: present value

fv: future value

type: payment type

NPER calculates the number of periods of an investment based on periodic constant payments and a constant interest rate.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

PV, FV.

rate: effective interest rate per period

value1: cash flow for period 1

value2: cash flow for period 2

NPV calculates the net present value of a cash flow.

PV.

settlement: settlement date

maturity: maturity date

issue: date of issue

first_interest: first interest date

rate: nominal annual interest rate

yield: annual yield of security

redemption: amount received at maturity

frequency: number of interest payments per year

basis: calendar basis

ODDFPRICE calculates the price per $100 face value of a security that pays periodic interest, but has an odd first period.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

ODDLPRICE, ODDFYIELD.

settlement: settlement date

maturity: maturity date

issue: date of issue

first_interest: first interest date

rate: nominal annual interest rate

price: price of security

redemption: amount received at maturity

frequency: number of interest payments per year

basis: calendar basis

ODDFYIELD calculates the yield of a security that pays periodic interest, but has an odd first period.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

ODDFPRICE, ODDLYIELD.

settlement: settlement date

maturity: maturity date

last_interest: last interest date

rate: nominal annual interest rate

yield: annual yield of security

redemption: amount received at maturity

frequency: number of interest payments per year

basis: calendar basis

ODDLPRICE calculates the price per $100 face value of a security that pays periodic interest, but has an odd last period.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

YIELD, DURATION.

settlement: settlement date

maturity: maturity date

last_interest: last interest date

rate: nominal annual interest rate

price: price of security

redemption: amount received at maturity

frequency: number of interest payments per year

basis: calendar basis

ODDLYIELD calculates the yield of a security that pays periodic interest, but has an odd last period.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

YIELD, DURATION.

call_put_flag: 'c' for a call and 'p' for a put

spot1: spot price of the underlying asset of the first option

spot2: spot price of the underlying asset of the second option

strike1: strike prices of the first option

strike2: strike prices of the second option

time: time to maturity in years

cost_of_carry1: net cost of holding the underlying asset of the first option (for common stocks, the risk free rate less the dividend yield)

cost_of_carry2: net cost of holding the underlying asset of the second option (for common stocks, the risk free rate less the dividend yield)

rate: annualized risk-free interest rate

volatility1: annualized volatility in price of the underlying asset of the first option

volatility2: annualized volatility in price of the underlying asset of the second option

rho: correlation between the two underlying assets

OPT_2_ASSET_CORRELATION models the theoretical price of options on 2 assets with correlation rho. The payoff for a call is max(spot2 - strike2,0) if spot1 > strike1 or 0 otherwise. The payoff for a put is max (strike2 - spot2, 0) if spot1 < strike1 or 0 otherwise.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

spot1: spot price of asset 1

spot2: spot price of asset 2

qty1: quantity of asset 1

qty2: quantity of asset 2

time: time to maturity in years

rate: annualized risk-free interest rate

cost_of_carry1: net cost of holding asset 1 (for common stocks, the risk free rate less the dividend yield)

cost_of_carry2: net cost of holding asset 2 (for common stocks, the risk free rate less the dividend yield)

volatility1: annualized volatility in price of asset 1

volatility2: annualized volatility in price of asset 2

rho: correlation between the prices of the two assets

OPT_AMER_EXCHANGE models the theoretical price of an American option to exchange one asset with quantity qty2 and spot price spot2 for another with quantity qty1 and spot price spot1.

OPT_EURO_EXCHANGE, OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in days

rate: annualized risk-free interest rate

cost_of_carry: net cost of holding the underlying asset

volatility: annualized volatility of the asset

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

amer_euro_flag: 'a' for an American style option or 'e' for a European style option

call_put_flag: 'c' for a call and 'p' for a put

num_time_steps: number of time steps used in the valuation

spot: spot price

strike: strike price

time: time to maturity in years

rate: annualized risk-free interest rate

volatility: annualized volatility of the asset

cost_of_carry: net cost of holding the underlying asset

A larger num_time_steps yields greater accuracy but OPT_BINOMIAL is slower to calculate.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in days

rate: annualized risk-free interest rate

volatility: annualized volatility of the asset

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in years

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS uses the Black-Scholes model to calculate the price of a European option struck at strike on an asset with spot price spot.

The returned value will be expressed in the same units as strike and spot.

OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_VEGA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in years

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS_CARRYCOST uses the Black-Scholes model to calculate the 'elasticity' of a European option struck at strike on an asset with spot price spot. The elasticity of an option is the rate of change of its price with respect to its cost_of_carry.

Elasticity is expressed as the rate of change of the option value, per 100% volatility.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in years

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS_DELTA uses the Black-Scholes model to calculate the 'delta' of a European option struck at strike on an asset with spot price spot.

The returned value will be expressed in the same units as strike and spot.

OPT_BS, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_VEGA, OPT_BS_GAMMA.

spot: spot price

strike: strike price

time: time to maturity in years

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS_GAMMA uses the Black-Scholes model to calculate the 'gamma' of a European option struck at strike on an asset with spot price spot. The gamma of an option is the second derivative of its price with respect to the price of the underlying asset.

Gamma is expressed as the rate of change of delta per unit change in spot. Gamma is the same for calls and puts.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_VEGA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in years

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS_RHO uses the Black-Scholes model to calculate the 'rho' of a European option struck at strike on an asset with spot price spot. The rho of an option is the rate of change of its price with respect to the risk free interest rate.

Rho is expressed as the rate of change of the option value, per 100% change in rate.

OPT_BS, OPT_BS_DELTA, OPT_BS_THETA, OPT_BS_VEGA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in years

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS_THETA uses the Black-Scholes model to calculate the 'theta' of a European option struck at strike on an asset with spot price spot. The theta of an option is the rate of change of its price with respect to time to expiry.

Theta is expressed as the negative of the rate of change of the option value, per 365.25 days.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_VEGA, OPT_BS_GAMMA.

spot: spot price

strike: strike price

time: time to maturity in years

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_BS_VEGA uses the Black-Scholes model to calculate the 'vega' of a European option struck at strike on an asset with spot price spot. The vega of an option is the rate of change of its price with respect to volatility.

Vega is the same for calls and puts. Vega is expressed as the rate of change of option value, per 100% volatility.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

spot: spot price

strike_call: strike price, if exercised as a call option

strike_put: strike price, if exercised as a put option

time: time in years until the holder chooses a put or a call option

time_call: time in years to maturity of the call option if chosen

time_put: time in years to maturity of the put option if chosen

rate: annualized risk-free interest rate

cost_of_carry: net cost of holding the underlying asset

volatility: annualized volatility of the asset in percent for the period through to the exercise date

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

spot1: spot price of asset 1

spot2: spot price of asset 2

qty1: quantity of asset 1

qty2: quantity of asset 2

time: time to maturity in years

rate: annualized risk-free interest rate

cost_of_carry1: net cost of holding asset 1 (for common stocks, the risk free rate less the dividend yield)

cost_of_carry2: net cost of holding asset 2 (for common stocks, the risk free rate less the dividend yield)

volatility1: annualized volatility in price of asset 1

volatility2: annualized volatility in price of asset 2

rho: correlation between the prices of the two assets

OPT_EURO_EXCHANGE models the theoretical price of a European option to exchange one asset with quantity qty2 and spot price spot2 for another with quantity qty1 and spot price spot1.

OPT_AMER_EXCHANGE, OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in days

rate: annualized risk-free interest rate

volatility: annualized volatility of the asset

cost_of_carry: net cost of holding the underlying asset

lambda: jump rate for executives

The model assumes executives forfeit their options if they leave the company.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike1: strike price at which the option is struck

strike2: strike price at which the option is re-struck if out of the money at time1

time1: initial maturity of the option in years

time2: extended maturity in years if chosen

rate: annualized risk-free interest rate

cost_of_carry: net cost of holding the underlying asset

volatility: annualized volatility of the asset

OPT_EXTENDIBLE_WRITER models the theoretical price of extendible writer options. These are options that have their maturity extended to time2 if the option is out of the money at time1.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

spot_min: minimum spot price of the underlying asset so far observed

spot_max: maximum spot price of the underlying asset so far observed

strike: strike price

time: time to maturity in years

rate: annualized risk-free interest rate

cost_of_carry: net cost of holding the underlying asset

volatility: annualized volatility of the asset

OPT_FIXED_STRK_LKBK determines the theoretical price of a fixed-strike lookback option where the holder of the option may exercise on expiry at the most favourable price observed during the options life of the underlying asset.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

spot_min: minimum spot price of the underlying asset so far observed

spot_max: maximum spot price of the underlying asset so far observed

time: time to maturity in years

rate: annualized risk-free interest rate

cost_of_carry: net cost of holding the underlying asset

volatility: annualized volatility of the asset

OPT_FLOAT_STRK_LKBK determines the theoretical price of a floating-strike lookback option where the holder of the option may exercise on expiry at the most favourable price observed during the options life of the underlying asset.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

alpha: fraction setting the strike price at the future date time_start

time_start: time until the option starts in days

time: time to maturity in days

rate: annualized risk-free interest rate

volatility: annualized volatility of the asset

cost_of_carry: net cost of holding the underlying asset

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: ratio of the number of calendar days to exercise and the number of calendar days in the year

ttime: ratio of the number of trading days to exercise and the number of trading days in the year

rate: risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

cost_of_carry: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

OPT_FRENCH values the theoretical price of a European option adjusted for trading day volatility, struck at strike on an asset with spot price spot.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: number of days to exercise

domestic_rate: domestic risk-free interest rate to the exercise date in percent

foreign_rate: foreign risk-free interest rate to the exercise date in percent

volatility: annualized volatility of the asset in percent for the period through to the exercise date

OPT_GARMAN_KOHLHAGEN values the theoretical price of a European currency option struck at strike on an asset with spot price spot.

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time: time to maturity in years

rate: the annualized rate of interest

volatility: annualized volatility of the asset in percent for the period through to the exercise date

lambda: expected number of 'jumps' per year

gamma: proportion of volatility explained by the 'jumps'

OPT_JUMP_DIFF models the theoretical price of an option according to the Jump Diffusion process (Merton).

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

p_t: zero coupon bond with expiry at option maturity

f_t: futures price

strike: strike price

t1: time to maturity of the option

t2: time to maturity of the underlying commodity futures contract

v_s: volatility of the spot commodity price

v_e: volatility of the future convenience yield

v_f: volatility of the forward rate of interest

rho_se: correlation between the spot commodity price and the convenience yield

rho_sf: correlation between the spot commodity price and the forward interest rate

rho_ef: correlation between the forward interest rate and the convenience yield

kappa_e: speed of mean reversion of the convenience yield

kappa_f: speed of mean reversion of the forward interest rate

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

type_flag: 'cc' for calls on calls, 'cp' for calls on puts, and so on for 'pc', and 'pp'

spot: spot price

strike1: strike price at which the option being valued is struck

strike2: strike price at which the underlying option is struck

time1: time in years to maturity of the option

time2: time in years to the maturity of the underlying option

rate: annualized risk-free interest rate

cost_of_carry: net cost of holding the underlying asset of the underlying option

volatility: annualized volatility in price of the underlying asset of the underlying option

For common stocks, cost_of_carry is the risk free rate less the dividend yield. time2 ≥ time1

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

spot: spot price

strike: strike price

time_payout: time to dividend payout

time_exp: time to expiration

rate: annualized interest rate

d: amount of the dividend to be paid expressed in currency

volatility: annualized volatility of the asset in percent for the period through to the exercise date

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

time1: time in years until the holder chooses a put or a call option

time2: time in years until the chosen option expires

cost_of_carry: net cost of holding the underlying asset

volatility: annualized volatility of the asset

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

fut_price1: price of the first futures contract

fut_price2: price of the second futures contract

strike: strike price

time: time to maturity in years

rate: annualized risk-free interest rate

volatility1: annualized volatility in price of the first underlying futures contract

volatility2: annualized volatility in price of the second underlying futures contract

rho: correlation between the two futures contracts

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

call_put_flag: 'c' for a call and 'p' for a put

spot: spot price

strike: strike price

a: amount received for each time period

time: time to maturity in years

m: number of time units the option has already met the condition

dt: agreed upon discrete time period expressed as a fraction of a year

rate: annualized risk-free interest rate

cost_of_carry: net cost of holding the underlying asset

volatility: annualized volatility of the asset

OPT_TIME_SWITCH models the theoretical price of time switch options. (Pechtl 1995). The holder receives a * dt for each period that the asset price was greater than strike (for a call) or below it (for a put).

OPT_BS, OPT_BS_DELTA, OPT_BS_RHO, OPT_BS_THETA, OPT_BS_GAMMA.

rate: effective annual interest rate

nper: number of periods

pv: present value

fv: future value

type: payment type

PMT calculates the payment amount for an annuity.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

PV, FV, RATE, ISPMT.

rate: effective annual interest rate

per: period number

nper: number of periods

pv: present value

fv: future value

type: payment type

PPMT calculates the principal part of an annuity's payment for period number per.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

IPMT.

settlement: settlement date

maturity: maturity date

rate: nominal annual interest rate

yield: annual yield of security

redemption: amount received at maturity

frequency: number of interest payments per year

basis: calendar basis

PRICE calculates the price per $100 face value of a security that pays periodic interest.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

YIELD, DURATION.

settlement: settlement date

maturity: maturity date

discount: annual rate at which to discount

redemption: amount received at maturity

basis: calendar basis

PRICEDISC calculates the price per $100 face value of a bond that does not pay interest at maturity.

If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

PRICEMAT.

settlement: settlement date

maturity: maturity date

issue: date of issue

discount: annual rate at which to discount

yield: annual yield of security

basis: calendar basis

PRICEMAT calculates the price per $100 face value of a bond that pays interest at maturity.

If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

PRICEDISC.

rate: effective interest rate per period

nper: number of periods

pmt: payment at each period

fv: future value

type: payment type

PV calculates the present value of fv which is nper periods into the future, assuming a periodic payment of pmt and an interest rate of rate per period.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

FV.

nper: number of periods

pmt: payment at each period

pv: present value

fv: future value

type: payment type

guess: an estimate of what the result should be

RATE calculates the rate of return.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period. The optional guess is needed because there can be more than one valid result. It defaults to 10%.

PV, FV.

settlement: settlement date

maturity: maturity date

investment: amount paid on settlement

rate: nominal annual interest rate

basis: calendar basis

RECEIVED calculates the amount to be received when a security matures.

If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

INTRATE.

p: number of periods

pv: present value

fv: future value

RRI determines an equivalent interest rate for an investment that increases in value. The interest is compounded after each complete period.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period. Note that p need not be an integer but for fractional value the calculated rate is only approximate.

This function is OpenFormula compatible.

PV, FV, RATE.

cost: initial cost of asset

salvage: value after depreciation

life: number of periods

SLN calculates the depreciation of an asset using the straight-line method.

DB, DDB, SYD.

cost: initial cost of asset

salvage: value after depreciation

life: number of periods

period: subject period

SYD calculates the depreciation of an asset using the sum-of-years method.

DB, DDB, SLN.

settlement: settlement date

maturity: maturity date

discount: annual rate at which to discount

TBILLEQ calculates the bond-equivalent yield for a treasury bill.

TBILLPRICE, TBILLYIELD.

settlement: settlement date

maturity: maturity date

discount: annual rate at which to discount

TBILLPRICE calculates the price per $100 face value for a treasury bill.

TBILLEQ, TBILLYIELD.

settlement: settlement date

maturity: maturity date

price: price

TBILLYIELD calculates the yield of a treasury bill.

TBILLEQ, TBILLPRICE.

cost: initial cost of asset

salvage: value after depreciation

life: number of periods

start_period: first period to accumulate for

end_period: last period to accumulate for

factor: factor at which the balance declines

no_switch: do not switch to straight-line depreciation

VDB calculates the depreciation of an asset for a given period range using the variable-rate declining balance method.

If no_switch is FALSE, the calculation switches to straight-line depreciation when depreciation is greater than the declining balance calculation.

DB, DDB.

values: cash flow

dates: dates of cash flow

guess: an estimate of what the result should be

XIRR calculates the annualized internal rate of return of a cash flow at arbitrary points in time. values lists the payments (negative values) and receipts (positive values) with one value for each entry in dates.

The optional guess is needed because there can be more than one valid result. It defaults to 10%.

IRR.

rate: effective annual interest rate

values: cash flow

dates: dates of cash flow

XNPV calculates the net present value of a cash flow at irregular times.

If type is 0, the default, payment is at the end of each period. If type is 1, payment is at the beginning of each period.

NPV.

settlement: settlement date

maturity: maturity date

rate: nominal annual interest rate

price: price of security

redemption: amount received at maturity

frequency: number of interest payments per year

basis: calendar basis

YIELD calculates the yield of a security that pays periodic interest.

frequency may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

PRICE, DURATION.

settlement: settlement date

maturity: maturity date

price: price of security

redemption: amount received at maturity

basis: calendar basis

YIELDDISC calculates the yield of a discounted security.

If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

PRICE, DURATION.

settlement: settlement date

maturity: maturity date

issue: date of issue

rate: nominal annual interest rate

price: price of security

basis: calendar basis

YIELDMAT calculates the yield of a security for which the interest is paid at maturity date.

If basis is 0, then the US 30/360 method is used. If basis is 1, then actual number of days is used. If basis is 2, then actual number of days is used within a month, but years are considered only 360 days. If basis is 3, then actual number of days is used within a month, but years are always considered 365 days. If basis is 4, then the European 30/360 method is used.

YIELDDISC, YIELD.