Financial analyst: MQL recipes

Recipes

Mortgage payment and first-year interest
NPV, IRR and profitability index of a project
Minimum-variance portfolio of three assets
Exact decimals in a ledger
Beta, alpha and Sharpe ratio of a stock
Black-Scholes price of a call and a put
Nominal, effective and continuous rates
Covariance matrix and risk budget of a fund
Factor regression of a fund by QR decomposition
CAGR, geometric mean and portfolio P/E

executed on MentDB

Mortgage payment and first-year interest

A 200000 loan at 3.6% a year over 20 years is repaid monthly. The script computes the constant payment, sums the interest of the first 12 months from the outstanding balance formula and gives the total cost of the credit.

Pipeline

01 · paymentAnnuity formulamath calc→02 · balanceOutstanding balance formulaconcat→03 · interestSum of 12 months of interestnum sum→04 · roundAmounts to the centmath round

Script

admin

#1. Loan 200000 at 3.6% a year, 20 years, monthly;
-> "[P]" 200000;
-> "[r]" (math calc "0.036/12");
-> "[n]" 240;
#2. Constant monthly payment;
-> "[pmt]" (math round (math calc "[P]*[r]/(1-(1+[r])^-[n])") 2);
#3. Interest of the first 12 months, from the outstanding balance;
-> "[bal]" (concat [P] "*(1+" [r] ")^(k-1)-" [pmt] "*((1+" [r] ")^(k-1)-1)/" [r]);
-> "[int1]" (math round (num sum (concat "(" [bal] ")*" [r]) "k" 1 12) 2);
#4. Total cost of the credit;
concat "{\"payment\": " [pmt] ", \"interest_year1\": " [int1] ", \"principal_year1\": " (math round (math calc "12*[pmt]-[int1]") 2) ", \"total_interest\": " (math round (math calc "[n]*[pmt]-[P]") 2) "}";

Result

mentdb

{"payment": 1170.22, "interest_year1": 7085.96, "principal_year1": 6956.68, "total_interest": 80852.8}

Go further

Full amortisation table: num table on the balance expression with k from 0 to 240 step 12
Effective annual rate of the monthly rate: (1+r)^12-1 with math calc

executed on MentDB

NPV, IRR and profitability index of a project

An investment of 50000 brings cash flows of 14000 to 12000 over 5 years. The script discounts them at the 8% cost of capital, solves the internal rate of return where the NPV is zero, and gives the profitability index.

Pipeline

01 · npvDiscount at 8%math eval→02 · irrRate where NPV = 0num solve→03 · indexPI = PV / investmentmath round

Script

admin

#1. Cash flows of the project, year 0 to 5;
-> "[cf]" "-50000/(1+x)^0 + 14000/(1+x)^1 + 16000/(1+x)^2 + 18000/(1+x)^3 + 15000/(1+x)^4 + 12000/(1+x)^5";
#2. NPV at the 8% cost of capital;
-> "[npv]" (math round (math eval [cf] "{\"x\": 0.08}") 2);
#3. IRR: the rate where the NPV is zero;
-> "[irr]" (num solve [cf] "x" 0 1);
concat "NPV at 8%: " [npv] ", IRR: " (math round (* [irr] 100) 2) "%, profitability index: " (math round (/ (+ [npv] 50000) 50000) 3);

Result

mentdb

NPV at 8%: 10161.81, IRR: 15.56%, profitability index: 1.203

Go further

NPV profile for a chart: num table on the same expression for x from 0 to 0.3
Sensitivity of the NPV to the rate: num derivative at x = 0.08

executed on MentDB

Minimum-variance portfolio of three assets

Three assets have volatilities of 20%, 12% and 6% with known correlations. The script writes the portfolio variance with the third weight equal to 1 - a - b, minimises it with Newton's method and returns the weights, expected return and volatility.

Pipeline

01 · modelVariance w' Sigma wconcat→02 · optimiseMinimise in a and bnum minimize→03 · readWeights from JSONjson select→04 · returnExpected returnmath calc

Script

admin

#1. Three assets: volatilities 20%, 12%, 6%, correlations 0.4, 0.1, 0.2;
-> "[var]" "0.04*a^2 + 0.0144*b^2 + 0.0036*(1-a-b)^2 + 2*0.4*0.2*0.12*a*b + 2*0.1*0.2*0.06*a*(1-a-b) + 2*0.2*0.12*0.06*b*(1-a-b)";
#2. Minimum variance weights, the third weight is 1-a-b;
json load "rx_pf" (num minimize [var] "{\"a\": 0.33, \"b\": 0.33}");
-> "[a]" (json select "rx_pf" "/a");
-> "[b]" (json select "rx_pf" "/b");
#3. Expected return 9%, 6%, 3% and volatility of the portfolio;
-> "[ret]" (math calc "0.09*[a]+0.06*[b]+0.03*(1-[a]-[b])");
concat "{\"w1\": " (math round [a] 4) ", \"w2\": " (math round [b] 4) ", \"w3\": " (math round (- 1 (+ [a] [b])) 4) ", \"return\": " (math round [ret] 4) ", \"volatility\": " (math round (math sqrt (json select "rx_pf" "/value")) 4) "}";

Result

mentdb

{"w1": 0.0264, "w2": 0.1245, "w3": 0.8492, "return": 0.0353, "volatility": 0.0572}

Go further

Target a return: add the options of num minimize with a constraint on the expected return and bounds [0, 1]
Build the covariance matrix with matrix mul from the volatilities and the correlation matrix

executed on MentDB

Exact decimals in a ledger

Binary floating point does not reconcile a clearing account exactly. The script sums the movements in exact decimals against floats, shows banker rounding HALF_EVEN against HALF_UP on a mean fee, and computes compound growth factors exactly, with the limit (1 + r/n)^n close to e^r.

Pipeline

01 · sumExact sum and comparisonbigdec sum→02 · roundHALF_EVEN against HALF_UPbigdec round→03 · growthExact growth factorbigdec pow→04 · eContinuous compoundingbigdec e

Script

admin

#1. Movements of a clearing account that must reconcile to zero, in EUR;
-> "[tx]" "[1250.10, -320.45, 89.99, -0.10, 0.20, 1999.99, -1500.00, 0.30, -1519.73, -0.30]";
#2. Exact decimal sum against the binary float sum;
-> "[exact]" (bigdec strip (bigdec sum [tx]));
-> "[float]" (stat sum [tx]);
-> "[ok]" (bigdec compare [exact] 0);
#3. Mean of three card fees, rounded to the cent: banker HALF_EVEN against HALF_UP, and the reversal of the -1500.00 entry;
-> "[fee]" (bigdec mean "[2.345, 1.085, 0.125]" 20);
-> "[avg]" (bigdec round [fee] 2 HALF_EVEN);
-> "[rev]" (bigdec negate -1500.00);
#4. 10 years at 3.5 %: exact growth factor, and 1 + r/n ^n for n = 100000, close to e^r;
-> "[g]" (bigdec strip (bigdec pow 1.035 10 30));
-> "[cont]" (bigdec precision (bigdec pow 1.00000035 100000 30) 12);
concat "{\"sum_exact\": " [exact] ", \"compare_to_zero\": " [ok] ", \"sum_float\": " [float] ", \"float_error\": " (bigdec abs [float]) ", \"mean_fee\": " (bigdec strip [fee]) ", \"half_even\": " [avg] ", \"half_up\": " (bigdec round [fee] 2) ", \"reversal\": " [rev] ", \"growth_10y\": " [g] ", \"compound_n100000\": " [cont] ", \"exp_0.035\": " (math calc "exp(0.035)") ", \"e_40_digits\": " (bigdec e 40) "}"

Result

mentdb

{"sum_exact": 0, "compare_to_zero": 0, "sum_float": -4.546363285840016E-14, "float_error": 0.00000000000004546363285840016, "mean_fee": 1.185, "half_even": 1.18, "half_up": 1.19, "reversal": 1500.00, "growth_10y": 1.41059876062112218251219726563, "compound_n100000": 1.03561970246, "exp_0.035": 1.0356197087996233, "e_40_digits": 2.718281828459045235360287471352662497757}

Go further

bigdec div divides with a given number of significant digits and a rounding mode (DOWN, FLOOR...).
Use bigdec precision to present a result with a fixed number of significant digits.

executed on MentDB

Beta, alpha and Sharpe ratio of a stock

From twelve monthly returns of a stock and of its index, the analyst computes the CAPM beta (covariance over market variance), the Jensen alpha with a 2.4 percent risk-free rate, the correlation and R squared, the annualised volatility and the Sharpe ratio.

Pipeline

01 · betaCov / Varstat covariance→02 · varianceMarket variancestat variance→03 · alphaJensen alphamath calc→04 · fitCorrelationstat correlation→05 · riskVolatility and Sharpestat stddev

Script

admin

#1. Twelve monthly returns in percent of a stock and of its index, risk-free rate 2.4 percent a year;
-> "[stock]" "[2.6, -0.4, 3.1, 1.9, -2.9, 3.2, 2.5, -1.8, 2.0, -2.1, 4.4, 0.3]";
-> "[index]" "[1.5, -0.9, 2.6, 0.4, -1.8, 2.9, 1.1, -0.2, 1.8, -2.2, 2.4, 0.8]";
-> "[rf]" (/ 2.4 12);
#2. CAPM beta = cov / var of the market, Jensen alpha and correlation;
-> "[beta]" (/ (stat covariance [stock] [index]) (stat variance [index]));
-> "[ms]" (stat mean [stock]);
-> "[mi]" (stat mean [index]);
-> "[alpha]" (math calc "([ms]-[rf])-[beta]*([mi]-[rf])");
-> "[rho]" (stat correlation [stock] [index]);
#3. Annualised Sharpe ratio and the part of the risk explained by the market;
-> "[sd]" (stat stddev [stock]);
-> "[sharpe]" (math calc "([ms]-[rf])/[sd]*sqrt(12)");
concat "{\"beta\": " (math round [beta] 3) ", \"alpha_monthly_pct\": " (math round [alpha] 3) ", \"correlation\": " (math round [rho] 3) ", \"r_squared\": " (math round (* [rho] [rho]) 3) ", \"vol_annual_pct\": " (math round (math calc "[sd]*sqrt(12)") 2) ", \"sharpe\": " (math round [sharpe] 2) "}"

Result

mentdb

{"beta": 1.294, "alpha_monthly_pct": 0.219, "correlation": 0.919, "r_squared": 0.844, "vol_annual_pct": 8.26, "sharpe": 1.26}

Go further

A beta of 1.29 means the stock moves 29 percent more than the index: size a hedge with it
Check that the alpha is significant with stat ttest_1 on the monthly excess returns

executed on MentDB

Black-Scholes price of a call and a put

A trader prices a 9-month European option on a stock at 52 EUR, strike 50, rate 3 pct, volatility 28 pct. The script computes d1 and d2, the normal distribution function from the error function, the call, the put with the complementary error function, the delta and checks the put-call parity.

Pipeline

01 · d1d2d1 and d2math log→02 · normalN(x) from erfmath erf→03 · callCall and deltamath exp→04 · putN(-x) from erfcmath erfc

Script

admin

#1. European option on a stock at 52 EUR, strike 50, 9 months, rate 3 pct, volatility 28 pct;
-> "[S]" 52; -> "[K]" 50; -> "[T]" 0.75; -> "[r]" 0.03; -> "[sig]" 0.28;
-> "[d1]" (/ (+ (math log (/ [S] [K])) (* (+ [r] (/ (* [sig] [sig]) 2)) [T])) (* [sig] (math sqrt [T])));
-> "[d2]" (- [d1] (* [sig] (math sqrt [T])));
#2. Normal distribution function from the error function: N of x = 1/2 of 1 + erf of x / sqrt 2;
-> "[N1]" (* 0.5 (+ 1 (math erf (/ [d1] (math sqrt 2)))));
-> "[N2]" (* 0.5 (+ 1 (math erf (/ [d2] (math sqrt 2)))));
-> "[disc]" (* [K] (math exp (* -1 (* [r] [T]))));
#3. Black-Scholes call, put by the complementary function, and the put-call parity check;
-> "[call]" (- (* [S] [N1]) (* [disc] [N2]));
-> "[put]" (- (* [disc] (* 0.5 (math erfc (/ [d2] (math sqrt 2))))) (* [S] (* 0.5 (math erfc (/ [d1] (math sqrt 2))))));
concat "d1 " (math round [d1] 4) ", d2 " (math round [d2] 4) ", call " (math round [call] 4) ", put " (math round [put] 4) ", delta " (math round [N1] 4) ", parity C - P - S + K e^-rT = " (math round (+ (- (- [call] [put]) [S]) [disc]) 10)

Result

mentdb

d1 0.3758, d2 0.1333, call 6.5802, put 3.4677, delta 0.6465, parity C - P - S + K e^-rT = 0

Go further

erfc keeps its precision far in the tails, where 1 - erf would lose all digits
Find the implied volatility of a market price with num solve on the call formula

executed on MentDB

Nominal, effective and continuous rates

A bank must show the effective annual rate of a loan quoted at 4.2 pct APR compounded monthly, and a savings product pays 0.01 pct a day. The script converts the rates with log1p and expm1 (exact for small rates), checks with the power function and gives the doubling time of the savings.

Pipeline

01 · logln of 1 + r/12math log1p→02 · effectivee^x - 1math expm1→03 · check(1 + r/12)^12 - 1math power→04 · doublingln 2 / ln of 1 + rmath log

Script

admin

#1. A consumer loan quoted at a nominal APR of 4.2 pct compounded monthly, and a savings account at 0.01 pct a day;
-> "[apr]" 0.042; -> "[daily]" 0.0001;
#2. Effective annual rate e^ 12 ln of 1 + r/12 minus 1, exact for small rates with log1p and expm1;
-> "[ear]" (math expm1 (* 12 (math log1p (/ [apr] 12))));
-> "[cont]" (* 12 (math log1p (/ [apr] 12)));
-> "[ear_day]" (math expm1 (* 365 (math log1p [daily])));
#3. Same result with the power function, and the doubling time of the savings;
-> "[check]" (- (math power (+ 1 (/ [apr] 12)) 12) 1);
-> "[double]" (/ (math log 2) (math log1p [daily]));
concat "EAR " (math round (* [ear] 100) 4) " pct, continuous rate " (math round (* [cont] 100) 4) " pct, check with power " (math round (* [check] 100) 4) " pct | savings EAR " (math round (* [ear_day] 100) 4) " pct, doubles in " (math round (/ [double] 365) 1) " years"

Result

mentdb

EAR 4.2818 pct, continuous rate 4.1927 pct, check with power 4.2818 pct | savings EAR 3.7172 pct, doubles in 19 years

Go further

For a daily rate of 1e-10 math power returns noise, log1p and expm1 stay exact
The continuous rate 4.1927 pct is the one used in the Black-Scholes formula

executed on MentDB

Covariance matrix and risk budget of a fund

The risk manager has the volatilities of equities, bonds and gold and their correlation matrix. The script builds the covariance matrix D.C.D with a diagonal matrix of the volatilities, computes the portfolio volatility and the share of the risk taken by each asset.

Pipeline

01 · diagDiagonal of the volatilitiesmatrix diag→02 · covD.C.Dmatrix mul→03 · variancew.Cov.wmatrix mul→04 · volSquare rootmath sqrt

Script

admin

#1. Annual volatilities of equities, bonds and gold, their correlation matrix and the weights of the fund;
-> "[vol]" "[0.18, 0.06, 0.15]";
-> "[corr]" "[[1, -0.20, 0.10], [-0.20, 1, 0.30], [0.10, 0.30, 1]]";
-> "[w]" "[0.60, 0.30, 0.10]";
#2. Covariance matrix D.C.D with D the diagonal matrix of the volatilities;
-> "[D]" (matrix diag [vol]);
-> "[cov]" (matrix mul (matrix mul [D] [corr]) [D]);
#3. Portfolio variance w.Cov.w, volatility and the risk contribution of each asset;
-> "[cw]" (matrix mul [cov] [w]);
json load "rx_pv" (matrix mul (concat "[" [w] "]") [cw]);
-> "[var]" (json select "rx_pv" "/[0]");
-> "[sd]" (math sqrt [var]);
json load "rx_cw" [cw]; json load "rx_w" [w]; -> "[rc]" "";
for (-> "[i]" 0) (< [i] 3) (++ "[i]") {
	-> "[rc]" (concat [rc] (math round (/ (* (json select "rx_w" (concat "/[" [i] "]")) (json select "rx_cw" (concat "/[" [i] "]"))) [var]) 3) " ");
};
json unload "rx_pv"; json unload "rx_cw"; json unload "rx_w";
concat "cov " [cov] " | volatility " (math round (* [sd] 100) 2) " pct | risk contributions equity bonds gold: " (string trim [rc])

Result

mentdb

cov [[0.0324,-0.0021599999999999996,0.0026999999999999997],[-0.00216,0.0036,0.0026999999999999997],[0.0026999999999999997,0.0026999999999999997,0.0225]] | volatility 10.92 pct | risk contributions equity bonds gold: 0.959 0.001 0.039

Go further

Equities weigh 60 pct of the money but 96 pct of the risk: a risk parity fund would lower them
Check that the correlation matrix is valid with matrix cholesky: it fails if it is not positive definite

executed on MentDB

Factor regression of a fund by QR decomposition

The analyst explains eight monthly excess returns of a fund by the market and size factors. The script decomposes the design matrix by Householder QR, solves the triangular system R.b = Qt.y for alpha and the loadings, reads the residual sum of squares from the rest of Qt.y and checks with the least squares solver.

Pipeline

01 · qrHouseholder QRmatrix qr→02 · projectQt.ymatrix transpose→03 · rowsSquare part of Rmatrix row→04 · solveAlpha and betasmatrix solve

Script

admin

#1. Eight monthly excess returns in pct of a fund, of the market and of the size factor SMB;
-> "[y]" "[2.1, -1.4, 3.0, 0.6, -2.2, 1.8, 2.6, -0.5]";
-> "[X]" "[[1, 1.5, 0.4], [1, -1.2, -0.3], [1, 2.4, 0.9], [1, 0.3, 0.2], [1, -1.9, -0.6], [1, 1.1, 0.8], [1, 2.0, 0.5], [1, -0.4, -0.1]]";
#2. Householder QR of the design matrix, then Qt.y;
json load "rx_qr" (matrix qr [X]);
json load "rx_qty" (matrix mul (matrix transpose (json select "rx_qr" "/Q")) [y]);
#3. Upper triangular system R1.b = first 3 values of Qt.y, the other values are the residuals;
-> "[R]" (json select "rx_qr" "/R");
-> "[R1]" (concat "[" (matrix row [R] 1) "," (matrix row [R] 2) "," (matrix row [R] 3) "]");
-> "[c]" (concat "[" (json select "rx_qty" "/[0]") "," (json select "rx_qty" "/[1]") "," (json select "rx_qty" "/[2]") "]");
json load "rx_b" (matrix solve [R1] [c]);
-> "[ssr]" 0;
for (-> "[i]" 3) (< [i] 8) (++ "[i]") {-> "[ssr]" (+ [ssr] (math power (json select "rx_qty" (concat "/[" [i] "]")) 2));};
-> "[out]" (concat "alpha " (math round (json select "rx_b" "/[0]") 4) " pct a month, market beta " (math round (json select "rx_b" "/[1]") 4) ", SMB loading " (math round (json select "rx_b" "/[2]") 4) ", residual SS " (math round [ssr] 4) " | same with matrix solve: " (matrix solve [X] [y]));
json unload "rx_qr"; json unload "rx_qty"; json unload "rx_b";
[out]

Result

mentdb

alpha 0.1227 pct a month, market beta 1.1048, SMB loading 0.4556, residual SS 0.0915 | same with matrix solve: [0.12271442548102973,1.1047939993477518,0.45559299923905805]

Go further

QR is the stable way to solve least squares: no Xt.X product that squares the condition number
Add the value factor HML as a fourth column and compare the residual sums

executed on MentDB

CAGR, geometric mean and portfolio P/E

From the yearly net asset values of a fund the analyst computes the compound annual growth rate with an n-th root, shows that the geometric mean of the growth factors gives the same figure while the arithmetic mean overstates it, and computes the P/E of an equal weight portfolio, which is the harmonic mean of the P/E ratios.

Pipeline

01 · cagrn-th root of end / startmath nroot→02 · factorsYearly growthjson iarray→03 · geometricGeometric meanstat geo_mean→04 · arithmeticArithmetic meanmath avg→05 · peHarmonic mean P/Estat harm_mean

Script

admin

#1. Net asset value of a fund at the end of each year, and the P/E ratios of its five holdings with equal amounts;
json load "rx_nav" "[100, 112.4, 104.9, 121.7, 133.0, 128.6, 146.2]";
-> "[pe]" "[12.5, 18.0, 9.8, 32.0, 15.2]";
#2. Compound annual growth rate: n-th root of end over start, minus 1;
-> "[n]" (- (json count "rx_nav" "/") 1);
-> "[cagr]" (- (math nroot (/ (json select "rx_nav" (concat "/[" [n] "]")) (json select "rx_nav" "/[0]")) [n]) 1);
#3. Yearly growth factors: the geometric mean gives the CAGR again, the arithmetic mean overstates it;
json load "rx_g" "[]";
for (-> "[i]" 1) (<= [i] [n]) (++ "[i]") {json iarray "rx_g" "/" (/ (json select "rx_nav" (concat "/[" [i] "]")) (json select "rx_nav" (concat "/[" (- [i] 1) "]"))) NUM;};
-> "[geo]" (- (stat geo_mean (json doc "rx_g")) 1);
-> "[ari]" (- (math avg (json doc "rx_g")) 1);
#4. P/E of an equal weight portfolio is the harmonic mean of the P/E ratios;
-> "[out]" (concat "CAGR " (math round (* [cagr] 100) 3) " pct, geometric " (math round (* [geo] 100) 3) " pct, arithmetic " (math round (* [ari] 100) 3) " pct | portfolio P/E " (math round (stat harm_mean [pe]) 2) " vs average P/E " (math round (math avg [pe]) 2));
json unload "rx_nav"; json unload "rx_g";
[out]

Result

mentdb

CAGR 6.535 pct, geometric 6.535 pct, arithmetic 6.901 pct | portfolio P/E 14.94 vs average P/E 17.5

Go further

The average P/E 17.5 is wrong for the portfolio: an equal amount buys more earnings in the cheap stocks
The gap between arithmetic and geometric means grows with the volatility, about variance / 2