suggested by Mike
Once upon a time I wrote a longwinded tutorial on how one calculates the
Rate of Return, wherein Newton and
Dietz played a role and ...
>Yeah, I remember ... longwinded 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 P_{1} and
 the investments for that month are A_{1}, A_{2}, ... A_{N}
(being negative in the case of a withdrawal) and
 these dollar values are invested for fractions of a month: T_{1}, T_{1}, ... T_{N} and
 the market value of our portfolio at the end of the month is P_{2}
(before any firstofthemonth 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
r_{1}, r_{2}, ... r_{N},
then an approximate annualized return for the portfolio is:
R = {
(1+r_{1})(1+r_{2})...(1+r_{N})
}^{12/N}  1
or, for N = 12 months:
R =
(1+r_{1})(1+r_{2})...(1+r_{N})  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 longwinded ... ?
Here's a picture of the spreadsheet:


Just RIGHTclick 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 endofmonth portfolio values. But, in order to maximize the funcoefficient, 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 approximatequickanddirty, annual return by assuming that
all the Ts are fractions of a year and all investments occur half way through the year
so T_{n} = 1/2.
The approximatequickanddirty formula then becomes:
where A is the sum of all investments during the year.
>Example?
We start with P_{1} = $50K, invest another A = $25K and end the year with P_{2} = $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 allatonce after N months or maybe ...?
>Example?
For the case: $25K invested allatonce, XIRR gives


>Allatonce, after half a year ... it looks pretty good, eh?
Yes, 8% quickanddirty 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%
