"joeu2004" wrote in message
...
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.


Joe:

Thanks for going to the trouble. I will try to summarize what you and I have
found separately.

My investment account has an XIRR of 20.11, which means that a hypothetical
$10,000 would grow to $12,011 in one year. The separately calculated
individual quarterly XIRRs are -.70, 5.41, 3.01, and 8.34. However, the
product of those quarterly XIRRs gives an ending balance of $11,681 when
using a hypothetical opening balance of $10,000, rather than $12,011.

If I follow you, your example came to a similar outcome. Your known XIRR is
17.45, which gives an ending balance of $11,745 if applied to $10,000. The
quarterly XIRRs in your example are -.70, 5.50, 3.00, and 8.50. However, the
product of those quarterly returns is 17.0762%, aka $11707.62 for a
hypothetical $10,000 opening balance.

We agree.

Here is the issue. If you look at Vanguard's website, you will see the
following SEC approved 2007 return for their Wellington fund, using their
lingo:

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

The product of those quarters for a $10,000 opening balance is 10,000 x
1.0114 x 1.0491 x 1.0301 x .9912 = $10834. That matches their annual number,
whereas in our examples, the quarterlies do not match the annuals.

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. If I
use the annual number, the line moves to $12,011. That rate of error would
compound in subsequent years and would diverge considerably over a decade.

I could wait till year-end, assume XIRR is an accurate representation of
reality, calculate the XIRR at 20.11, and use RATE or POWER to find the
equivalent average quarterly rate (4.69). The resulting graph would show a
flat line for the entire year, ending at $12,011.

If I accept my quarterly rates, the graph is a line that changes direction
at the end of each quarter, with an ending value below the annual method.

Like you, I would assume the annual XIRR of 20.11 is correct, but how do I
compute quarterly total returns that correspond to what Vanguard does, so
that I can build my chart at the end of each quarter and directly compare it
to another fund or standard benchmark.

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.

Or are the quarterly returns yielding $11,681 a better representation, even
though they don't match annual XIRR?

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