CHAPTER 14

Building Your First Index in Excel

A five-stock index, ten days, every formula

Opening question

“I understand the formulas in the book. But could I actually calculate an index myself — with a divisor, a rebalance and a stock split — in a spreadsheet?”

In this chapter

How to design an index workbook: eight sheets, each with one job
The core formulas: SUMPRODUCT for market value, the divisor update, price and total-return levels
A five-stock, ten-day worked example with a dividend, a quarterly rebalance and a 2-for-1 split
Capping weights in a spreadsheet
Returns, volatility and maximum drawdown with STDEV.S and a running maximum
Charts that tell the truth
The most common spreadsheet errors and the checks that catch them

🔒

This chapter is in the full book

Chapter 1 is free to read online. The full text of chapter 14 — with its tables, figures, worked examples and practice lab — is available in the complete edition of Index Investing: From Theory to Practice (Volume 1, NXW Investment Library).

Read Chapter 1 free Get the full book — [email protected]

Sections of this chapter

14.1 Why start with a spreadsheet?

14.2 Designing the workbook

14.3 The example: five stocks over ten days

14.4 Weights and index shares

14.5 The daily calculation: market value, divisor, level

14.6 Dividends and the total-return index

14.7 The quarterly rebalance

14.8 The stock split

14.9 Returns, volatility and drawdown

14.10 Charts that tell the truth

14.11 Common spreadsheet errors and the checks that catch them

← Chapter 13: Data for Index Building Chapter 15: An Index Engine in R →