Reference

293 Notitia Sheets functions

The formulas of Notitia Sheets, the desktop app and the terminal (SHEET function): live prices, indicators, risk, options, bonds and every usual spreadsheet function. Each example can be typed as is in a cell.

Excel model library Desktop apps and Excel add-in

Notitia data

NOTITIA.QUOTE(symbol, [field]): Live quote field: price, chg, name, currency, volume, high52, low52, dayHigh, dayLow, prev, exch, time.Example: =NOTITIA.QUOTE("AAPL","price")
NOTITIA.PRICE(symbol): Live last price.Example: =NOTITIA.PRICE("MC.PA")
NOTITIA.HISTORY(symbols, [range], [header]): Daily closes aligned across symbols, spilled as Date | SYM1 | SYM2. Range 3mo, 6mo, 1y, 2y or 5y.Example: =NOTITIA.HISTORY("AAPL,MSFT","1y")
NX(...): Live Notitia quote: NOTITIA.QUOTE("AAPL", "price"|"chg"|"name"|"volume"|"currency"|"high52"|...)
NX.HIST(...): Daily closes from Notitia, aligned, spilled as Date | SYM1 | SYM2... ("AAPL,MSFT", "3mo"|"6mo"|"1y"|"2y"|"5y")
NX.QUOTE(...): Live Notitia quote: NOTITIA.QUOTE("AAPL", "price"|"chg"|"name"|"volume"|"currency"|"high52"|...)

Technical indicators

SMA(prices, [window]): Simple moving average, one value per row; blank until the window is full.Example: =SMA({1;2;3;4;5},3) → {blank; blank; 2; 3; 4}
EMA(prices, [window]): Exponential moving average, weight 2 / (n + 1) on the newest price, seeded with the first SMA.Example: =EMA({1;2;3;4;5},3) → {blank; blank; 2; 3; 4}
RSI(prices, [window]): Wilder relative strength index from 0 to 100. Above 70 is often read as overbought, below 30 as oversold.Example: =INDEX(RSI(B2:B253,14),252)
MACD(prices, [fast], [slow], [signal], [header]): Three columns: MACD line (EMA fast - EMA slow), signal (EMA of the MACD) and histogram.Example: =MACD(B2:B253,12,26,9,TRUE)
BOLLINGER(prices, [window], [k], [header]): Lower, middle and upper Bollinger bands: SMA plus and minus k standard deviations.Example: =BOLLINGER(B2:B253,20,2,TRUE)
ATR(highs, lows, closes, [window]): Average true range (Wilder): the typical daily range, gaps included. Used to size stops.Example: =ATR(C2:C253,D2:D253,B2:B253,14)
ROC(prices, [n]): Rate of change over n periods, as a fraction.Example: =ROC({100;105;110},2) → {blank; blank; 0.1}
ZSCORE(prices, [window]): How many standard deviations the price sits from its rolling mean: a mean reversion gauge.Example: =INDEX(ZSCORE(B2:B253,20),252)

Risk and performance

RETURNS(prices): Simple returns of a price column, spilled (one row shorter).Example: =RETURNS({100;110;99}) → {0.1; -0.1}
LOGRETURNS(prices): Log returns LN(p(t) / p(t-1)), spilled.Example: =LOGRETURNS({100;110})
HVOL(prices, [periods per year]): Annualised historical volatility of a price column.Example: =HVOL(B2:B253)
SHARPE(returns, [rf annual], [periods]): Annualised Sharpe ratio: mean excess return over its standard deviation.Example: =SHARPE(RETURNS(B2:B253),0.03)
SORTINO(returns, [rf annual], [periods]): Like Sharpe, but only downside volatility counts.Example: =SORTINO(RETURNS(B2:B253),0.03)
MAXDD(prices): Maximum drawdown, the worst fall from a peak, as a negative fraction.Example: =MAXDD({100;120;90;130}) → -0.25
CALMAR(prices, [periods per year]): Compound annual growth over the absolute maximum drawdown.Example: =CALMAR(B2:B253)
ULCER(prices): Ulcer index in percent: depth and length of drawdowns together.Example: =ULCER(B2:B253)
VAR.HIST(returns, [confidence]): Historical value at risk: the loss (positive) not exceeded at the confidence level.Example: =VAR.HIST(RETURNS(B2:B253),0.99)
VAR.PARAM(returns, [confidence]): Parametric (normal) value at risk: -(mean + z x volatility).Example: =VAR.PARAM(RETURNS(B2:B253),0.99)
CVAR.HIST(returns, [confidence]): Expected shortfall: the average of the losses beyond the VaR.Example: =CVAR.HIST(RETURNS(B2:B253),0.975)
BETA(returns, market returns): Sensitivity of the asset to the market: covariance over market variance.Example: =BETA(RETURNS(B2:B253),RETURNS(C2:C253))
TREYNOR(returns, market, [rf annual], [periods]): Annual excess return per unit of beta.Example: =TREYNOR(RETURNS(B2:B253),RETURNS(C2:C253),0.03)
INFORATIO(returns, benchmark, [periods]): Information ratio: active return over tracking error, annualised.Example: =INFORATIO(RETURNS(B2:B253),RETURNS(C2:C253))
TRACKINGERROR(returns, benchmark, [periods]): Annualised volatility of the return difference with the benchmark.Example: =TRACKINGERROR(RETURNS(B2:B253),RETURNS(C2:C253))
DOWNSIDEDEV(returns, [threshold], [periods]): Annualised deviation of returns below the threshold.Example: =DOWNSIDEDEV(RETURNS(B2:B253))
OMEGA(returns, [threshold]): Sum of gains above the threshold over sum of losses below it.Example: =OMEGA({0.02;-0.01;0.03;-0.02}) → 1.667
KELLY(win probability, payoff ratio): Kelly fraction of capital for a bet paying b to 1. Many traders use half of it.Example: =KELLY(0.55,1) → 0.1
KELLY.RETURNS(returns, [rf annual], [periods]): Continuous Kelly leverage: mean excess return over variance.Example: =KELLY.RETURNS(RETURNS(B2:B253))
POSSIZE(capital, risk %, entry, stop): Units to buy so that hitting the stop costs exactly the risk share of capital.Example: =POSSIZE(100000,1%,50,48) → 500
CAGR(begin, end, years): Compound annual growth rate.Example: =CAGR(100,200,10) → 0.0718

Portfolio

COVMATRIX(returns, [sample]): Covariance matrix of a block of returns, one asset per column. Rows with a gap are dropped.Example: =COVMATRIX(RETURNS(B2:E253))
CORRMATRIX(returns): Correlation matrix, one asset per column.Example: =CORRMATRIX({0.01,0.02;-0.02,-0.01;0.015,0.01})
PORTVOL(weights, cov, [periods per year]): Portfolio volatility SQRT(w' C w); pass 252 to annualise a daily covariance.Example: =PORTVOL({0.5;0.5},{0.04,0.006;0.006,0.09}) → 0.1884
MINVAR.WEIGHTS(cov, [long only]): Minimum variance weights. Long only by default (exact projected gradient); FALSE gives the closed form with shorts.Example: =MINVAR.WEIGHTS({0.04,0.006;0.006,0.09}) → {0.712; 0.288}
MAXSHARPE.WEIGHTS(expected returns, cov, [rf], [long only]): Maximum Sharpe (tangency) weights.Example: =MAXSHARPE.WEIGHTS({0.08;0.12},{0.04,0.006;0.006,0.09},0.02)
RISKPARITY.WEIGHTS(cov): Weights where every asset carries the same share of risk.Example: =RISKPARITY.WEIGHTS({0.04,0.006;0.006,0.09})
RISKCONTRIB(weights, cov): Share of portfolio variance carried by each asset (sums to 1).Example: =RISKCONTRIB({0.5;0.5},{0.04,0.006;0.006,0.09})

Monte Carlo

MC.PATHS(S0, drift, vol, years, [steps], [paths], [seed]): Geometric Brownian motion paths: one row per step, one column per path. A seed makes the draw repeatable.Example: =MC.PATHS(100,0.06,0.2,1,12,5,42)
MC.OPTION(type, S, K, T, r, sigma, [q], [paths], [seed], [with error]): Monte Carlo price of a European option (antithetic draws). TRUE as the last argument also returns the standard error.Example: =MC.OPTION("call",100,100,1,0.03,0.2,0,50000,7)
MC.VAR(value, drift, vol, days, [confidence], [paths], [seed], [cvar]): Monte Carlo value at risk of a position over a horizon in trading days; TRUE as the last argument gives the expected shortfall.Example: =MC.VAR(1000000,0.05,0.2,10,0.99,20000,1)

Options

BS.PRICE(type, S, K, T, r, sigma, [q]): Black-Scholes-Merton price of a European call or put.Example: =BS.PRICE("call",100,100,1,0.05,0.2) → 10.4506
BS.DELTA(type, S, K, T, r, sigma, [q]): Change of the option price for one unit of the underlying.Example: =BS.DELTA("call",100,100,1,0.05,0.2) → 0.6368
BS.GAMMA(type, S, K, T, r, sigma, [q]): Change of delta for one unit of the underlying.Example: =BS.GAMMA("call",100,100,1,0.05,0.2) → 0.0188
BS.VEGA(type, S, K, T, r, sigma, [q]): Price change for one volatility point.Example: =BS.VEGA("call",100,100,1,0.05,0.2) → 0.3752
BS.THETA(type, S, K, T, r, sigma, [q]): Price change per calendar day.Example: =BS.THETA("call",100,100,1,0.05,0.2)
BS.RHO(type, S, K, T, r, sigma, [q]): Price change for one rate point.Example: =BS.RHO("call",100,100,1,0.05,0.2)
BS.IV(type, price, S, K, T, r, [q]): Implied volatility that reproduces a market price.Example: =BS.IV("call",10.45,100,100,1,0.05) → 0.2
BS.DIGITAL(type, S, K, T, r, sigma, [q]): Cash-or-nothing digital paying 1 if the option ends in the money.Example: =BS.DIGITAL("call",100,100,1,0.05,0.2) → 0.5323
PROB.ITM(type, S, K, T, r, sigma, [q]): Risk-neutral probability of ending in the money, N(d2) for a call.Example: =PROB.ITM("call",100,100,1,0.05,0.2) → 0.5596
PAYOFF(type, spot at expiry, strike, [premium]): Payoff at expiry net of the premium paid; fill down a column of spots to draw the diagram.Example: =PAYOFF("call",110,100,4) → 6
BINOMIAL.AMERICAN(type, S, K, T, r, sigma, [q], [steps]): American option on a Cox-Ross-Rubinstein tree (early exercise).Example: =BINOMIAL.AMERICAN("put",100,100,1,0.05,0.2)
BLACK76(type, F, K, T, r, sigma): Black 76 price of an option on a future or forward.Example: =BLACK76("call",100,100,1,0.05,0.2)
BSDELTA(...)
BSGAMMA(...)
BSPRICE(...): Black-Scholes price (type, S, K, T years, r, sigma, [q])
BSRHO(...)
BSTHETA(...)
BSVEGA(...)
IMPLIEDVOL(...): Implied volatility (type, price, S, K, T, r, [q])

Fixed income

PRICE(settlement, maturity, rate, yield, redemption, frequency, [basis]): Clean bond price per 100 of face value.Example: =PRICE(DATE(2026,1,1),DATE(2031,1,1),0.04,0.05,100,2)
YIELD(settlement, maturity, rate, price, redemption, frequency, [basis]): Yield to maturity from a clean price.Example: =YIELD(DATE(2026,1,1),DATE(2031,1,1),0.04,95.6,100,2)
DURATION(settlement, maturity, coupon, yield, frequency, [basis]): Macaulay duration in years.Example: =DURATION(DATE(2026,1,1),DATE(2031,1,1),0.04,0.05,2)
MDURATION(settlement, maturity, coupon, yield, frequency, [basis]): Modified duration: price change in percent for one yield point.Example: =MDURATION(DATE(2026,1,1),DATE(2031,1,1),0.04,0.05,2)
ACCRINT.M(...): Accrued interest per 100 since the last coupon (settle, maturity, coupon, freq)
DISC(...)
TBILLPRICE(...)
TBILLYIELD(...)

Time value and valuation

WACC(equity, debt, cost of equity, cost of debt, tax rate): Weighted average cost of capital, debt after tax.Example: =WACC(800,200,0.09,0.05,0.25) → 0.0795
CAPM(rf, beta, market return): Expected return under the CAPM: rf + beta x (market - rf).Example: =CAPM(0.03,1.2,0.08) → 0.09
GORDON(cash flow, discount rate, growth): Gordon growth terminal value CF x (1 + g) / (r - g).Example: =GORDON(100,0.08,0.02) → 1700
NPV(rate, flows...): Net present value of flows at the end of each period (add the period-0 flow outside).Example: =-1000+NPV(0.08,300,400,500) → 17.63
IRR(flows, [guess]): Internal rate of return of periodic flows.Example: =IRR({-1000;300;400;500}) → 0.0890
XNPV(rate, flows, dates): Net present value of dated flows.Example: =XNPV(0.08,{-1000;600;600},{45658;46023;46388})
XIRR(flows, dates, [guess]): Internal rate of return of dated flows.Example: =XIRR({-1000;600;600},{45658;46023;46388})
PMT(rate, nper, pv, [fv], [type]): Payment of a loan (negative: money out).Example: =PMT(0.04/12,240,250000) → -1514.95
DB(...)
DDB(...)
EFFECT(...)
FV(...)
IPMT(...)
MIRR(...)
NOMINAL(...)
NPER(...)
PDURATION(...)
PPMT(...)
PV(...)
RATE(...)
RRI(...)
SLN(...)
SYD(...)

Statistics

NORM.S.INV(probability): Inverse of the standard normal distribution; with RAND() it draws a normal shock.Example: =NORM.S.INV(0.99) → 2.3263
CORREL(x, y): Correlation coefficient of two ranges.Example: =CORREL({1;2;3},{2;4;7})
PERCENTILE.INC(range, k): k-th percentile, inclusive method.Example: =PERCENTILE.INC({1;2;3;4;5},0.25) → 2
AVERAGE()
AVERAGEA()
AVERAGEIF(...)
AVERAGEIFS(...)
COUNT()
COUNTA()
COUNTBLANK(...)
COUNTIF(...)
COUNTIFS(...)
COVAR(...)
COVARIANCE.P(...)
COVARIANCE.S(...)
FORECAST(...)
FORECAST.LINEAR(...)
GEOMEAN()
HARMEAN()
INTERCEPT(...)
KURT()
LARGE(...)
MAX()
MAXIFS(...)
MEDIAN()
MIN()
MINIFS(...)
MODE()
MODE.SNGL()
NORM.DIST(...)
NORM.INV(...)
NORM.S.DIST(...)
NORMDIST(...)
NORMINV(...)
NORMSDIST(...)
NORMSINV(...)
PEARSON(...)
PERCENTILE(...)
PERCENTILE.EXC(...)
QUARTILE(...)
QUARTILE.INC(...)
RANK(...)
RANK.AVG(...)
RANK.EQ(...)
RSQ(...)
SKEW()
SLOPE(...)
SMALL(...)
STANDARDIZE(...)
STDEV()
STDEV.P()
STDEV.S()
STDEVP()
VAR()
VAR.P()
VAR.S()
VARP()

Matrices and arrays

MMULT(array1, array2): Matrix product; columns of the first must equal rows of the second.Example: =MMULT({1,2;3,4},{5;6}) → {17; 39}
MINVERSE(matrix): Inverse of a square matrix; #NUM! when it is singular.Example: =MINVERSE({4,7;2,6}) → {0.6,-0.7; -0.2,0.4}
MDETERM(matrix): Determinant of a square matrix.Example: =MDETERM({4,7;2,6}) → 10
MUNIT(n): Identity matrix of size n.Example: =MUNIT(3)
TRANSPOSE(array): Rows become columns.Example: =TRANSPOSE({1,2,3})
HSTACK(array1, array2, ...): Arrays side by side; short ones are padded with #N/A.Example: =HSTACK({1;2},{3;4})
VSTACK(array1, array2, ...): Arrays one above the other.Example: =VSTACK({1,2},{3,4})
SEQUENCE(rows, [cols], [start], [step]): A grid of numbers in sequence.Example: =SEQUENCE(5)
SORT(array, [column], [order]): Sorted copy of a range.Example: =SORT({3;1;2})
FILTER(array, include, [if empty]): Rows where the condition holds.Example: =FILTER(A2:C100,C2:C100>0)
UNIQUE(array): Distinct rows.Example: =UNIQUE({"a";"b";"a"})
SORTBY(...)

Pivot tables

GROUPBY(row fields, values, [function], [headers], [total]): Pivot-style summary: one row per distinct key, values aggregated with SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, MEDIAN, STDEV or PRODUCT, plus a total. Headers are detected when the first row is text.Example: =GROUPBY(A1:A100,C1:C100,SUM)
PIVOTBY(row field, column field, values, [function], [headers]): Cross table: row keys down, column keys across, totals on both sides.Example: =PIVOTBY(A1:A100,B1:B100,C1:C100,SUM)
SUMIFS(sum range, criteria range, criterion, ...): Sum of the rows meeting every criterion.Example: =SUMIFS(C2:C100,A2:A100,"Tech",B2:B100,">0")

Lookup

XLOOKUP(key, lookup array, return array, [if not found]): Finds a key and returns the matching item.Example: =XLOOKUP("b",{"a";"b"},{1;2}) → 2
INDEX(array, row, [column]): Item at a position; INDEX(spill, n) picks one value of a spilled result.Example: =INDEX({10;20;30},2) → 20
COLUMN()
COLUMNS(...)
HLOOKUP(...)
INDIRECT(...)
LOOKUP(...)
MATCH(...)
OFFSET(...)
ROW()
ROWS(...)
VLOOKUP(...)
XMATCH(...)

Logic

IFERROR(value, if error): Replaces an error by a fallback.Example: =IFERROR(1/0,0) → 0
AND()
CHOOSE(...)
FALSE()
IF(...)
IFNA(...)
IFS(...)
ISBLANK(...)
ISERR(...)
ISERROR(...)
ISEVEN(...)
ISLOGICAL(...)
ISNA(...)
ISNUMBER(...)
ISODD(...)
ISTEXT(...)
N(...)
NA()
NOT(...)
OR()
SWITCH(...)
TRUE()
XOR()

Text

CHAR(...)
CLEAN(...)
CODE(...)
CONCAT()
CONCATENATE()
EXACT(...)
FIND(...)
FIXED(...)
LEFT(...)
LEN(...)
LOWER(...)
MID(...)
PROPER(...)
REPLACE(...)
REPT(...)
RIGHT(...)
SEARCH(...)
SUBSTITUTE(...)
T(...)
TEXT(...)
TEXTAFTER(...)
TEXTBEFORE(...)
TEXTJOIN(...)
TRIM(...)
UPPER(...)
VALUE(...)

Dates

DATE(...)
DATEDIF(...)
DATEVALUE(...)
DAY(...)
DAYS(...)
DAYS360(...)
EDATE(...)
EOMONTH(...)
HOUR(...)
MINUTE(...)
MONTH(...)
NETWORKDAYS(...)
NOW()
SECOND(...)
TIME(...)
TODAY()
WEEKDAY(...)
WEEKNUM(...)
WORKDAY(...)
YEAR(...)
YEARFRAC(...)

Maths

ABS(...)
CEILING(...)
CEILING.MATH(...)
CHIDIST(...)
CHISQ.DIST(...)
CHISQ.DIST.RT(...)
COMBIN(...)
EXP(...)
FACT(...)
FLOOR(...)
FLOOR.MATH(...)
GCD()
INT(...)
LCM()
LN(...)
LOG(...)
LOG10(...)
MOD(...)
MROUND(...)
PI()
POWER(...)
PRODUCT()
QUOTIENT(...)
RAND()
RANDARRAY()
RANDBETWEEN(...)
ROUND(...)
ROUNDDOWN(...)
ROUNDUP(...)
SIGN(...)
SQRT(...)
SUM(): Sum of numbers
SUMIF(...)
SUMPRODUCT(...)
SUMSQ()
T.DIST(...)
T.DIST.2T(...)
T.DIST.RT(...)
T.INV(...)
T.INV.2T(...)
TDIST(...)
TINV(...)
TRUNC(...)