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.
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).
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
Measure
Value
Simple beta, reported returns
0.50
Dimson beta, one lag
0.75
Dimson beta, two lags
0.88
Dimson beta, three lags
0.97
R squared, simple against three lags
0.61 against 0.89
Smoothing parameter, estimated
0.576
Simple beta, unsmoothed returns
1.20
Dimson beta, three lags, unsmoothed
1.15
Annualized volatility, market
14.0%
Annualized volatility, fund reported
9.0%
Annualized volatility, fund unsmoothed
17.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
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.
Keep 44 quarters, or adjust the ranges: every regression on Results uses rows 14 to 53, so all eight lines share the same sample.
Leave the smoothing parameter blank to use the estimated autocorrelation, or type a value you trust more. Above 0.95 the filter is capped.
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.
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.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
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.