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:

>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.

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.
 >Example? 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 ...? >Example? 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%