From prices to returns.
Shift, divide, take the log.

Two returns, one ratio.

Every return starts from the gross return g: a price divided by an earlier price. The log-return takes its natural logarithm; the simple return subtracts one.

The first example of the running sums and products workbook shows the first property with monthly returns of three funds (sheet By column): the running product of the gross returns gives the growth of 1 invested, the running sum of the log-returns gives the cumulative log-return, and the exponential of the sum equals the product.

=fx_CumProduct(1 + n_Returns)       growth of 1 invested: running product of g
=fx_CumSum(LN(1 + n_Returns))       cumulative log-return: running sum of r = ln(g)
=MAX(ABS(EXP(L9#) - H9#))           check: 4.4E-16, EXP of the sum = the product

For small moves the two are close and the gap grows with the size of the move: in the workbook, a simple return of −5.04% is a log-return of −5.17%.

A column of prices, and a name that follows it.

Type or paste the closing prices in one column, oldest on top; in the workbook they start in C9. Then give the column a name in Formulas > Name Manager, with the trim operator :., and a second name for its length:

d_HistPrices   ='LogReturns (1 period)'!$C$9:.$C$1000
n_NObs         =ROWS(d_HistPrices)

C9:.C1000 is the range C9:C1000 without its trailing empty cells, the same as TRIMRANGE(C9:C1000, 2). The name therefore ends at the last price, whatever the length of the series, and grows when you add prices below. Every formula on this page uses d_HistPrices and n_NObs, so none of them has to change when the data do. The prefixes follow one convention across the series: d_ for a dynamic range, one that changes size (a spill or a trimmed range such as this one), n_ for every other name (inputs, constants, single results), fx_ for LAMBDA functions.

The trim operators need Excel for Microsoft 365 or Excel for the web. The prices can come from any source: a download, a data provider or a table copied from a report.

Drop the first, drop the last.

With n prices there are n − 1 one-period returns. The numerator is the price column without its first price, the denominator the same column without its last price: the two are aligned row by row, so one division gives every ratio.

=LET(Prices_, d_HistPrices,
  LN(DROP(Prices_, 1) / DROP(Prices_, -1)))

LET gives the column the short name Prices_, the same name fx_LogReturn and fx_Return use for their argument, so the formula already reads like the function it becomes in section 05. For simple returns, drop the logarithm and subtract one: =LET(Prices_, d_HistPrices, DROP(Prices_, 1) / DROP(Prices_, -1) - 1).

Enlarge

Seven formulas, one rule

There are several ways to carry out the same calculation dynamically, with older Excel functions (INDEX, OFFSET) and newer ones (DROP, TAKE, SEQUENCE, LAMBDA). Here are the possible choices; our preference is the first one, LN + DROP.

DROP and TAKE need neither the number of prices nor a fixed size; INDEX and OFFSET need n_NObs.

Overlapping windows or non-overlapping blocks.

A weekly return from daily prices spans k = 5 periods; a monthly one about 21. There are two ways to cut the series into k-period returns.

Enlarge

Overlapping: shift by k

Shift by k instead of 1. Every price but the last k starts a window, so n prices give n − k returns, and consecutive returns share k − 1 periods:

=LET(Prices_, d_HistPrices,
  LN(DROP(Prices_, n_k) / DROP(Prices_, -n_k)))

Overlapping returns use all the data, but neighbouring returns are strongly correlated even when the one-period returns are not: statistics that assume independent observations, such as the usual standard errors, look more precise than they are.

Enlarge

Non-overlapping: blocks anchored at the latest price

Blocks that do not overlap give m = ROUNDDOWN((n − 1) / k, 0) returns. When k does not divide n − 1, q = MOD(n − 1, k) periods are left over. The workbook puts them at the start, so that the last return always ends on the latest price: the end positions are n, n − k, n − 2k, …, and SORT puts them back in date order.

=LET(
  Prices_, d_HistPrices,
  n_,      n_NObs,
  m_,      ROUNDDOWN((n_ - 1) / n_k, 0),
  endIx_,  SORT(SEQUENCE(m_, 1, n_, -n_k)),
  LN(INDEX(Prices_, endIx_) / INDEX(Prices_, endIx_ - n_k))
)

With 22 prices and k = 6, the blocks end at 10, 16 and 22 and the first three prices are not used. Anchoring at the first price instead would end the blocks at 7, 13 and 19 and leave out the three most recent prices, usually the ones that matter most.

Enlarge

fx_Return: one function for every case.

fx_Return(Prices_, [k_], [overlap_], [log_]) brings the two branches and the two kinds of return together. The optional arguments default to one period, overlapping windows and log-returns, so =fx_Return(d_HistPrices) equals fx_LogReturn. The flags accept TRUE/FALSE or 1/0.

The two functions are independent: define either one on its own. The workbook has both. How to add a LAMBDA in Name Manager.

=LAMBDA(Prices_,
  LET(
    Text1_, "Prices_: one column, oldest price on top",
    Text2_, "Shift rule: drop the first price (numerator), drop the last price (denominator)",
    num_,   DROP(Prices_, 1),
    den_,   DROP(Prices_, -1),
    Text3_, "One-period log-returns: n - 1 values, in date order",
    LN(num_ / den_)
  )
)

=LAMBDA(Prices_, [k_], [overlap_], [log_],
  LET(
    Text1_,  "Defaults: one period, overlapping windows, log-returns (flags accept 1/0 or TRUE/FALSE)",
    h_,      IF(ISOMITTED(k_), 1, k_),
    ov_,     IF(ISOMITTED(overlap_), TRUE, overlap_),
    lg_,     IF(ISOMITTED(log_), TRUE, log_),
    Text2_,  "Non-overlapping blocks end at n, n-k, n-2k, ...: anchored at the latest price, sorted into date order",
    n_,      ROWS(Prices_),
    endIx_,  SORT(SEQUENCE(ROUNDDOWN((n_ - 1) / h_, 0), 1, n_, -h_)),
    Text3_,  "Shift rule: numerator = later prices, denominator = prices k periods earlier",
    num_,    IF(ov_, DROP(Prices_, h_), INDEX(Prices_, endIx_, 1)),
    den_,    IF(ov_, DROP(Prices_, -h_), INDEX(Prices_, endIx_ - h_, 1)),
    Text4_,  "Gross return, then log-return or simple return",
    gross_,  num_ / den_,
    IF(lg_, LN(gross_), gross_ - 1)
  )
)

Enlarge

Both functions in the LAMBDA library

The slides.

The slides of the lesson, by Gianluca Fusai, Luca Alberici and Marco Migliardi, cover the same ground in more detail: log and simple returns, the shift rule and its alternatives, overlapping and non-overlapping windows on a small example, and the two LAMBDA functions.

Open the slides (PDF)

PDF · 34 slides · 0.35 MB · no email needed