Fontics | Modules | Utility Functions

Utility Functions

Syntax, parameters and examples for Fontics custom functions.

Use Fontics functions directly in your Excel or Google Sheets formulas.

Function names start with FT. in Excel and FT_ in Google Sheets.

FT.BSE — Black-Scholes

Calculates the value of a European call or put option using Black-Scholes. A European option can be exercised only at expiry.

Syntax

=FT.BSE(S, K, T, r, sigma, [q], [type])

Function parameters
Parameter	Description	Default
S	Current value of the underlying asset or equity. Use the same units as K.	Required
K	Strike price. Use the same units as S.	Required
T	Time to expiry in years. For example, 0.5 means six months.	Required
r	Annual risk-free (Treasury) rate, entered as a decimal.	Required
sigma	Annualised volatility, entered as a decimal. For example, 0.30 means 30%.	Required
q	Optional annual dividend yield, entered as a decimal.	0
type	Optional option type: "C" for call or "P" for put.	"C"

Black-Scholes equations

C = S e^{-qT}N(d_1) - K e^{-rT}N(d_2)

P = K e^{-rT}N(-d_2) - S e^{-qT}N(-d_1)

\begin{aligned}d_1 &= \frac{\ln(S/K) + \left(r-q+\frac{\sigma^2}{2}\right)T}{\sigma\sqrt{T}} \\[10pt] d_2 &= d_1 - \sigma\sqrt{T}\end{aligned}

C / P: Call / put value, selected by type = "C" / "P".
S / K: Underlying value / strike price.
T: Time to expiry in years.
r / q: Continuously compounded annual risk-free rate / dividend yield.
σ: Annualised volatility (the sigma argument).
N(x): Probability that a standard normal variable is less than or equal to x.

With q = 0, the call equation reduces to the version without dividends.

Examples

=FT.BSE(100, 100, 1, 0.05, 0.30)

Values a one-year call with an underlying value and strike of 100, a 5% risk-free rate, 30% volatility and no dividends.

=FT.BSE(100, 100, 1, 0.05, 0.30, 0.02, "P")

Values a put with the same inputs and a 2% annual dividend yield.

Result and input notes

Returns the option value in the same units as S and K. Use positive values for S, K and T, and a non-negative volatility. The risk-free rate and dividend yield use the continuously compounded convention of Black-Scholes.

The model assumes constant volatility and rates. It does not model early exercise or a schedule of individual cash dividends.

FT.VOL — Historical Volatility

Calculates annualised historical volatility from a series of prices. Arrange prices from oldest to newest, using observations at a consistent frequency.

Syntax

=FT.VOL(prices, [period])

Function parameters
Parameter	Description	Default
prices	Range of historical prices, ordered from oldest to newest. Use a single column or row of positive numeric prices, without a header.	Required
period	Optional observation frequency: "D" = daily, "W" = weekly, "M" = monthly, "Q" = quarterly, "H" = half-yearly, "Y" = yearly.	"D"

Volatility formula

\sigma_{\mathrm{annual}}=\operatorname{SD}_{\mathrm{sample}}\!\left(\ln\frac{p_i}{p_{i-1}}\right)\sqrt{m}

Take the sample standard deviation of successive log price returns, then multiply by the square root of the number of observations per year.

pᵢ / pᵢ₋₁: Current price / preceding price.
SDsample: Sample standard deviation of all log returns, using n − 1 for n returns.
m: Observations per year, determined by period.

Function parameters
Period	Frequency	Observations per year
D	Daily	m = 252
W	Weekly	m = 52
M	Monthly	m = 12
Q	Quarterly	m = 4
H	Half-yearly	m = 2
Y	Yearly	m = 1

The range is flattened in row order, values are converted using JavaScript Number, and only finite positive values are retained. Numeric text can therefore be included. At least three retained prices are required. Invalid period codes or insufficient prices produce an invalid numeric result.

Removing a price joins the observations on either side into one return. Clean gaps in your source series so the remaining observations still match the selected frequency.

Examples

=FT.VOL(A2:A253)

Calculates annualised volatility from daily prices in A2:A253.

=FT.VOL(B2:B61, "M")

Calculates annualised volatility from monthly prices in B2:B61.

Result and input notes

Returns annualised volatility as a decimal. A result of 0.40 means 40%; format the result cell as a percentage to display it that way.

Match period to the frequency of your prices. Use consistently adjusted prices where appropriate to avoid artificial jumps from stock splits. Historical volatility measures past price variation; it is not a forecast of future volatility.

FT.DLOM — Discount for Lack of Marketability

Estimates a Discount for Lack of Marketability using the Chaffe, Finnerty or Simplified Finnerty model. DLOM represents a discount associated with an investment's limited marketability.

Syntax

=FT.DLOM(T, sigma, [r], [q], [type])

Function parameters
Parameter	Description	Default
T	Expected time to liquidity in years. For example, 2 means two years.	Required
sigma	Annualised equity volatility, entered as a decimal. For example, 0.70 means 70%.	Required
r	Optional annual risk-free (Treasury) rate, entered as a decimal. Used by the Chaffe model.	0
q	Optional annual dividend yield, entered as a decimal. Used by full Finnerty and Chaffe; ignored by Simplified Finnerty.	0
type	Optional model: "C" = Chaffe, "F" = Finnerty, "S" = Simplified Finnerty.	"F"

Model notation

D: DLOM as a decimal; 0.25 represents 25%.
T: Expected time to liquidity / restriction period, in years.
σ: Annualised equity volatility (the sigma argument).
r / q: Continuously compounded annual risk-free rate / dividend yield.
N(x): Standard normal cumulative distribution function.

Full Finnerty — type = "F"

D_F=e^{-qT}\left[2N\!\left(\frac{a}{2}\right)-1\right]

Here, a is the adjusted volatility-time term calculated from σ and T by the full Finnerty model. This model uses T, σ and q; r does not affect the result.

Simplified Finnerty — type = "S"

D_S=1-2N\!\left(-\frac{\sigma\sqrt{T/3}}{2}\right)

Uses T and σ only. Assumes r = 0 and q = 0; supplied r and q values do not affect the result.

Chaffe — type = "C"

D_C=1-2N\!\left(-\frac{\sigma\sqrt{T}}{2}\right)

Shown for r = 0 and q = 0. When either rate is non-zero, FT.DLOM uses the full at-the-money Black-Scholes put calculation with those rates.

Examples

=FT.DLOM(2, 0.70)

Estimates DLOM using Finnerty, a two-year time to liquidity, 70% volatility and the default rates.

=FT.DLOM(2, 0.70, 0.05, 0, "C")

Uses Chaffe with a 5% risk-free rate and no dividends.

=FT.DLOM(2, 0.70, 0, 0, "S")

Uses Simplified Finnerty with a two-year time to liquidity and 70% volatility.

Result and input notes

Returns the discount as a decimal. A result of 0.25 means 25%. Use a positive time to liquidity and a non-negative volatility.

To apply a 25% discount to a value of 100, calculate 100 × (1 − 0.25) = 75. The model estimate depends on your assumptions; selecting the model and inputs requires judgement about the investment and its liquidity restrictions.

Entering formulas

Examples use commas between arguments. If your Excel regional settings use semicolons, replace the commas with semicolons. Put text arguments such as "P", "M" or "F" in double quotes.

If Excel does not recognise FT.BSE, FT.VOL or FT.DLOM or Google Sheets does not recognise FT_BSE, FT_VOL or FT_DLOM, check that the Fontics add-in is installed and loaded. If a formula returns an error, check the input values, range and quoted text arguments.