On Jan 7, 3:56 am, "Carl LaFong" wrote previously:
I am trying to compare the rates of return on an investment account to other
benchmarks, such as the SP 500 or certain Vanguard and Fidelity mutual
funds. I am using Excel 2007.

Please forgive the incessant postings, but my first posting (and
errata <sigh) probably was not very helpful insofar as it was not
dispositive. Perhaps the following will offer more insight.

For the following, consider a different hypothetical investment. It
is probably similar to yours, but since you did not post the details,
I chose my own "nice" numbers. Consequently, the bottom line is
slightly different.

[Note: I wrote this follow-up before I saw yours, which now does
include some real numbers for you. However, it is still lacking
sufficient detail for me to do an XIRR analysis -- or for me see any
mistakes that you might be making in using XIRR. So I decided to
stick with my own hypothetical example.]

Consider an initial investment of $10,000 on 1/1/2007 followed by
periodic investments of $100 on the first of each subsequent month.
Suppose on 1/1/2008, the investment is worth about $12.939.88 [1].

Normally, industry benchmarks and fund prospectuses do not take
periodic investments into account. (Annuity prospectuses do, at least
during the investment phase.) However, they do take reinvested
dividends into account. So for the sake of argument, assume the $100/
month investment represents reinvested dividends for comparison
purposes.

Breaking this down by quarter, we might see the following, with
investments represented by negative numbers:

1/1/2007 -$10,000
2/1/2007 -$100
3/1/2007 -$100
4/1/2007 $10,129.30 (quarter-end value)

Suppose we compute the quarterly "total return" (distinct from the
simple "return", which does not take reinvested dividends into
account) as follows, based on what I had asserted in my previous
posting (viz. endBalance/startBalance - 1):

(10129.30 / 10000) - 1 = 1.2930% (approximately)

For the subsequent quarters, the "initial investment" is the ending
balance plus the $100 reinvested dividend (and remember to negate
it). Thus, we might see the following:

4/1/2007 -$10,229.30 (quarter-end value $10,129.30 plus $100
investment)
5/1/2007 -$100
6/1/2007 -$100
7/1/2007 $10,997.35 (quarter-end value)
8.5697% (approximate quarterly rate, calculated
as above)

7/1/2007 -$11,097.35
8/1/2007 -$100
9/1/2007 -$100
10/1/2007 $11,633.21
5.7820%

10/1/2007 -$11,733.21
11/1/2007 -$100
12/1/2007 -$100
1/1/2008 $12,938.88
11.2236%

Note that (1+1.2930%)*(1+8.5697%)*(1+5.7820%)*(1+11.2236%) [2] is
29.3888%, which is indeed the same as:

(12938.88 / 10000) - 1

However, those quarterly and annual rates are wrong(!). I know that
because I engineered the quarterly and year-end values based on
foreknowledge of hypothetical actual quarterly rates [1].

As I will show below, if you find that a benchmark or fund annual rate
of return is the product of the quarterly rates of return, I believe
they are not taking reinvested dividends into account. That is, you
might be looking at the simple returns, not the total returns. Or
they are simply using the "endBalance/startBalance" approach, which on
second thought now would surprise me.

An XIRR construction of this scenario would be (in A1:B13):

1/1/2007 -10000
2/1/2007 -100
3/1/2007 -100
4/1/2007 -100
5/1/2007 -100
6/1/2007 -100
7/1/2007 -100
8/1/2007 -100
9/1/2007 -100
10/1/2007 -100
11/1/2007 -100
12/1/2007 -100
1/1/2008 12938.88

The XIRR result is 17.4522% (approximately).

If we apply XIRR to each of the quarters, set up as above for the
"endBalance/startBalance" analysis, we get the following quarterly
return rates [3]: -0.7%, 5.5%, 3.0% and 8.5%. Those are the same as
the hypothetical quarter rates that I used to derive the example.

Thus, the quarterly XIRR does indeed compute the correct quarterly
market rates of appreciation.

However, (1-0.7%)*(1+5.5%)*(1+3.0%)*(1+8.5%)-1 [2] is 17.0762%
(approximately), not 17.4522%. This is also true if I "normalize" the
quarterly results [4], which results in an annual rate of 16.9760%.

I have not given any thought to why this "discrepancy" exists. I
believe there is a mathematical explanation -- perhaps something
similar to the fact that the average of averages of different size
groups is not equal to the average of the whole. (But I would think
that my "normalization" approach would correct for that particular
explanation.)

For the same reason, I cannot say, with impunity, which annual rate is
correct mathematically. I believe it is the first XIRR based on the
complete annual cash flow. But I am relunctant to say that "for
sure".

But my point is: even when the quarterly XIRRs are computed
"correctly" (i.e. they correctly reflect the true rate of return for
the period), the product of the quarterly ratess does not equal the
annual XIRR.

So I conclude that if a benchmark or fund annual rate of return is
equal to the product of the quarterly rates of return (including any
reinvested dividends for the quarter), they "must" be using the
"endBalance/startBalance" approach. But I suspect they would do that
only for simple "returns", not "total returns" (which include
reinvested dividends).

FYI, I have always had trouble validating a funds "total return" rate
by simply taking the product of its stated quarterly "total return"
rates. I suspect now that this explains why. That is, I suspect that
you will find that "total return" rates do indeed take the timing of
reinvested dividends into account, effectively computing the XIRR.

HTH. I apologize for the lengthy "explanation". I had taken some
things for granted myself.




Endnotes:

[1] The quarterly and year-end values were derived by actually
applying the following quarterly appreciation rates to the periodic
investments: -0.7%, 5.5%, 3.0% and 8.5%.

[2] When multiplying rates (1+q1)*(1+q2)*...., the actual computed
values are used, not the approximate values shown here.

[3] The quarterly XIRR is computed as you did, namely (for the first
quarter, for example):

=( 1+xirr(B1:B4, A1:A4) ) ^ ( (A4-A1) / 365 ) - 1

[4] Quarterly XIRR results are normalize as follows (for the first
quarter, for example):

=(1 - 0.7%) ^ ( 365 / 4 / (A4 - A1) )



The calculated XIRR for the account for one year is 20.11%. Using a
hypothetical $10,000 beginning balance, the account generates a final
balance of $12,011.

The POWER and RATE functions reveal that the equivalent quarterly return is
about 4.69%.

That is: 10,000 x 1.0469 x 1.0469 x 1.0469 x 1.0469 = 12012.15, allowing for
rounding error. Of course, in the real world, the quarterly returns vary
from quarter to quarter.

I have separately calculated the individual quarterly XIRRs as:

Q1: -.70

Q2: 5.41

Q3: 3.01

Q4: 8.34

I used this formula for the first quarter, where the dates are in column A
and the amounts are in column B:

=((1+XIRR(B7:B14,A7:A14,0))^((A14-A7)/365)-1)*100

Here is the problem: 10000 x .9930 x 1.0541 x 1.0301 x 1.0834 = 11681, not
12011.

For comparison, here are returns for the Vanguard Wellington mutual fund,
using Vanguard's own figures:

2007 annual return: 8.34

10000 x 1.0834 = 10834 final value

2007 individual quarterly returns: 1.14, 4.91, 3.01, -.88.

10000 x 1.0114 x 1.0491 x 1.0301 x .9912 = 10834 final value

The final balance using the annual return for Wellington is the same as the
final balance using individual quarters.

I see no errors in my data entry, so I must assume that XIRR does not in
fact provide quarterly results that can be accurately compared to benchmarks
such as Vanguard Wellington.

Why is that? How can my quarterly returns yielding a final value of 11681 be
reconciled with the annual return yielding a final value of 12011? If they
cannot be reconciled, how do I accurately calculate quarterly returns that
can be compared to standard benchmarks?

I am told that XIRR always gives an effective annual rate, even when used
for quarterly calculations. Perhaps that plays into this, but I don't know
how to arrive at quarterly returns that agree with the calculated annual
XIRR.

I can provide the actual values and dates if needed, but first want to check
my understanding.

Thanks for any assistance.