Free Excel templates

A free Dimson beta calculator in Excel, for smoothed private fund returns

The simple beta against the Dimson beta with one, two and three lags, on reported returns and on an unsmoothed series, with the volatility each one implies: four linked sheets, one fictional fund, every coefficient a live LINEST formula, no macros.

Reported returns of private funds are smoothed: valuations follow the market a quarter or two late, so a regression of the fund on the market in the same quarter misses part of the exposure. The Dimson beta regresses the fund on the market’s current and lagged returns and adds the coefficients up. On Harrow Capital Partners II, a fictional fund with 44 quarters of returns generated by a script, the simple beta on reported returns is 0.50. Adding one lag lifts it to 0.75, two lags to 0.88 and three to 0.97. Unsmoothing the series first, with a smoothing parameter of 0.576 estimated from the data, gives a simple beta of 1.20, and the reported volatility of 9.0% a year becomes 17.7%, against 14.0% for the market. Every one of those numbers is a formula you can move.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Dimson_Beta_Calculator.xlsx (17 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Private Markets Performance. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

The regression

R(t) = a + b0 M(t) + b1 M(t−1) + b2 M(t−2) + b3 M(t−3) + e(t)

Dimson beta = b0 + b1 + b2 + b3. With no lags, it is the simple beta.

What is in the file

Four sheets. Blue type on a pale blue fill is an input; black type is a formula.

Read Me
What the file does, how the data were generated, how to paste your own series, and its limits.
Data
44 quarters of the fund’s reported return and the market’s return, the three lagged market columns, the estimated autocorrelation, an optional override of the smoothing parameter, and the unsmoothed series r(t) = (R(t) − phi R(t−1)) / (1 − phi).
Results
Eight regressions on the same 40 quarters: simple and Dimson with one, two and three lags, on reported and on unsmoothed returns, each with its coefficients, their sum and the R squared; then annualized volatility three ways.
Checks
Seven identities that hold for any data, from LINEST against SLOPE and covariance over variance to re-smoothing the unsmoothed series, then the worked case. The file ends ALL OK.

What the worked case returns

MeasureValue
Simple beta, reported returns0.50
Dimson beta, one lag0.75
Dimson beta, two lags0.88
Dimson beta, three lags0.97
R squared, simple against three lags0.61 against 0.89
Smoothing parameter, estimated0.576
Simple beta, unsmoothed returns1.20
Dimson beta, three lags, unsmoothed1.15
Annualized volatility, market14.0%
Annualized volatility, fund reported9.0%
Annualized volatility, fund unsmoothed17.7%

Read the lag coefficients, not just their sum. On reported returns the market of the same quarter explains a beta of 0.49 in the three-lag regression, and the three previous quarters add 0.28, 0.13 and 0.07: the fund keeps catching up with the market for most of a year, which is what smoothing looks like in a regression. On the unsmoothed series the lags add nothing worth having (0.02, −0.08 and 0.01), because the filter has already moved the response back into the quarter it belongs to. The data were built with a beta of 1.10: both corrections land within 0.13 of it, against 0.60 for the simple beta on reported returns. The volatility tells the same story from the other side. Reported returns look less volatile than the market; unsmoothed, the fund is more volatile than the market, as a levered equity position should be.

How to use it on your own fund

  1. On Data, paste your fund’s reported quarterly returns and the market’s returns over the two blue columns, as decimals, oldest first. Use a total return index that matches the strategy.
  2. Keep 44 quarters, or adjust the ranges: every regression on Results uses rows 14 to 53, so all eight lines share the same sample.
  3. Leave the smoothing parameter blank to use the estimated autocorrelation, or type a value you trust more. Above 0.95 the filter is capped.
  4. Read Results line by line. When the Dimson beta stops rising as lags are added, you have enough lags; on quarterly private fund data that is usually two to four.
  5. Keep the Checks sheet. The identities hold for any data; the worked case rows stop matching once you paste your own series, which is expected.

Questions people ask about it

What is the Dimson beta?

A beta estimated by regressing an asset’s returns on the market’s returns of the same period and of earlier periods, then adding the coefficients. Elroy Dimson proposed it in 1979 for thinly traded shares, whose prices react to the market late. Private fund returns, which follow appraisals and quarterly valuations, show the same lag.

How many lags should I use?

Enough that adding one more stops changing the sum. On this fund the sum rises from 0.50 to 0.75, 0.88 and 0.97 as lags are added. On quarterly private fund data, two to four lags is typical; with few observations, each extra lag costs precision.

Dimson beta or unsmoothing?

They answer the same problem from two sides. The Dimson regression leaves the returns as reported and lets the lags pick up the delayed response. Unsmoothing rebuilds a return series first and regresses that. The file runs both on the same 40 quarters so you can see whether they agree.

Is this Dimson beta calculator really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click. It has no macros and no circular reference.

Where do the data come from?

A script with a fixed seed, 20261103, generated a market series, an economic fund return with a beta of 1.10 to it, and a reported return that keeps 60% of last quarter’s reported figure. The fund and the returns are fictional, and nothing here is investment advice.

The rest of the files

This calculator sits alongside the companion files for Private Markets Performance: the complete Chapter 19 measurement, the PME calculator, the practice cases, the measurement checklist and the cohort of twenty-four. Free, like this one.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Private Markets Performance. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.