The tracking spreadsheet and adjusted returns

One stock, one week

Halton Dairy Co-op closes at 40.00 on Friday and 41.20 the next Friday. The raw weekly return is =41.20/40-1 = 3.00 %. But Halton went ex-dividend on the Wednesday and paid 0.30 a share. The holder earned the dividend as well as the price move, so the week's total return is

$$r_t=\frac{P_t+D_t}{P_{t-1}}-1=\frac{41.20+0.30}{40.00}-1$$

=(41.20+0.30)/40-1 = 3.75 %. Lesson 13.1's rule — total return includes the dividend — applies to every week of the tracking sheet. The alternative is to download the adjusted close, which the data provider has already restated for dividends and splits; then the raw formula is right. Use one or the other, never both, and say which on the sheet.

The trap runs the other way too. On the ex-date the price drops by roughly the dividend with nothing having gone wrong. A raw close series shows every dividend payer taking a small loss each quarter, and a group that tracks raw closes concludes that the banks and utilities in its basket "underperformed" — a finding manufactured by the spreadsheet.

Several weeks — compound, do not add

Three weekly returns: 2 %, −1 %, 1.5 %. The cumulative return is the product of the growth factors, not the sum of the rates:

$$r_{\text{cum}}=\prod _t(1+r_t)-1=1.02\times 0.99\times 1.015-1$$

=PRODUCT(1+E3:E5)-1 = 0.024947 — 2.49 %, against a naive sum of 2.50 %. Over three small weeks the gap is a hundredth of a point; over twelve weeks with ±5 % swings it is the difference between a right answer and a wrong one, and the paper marks the value. Add a fourth week of 0.8 % and the cumulative is =PRODUCT(1.02,0.99,1.015,1.008)-1 = 3.31 %.

The 1+E3:E5 inside PRODUCT adds 1 to every cell of the range before multiplying — in current Excel it spills automatically; in older versions confirm it with Ctrl+Shift+Enter. =GEOMEAN(1+range)-1 gives the average weekly rate instead (Lesson 13.1).

The basket's return — equal-weighted

The brief says nothing about how much to put in each stock, so the convention is equal weight at the start — one twelfth of the money in each — and no rebalancing unless the group says so. Then the basket's cumulative return is the simple average of the twelve stocks' cumulative returns. Four of the chosen twelve finish the term at 5 %, −2 %, 3 %, 1 %: =AVERAGE(0.05,-0.02,0.03,0.01) = 1.75 %. With twelve, the range is =AVERAGE(F3:F14). That is Lesson 13.2's weighted portfolio return with every weight 1/12, which is why SUMPRODUCT collapses to AVERAGE.

Four of the random twelve: 1 %, 4 %, −3 %, 2.5 % → =AVERAGE(...) = 1.125 %. The chosen four are ahead by 0.625 points — and Lesson 14.1's simulation has already told you how little that means.

The sheet's columns

Week · Close · Dividend · Weekly return · Cumulative per stock, with the two indices in the same layout (an index pays no dividend cell — use the total-return version of the index if the provider gives it, and say so). One block per basket, a row of AVERAGE under each, and a chart of the two basket cumulatives against the two indices. That chart is the presentation.

Cold

Weekly return = (close + dividend) ÷ prior close − 1, or the adjusted close with the raw formula; cumulative = PRODUCT(1 + range) − 1; the basket = AVERAGE of its stocks' cumulatives.