Dietz       ... modified
suggested by Mike

Once upon a time I wrote a long-winded tutorial on how one calculates the Rate of Return, wherein Newton and Dietz played a role and ...

>Yeah, I remember ... long-winded and ...
Pay attention. The point is, many organizations (requiring "performance measurements"; see this and this and that), for reasons which I fail to understand, insist that practitioners actually use the modified Dietz approximation for calculating Rates of Return, so I thought ...

>You thought you'd regurgitate Dietz.
Well, I thought I'd put together a spreadsheet which Does the Dietz.

Let's review:

  • Our portfolio starts a particular month with a market value of P1 and
  • the investments for that month are A1, A2, ... AN (being negative in the case of a withdrawal) and
  • these dollar values are invested for fractions of a month: T1, T1, ... TN and
  • the market value of our portfolio at the end of the month is P2 (before any first-of-the-month investment)
then the modified Dietz approximate gain for that month is given by the magic formula:

and if these gains are computed for each of N successive months, say r1, r2, ... rN,
then an approximate annualized return for the portfolio is:

R = { (1+r1)(1+r2)...(1+rN) }12/N - 1

or, for N = 12 months:

R = (1+r1)(1+r2)...(1+rN) - 1

The approximation used in generating the above prescription is replacing
(1+r)T by 1+Tr which assumes that Tr is small, and for T a fraction of a month
(so T is no greater than 1) and r a monthly return (so r is most likely less than 0.01, meaning 1%) then it's not bad ... as seen here for T = 0.5

However, since the investments should get multiplied by (1+r)T and, in the approximation, they get multiplied by (1+Tr) instead, small errors get amplified by large investments and ...

>Is this going to be another long-winded ... ?

Here's a picture of the spreadsheet:

Just RIGHT-click and Save Target to download a .ZIPd file.

>I assume I get to fill in the guys inside the red boxes, like the dates and investments and ...
Yes, and the end-of-month portfolio values. But, in order to maximize the fun-coefficient, the spreadsheet generates numbers at random, inside the red boxes, so every time you press the F9 key you get a new set of numbers.

>And when I get tired of that?
You fill in the red boxes.

Quick-and-Dirty Dietz

This magic formula may also be used to give an approximate-quick-and-dirty, annual return by assuming that all the Ts are fractions of a year and all investments occur half way through the year so Tn = 1/2.

The approximate-quick-and-dirty formula then becomes:

where A is the sum of all investments during the year.

We start with P1 = $50K, invest another A = $25K and end the year with P2 = $80K.
The approximate return is (80 - 50 - 25) / (50 + 25/2) = 0.08 or 8%.

>And what does XIRR give?
It depends upon when and how the $25K was invested:
A piece of it every month or maybe all-at-once after N months or maybe ...?

For the case: $25K invested all-at-once, XIRR gives  

>All-at-once, after half a year ... it looks pretty good, eh?
Yes, 8% quick-and-dirty and XIRR agree pretty well. In fact, if the Jan 1/00 portfolio was $50K and a $25K investment was made on July 1/00 (after six months) and the Dec 31/00 portfolio was $80K, then XIRR gives an annual return (for the year 2000) of 8.03%