On Jan 7, 6:02*pm, "Carl LaFong" wrote:
I am trying to build a graph in Excel that represents my total return based
on a hypothetical $10,000 investment, so the "correct" numbers are
important. If I use my quarterly numbers, the line moves to $11,681.

You are continuing to mix two different methodogies. You arrive at
$11,681 by computing the annual rate based on the product of the
quarterly XIRR rates. I have already demonstrated that that is
incorrect. And I tried to offer an explanation of why it is
incorrect, using a mathematical "argument" (not really a proof).

If you apply the quarterly XIRR correctly, you should compute exactly
your quarter-end values as well as your year-end value ($12,011).
Since you still have not included sufficient data (namely all of the
interim reinvestmented values and dates), I cannot demonstrate by
using your example.

However, I can demonstrate using the Wellington Fund (VWELX) data.
See below.


You summarized the VWELX performance as follows:
"Average annual return": 8.34 (this is aka total return, with all distributions
reinvested) .
"Total returns" for the 4 quarters: 1.14, 4.91, 3.01, -0.88 (likewise with all
distributions reinvested)."

Using the VWLEX distribution and price history, I have constructed the
following cash flows based on a hypothetical initial investment of
$10,000 on 12/31/2006. (Forgive me if the columns do not line up.)

Date Value Price/sh Shares Distrib/sh
12/31/2006 -10,000.00 32.43 308.3565
3/26/2007 -67.84 32.69 2.0752 0.2200
3/31/2007 10,116.97 32.59 310.4317

3/31/2007 -10,116.97 32.59 310.4317
6/25/2007 -83.82 32.69 2.5640 0.2700
6/30/2007 10,616.81 33.92 312.9956

6/30/2007 -10,616.81 33.92 312.9956
9/24/2007 -81.38 34.69 2.3459 0.2600
9/30/2007 10,932.89 34.67 315.3415

9/30/2007 -10,932.89 34.67 315.3415
12/28/2007 -553.11 32.63 16.9509 1.7540
12/31/2007 10,839.38 32.62 332.2925

Using the endBalance/startBalance methodology, I compute quarterly and
annual results that are "close" to what you reported, namely:
quarterly rates of 1.1697%, 4.9406%, 2.9772%, -0.8553%, and annual
rate of 8.3938%. I have not yet determined the cause of the small
discrepancy. I thought it might be the expense ratio of 0.30%. But
when I factor that in, prorated appropriately, my numbers still do not
match. Nonetheless, I think my numbers are close enough to validate
the table above.

The quarterly XIRRs are 0.4911%, 4.1103%, 2.2096% and -5.9044%. The
annual XIRR is 0.5259%. I will use the first quarter figures to
demonstrate how to derive the quarter-end value of $10,116.97.

First, the daily rate is derived in I1 from the quarterly XIRR by the
following formula:

=rate(A3-A1, 0, -1, 1+F3)

where A3 is 3/31/2007, A1 is 12/31/2006, and F3 is 0.4911%.

Then the quarter-end value is the sum of the appreciated values of
each cash flow, computed as follows:

=fv(I1, A3-A1, 0, B1) + fv(I1, A3-A2, 0, B2)

where B1 is -10,000.00, A2 is 3/26/2007, and B2 is -67.84. That
evaluates to 10,049.11 + 67.86, which is indeed 10,116.97, the quarter-
end value on 3/31/2007.

You can repeat that process for each quarter as well as for the annual
figure.

Thus, there is nothing inconsistent between the quarterly XIRRs and
the annual XIRR. They simply do not combine in the same manner as
quarterly and annual rates based on the endBalance/startBalance
method.


Barring another method, the only choice I see is to wait till year end and
use the RATE-computed quarterly averages and give up on using Excel to
directly calculate quarterly total returns that correspond to industry
standards.

The problem is not with Excel, but with your application of the
formulas. All of my computations are done using Excel.

As I have demonstrated, it appears that the "industry" (at least the
Wellington Fund) uses the endBalance/startBalance method. I believe I
stated that at the outset.


Any further insights? What assumptions would you make for the purposes of my
graph?

HTH.