Calculating quarterly investment returns---XIRR or another function??

[Carl LaFong]

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.

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.

 

[joeu2004]

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.

First, XIRR is probably overkill for this application.

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

That is an __average__ quarterly rate. Presumably you computed:

=rate(4, 0, -10000,12011)

I have separately calculated the individual quarterly XIRRs as:
Q1: -.70
Q2: 5.41
Q3: 3.01
Q4: 8.34

What are those figures: percentages or something else? If something
else, what are the units?

With -0.70 (and "*100" in your formula below), I thought these are
percentages. But applying those numbers as percentages (interpreted
as daily, quarterly or annual percentages) to an initial investment of
$10,000 does not even come close to an ending value of $12,011 either
way.

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

Huh!? Values and dates of what? The range covers 8 rows, presumably
in the first quarter. What are those 8 rows: the value of your
investment on 8 separate dates within the quarterd? (That would be
your first mistake <wink>.) The value of your investment at the
beginning and end of the quarter (properly signed!), with 6 blank rows
in between(!)?

In any case, dividing by 365 would seem to yield a __daily__ rate, not
a quarterly rate. And you are only confusing the matter by
multiplying by 100 instead of simply formatting the result (without
"*100") as Percentage.

No matter, that is overkill or simply wrong. The quarterly rate can
be computed simply by:

=(endBalance / startBalance) - 1

where startBalance is the ending balance of the previous quarter (or
your initial investment), and endBalance is the ending balance of the
current quarter.

I would not try to normalize such rates to account for the difference
in the number of days in each quarter; that is, the fact that in 2007,
for example, the respective quarters had 90, 91, 92 and 92 days each
instead of 91.25 (365/4). But if that is what you want to do, I still
would not use XIRR for that purpose. Instead, you might use either of
the following equivalent formulas:

=(endBalance / startBalance) ^ ( 365 / 4 / (endDate - startDate) ) - 1

=fv(rate(endDate - startDate, 0, -startBalance, endBalance), 365/4, 0,
-1) - 1

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

Yes, if you compute the quarterly rate for your investment in the
manner that I describe above, you could compare with the quarterly
benchmark returns or use this methodology to compute the annual
return.

(Of course, the latter can be computed more easily with the same
(endBalance/startBalance)-1 formula.)

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.

Well, it is true that XIRR returns annual rates, not quarterly rates.
You could convert the XIRR rate to a quarterly rate, if you do it
correctly. But first you have to adapt your data correctly in order
to use XIRR. But as I said, that is overkill. So I will not even
explain how.

HTH.

 

[joeu2004]

Errata....

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

[....]
In any case, dividing by 365 would seem to yield a __daily__ rate, not
a quarterly rate.

Oops! If A7 is the ending date of the previous quarter and A14 is the
ending date of the current quarter, "^(A14-A7)/365" should convert the
XIRR rate to a (nearly) quarterly rate. I would use "^(365/4)"
instead in order to normalize quarters.

Note: Normalize your quarterly rates does not get us any closer to
12011. So there is something fundamentally wrong with the way that
you are using XIRR in this application. No matter. As I said, using
XIRR here is overkill. Fergetaboutit!

 

[joeu2004]

Errata*2....

[....]
I would use "^(365/4)" instead in order to normalize quarters.

Brain fart! Of course that should be simply "^(1/4)".

sub meaCulpa()
for i = 1 to 10000
debug.print "I will not post when I am late for an appointment."
next
end sub

 

[Carl LaFong]

Joe: See my answers to your points below

I have separately calculated the individual quarterly XIRRs as:
Q1: -.70
Q2: 5.41
Q3: 3.01
Q4: 8.34

What are those figures: percentages or something else? If something
else, what are the units?

Those are percentages. A loss of .70 in q1, a gain of 5.41 in q2, etc.

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

Huh!? Values and dates of what?

The dates are the dates of each interim addition to the account during that
particular quarter. The values are the account values on those dates, as
reported by my broker. For instance, on Feb 24, I might have added $400 to
the account and the broker reports an account value on that date of
$11.099.87. (those are just examples, not the actual figures)



No matter, that is overkill or simply wrong. The quarterly rate can
be computed simply by:

=(endBalance / startBalance) - 1

where startBalance is the ending balance of the previous quarter (or
your initial investment), and endBalance is the ending balance of the
current quarter.


Here are some real numbers:

12/31/06 beginning balance: 7930.78

3/31/07 balance 11820.65

6/30/07 balance 15993.99

9/30/07 balance 19208.92

12/31/07 ending balance 39158.28

using your method of quarterly rate = (endBalance / startBalance) - 1

q1: 49.05%

q2: 35.31%

q3: 20.10%

q4: 103.85%

What this method ignores is that there were several additions to the account
each quarter on the specified dates. That is: 10000 x 1.4915 x 1.3531 x
1.2010 x 203.85 = 49409. This is clearly way high. We know from XIRR that
the return for the account for the year was 20.11%. That is, the year end
value of a hypothetical 10000 beginning investment must be 12011, not 49409.
The question is, what are the quarterly returns? I guess you would refer to
these as "average quarterly returns"?

I need a method that is comparable to what Vanguard has done as per the
example I mentioned for the Wellington fund, namely:


Wellington 2007 total return: 8.34
hypothetical 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

My account XIRR for 2007: 20.11

hypothetical 10000 x 1.0211 = 12011 final value

2007 individual average quarterly returns: ?, ?, ?, ?

10000 x ? x ? x ? x ? = 12011 final value

I know from the RATE or POWER functions that quarterly returns of 4.69 will
result in a final value of 12011, but in the real world my rates of return
vary from quarter to quarter and are not a constant 4.69.

For all I know, I don't need XIRR to compute the quarterly rate. However, it
is a given that in my case the XIRR for the entire year is 20.11%.

What I DO need is a method that mirrors the Vanguard example such that a
hypothetical 10k beginning balance x q1 x q2 x q3 x q4 gives the same result
as a hypothetical 10k beginning balance x XIRR annual rate (20.11 in this
particular case). The quarterly and annual calculations must yield the same
final value of 12011, just as both methods yielded 10834 in the Vanguard
example.

Again, how do I calculate average quarterly returns that can replace the
question marks in the equation below?

10000 x ? x ? x ? x ? = 12011 final value

Thanks for any further help.

 

[Carl LaFong]

Let me make a minor correction to my own post:



Joe said:

Huh!? Values and dates of what?

I said:

The dates are the dates of each interim addition to the account during
that particular quarter. The values are the account values on those dates,
as reported by my broker. For instance, on Feb 24, I might have added $400
to the account and the broker reports an account value on that date of
$11.099.87. (those are just examples, not the actual figures)

The dates are the ending date of the prior quarter quarter, the dates of the
interim additions of new money, and the last date of the current quarter.

The values are the ending balance for the prior quarter as reported by my
broker, the amounts of the interim additions, and the closing balance for
the current quarter as reported by my broker. Column A has dates. Column B
has dollar amounts. Each quarter has several additions to the account.

 

[Ron Rosenfeld]

The dates are the dates of each interim addition to the account during that
particular quarter. The values are the account values on those dates, as
reported by my broker. For instance, on Feb 24, I might have added $400 to
the account and the broker reports an account value on that date of
$11.099.87. (those are just examples, not the actual figures)

This may be the problem.

For XIRR, the dates should be as you write -- the dates of each interim
addition to the account.

BUT, the Values should be the AMOUNT of the ADDITIONS. Not the account value
on that date.

Your table should look something like:

Column
A B

Dates Value

Start Value of Account
Add'n1 Amount of first addition
Add'n2 Amount of second addition
Add'n3 Amount of third addition
Withdr1 Amount of 1st withdrawal as a negative number
.... ...
End Value of Account at end of period AS A NEGATIVE NUMBER


--ron

 

[joeu2004]

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

 

[joeu2004]

What I DO need is a method that mirrors the Vanguard example such that a
hypothetical 10k beginning balance x q1 x q2 x q3 x q4 gives the same result
as a hypothetical 10k beginning balance x XIRR annual rate (20.11 in this
particular case).

10K(!)? Well, you do say "hypothetical" 10K. If this is for a class
or for your personal edification, fine.

But if this for a real 10K, you should not rely on this newsgroup
(some might say any newsgroup) for professional information that can
have major financial or legal consequences. You have no idea of the
qualifications (or not) of the people who are posting responses, no
matter what they claim.

For a real 10K, you should consult a CPA. If you are that CPA, you
should be able to get dispositive advice from either the SEC or the
FASB.

(You might try posting to misc.taxes.moderated. Many participants
claim to be CPAs. But bear in mind that many other participants are
not; and again, anyone can claim to be anything on the Internet.)

PS: misc.taxes.moderated participants often deprecate comments
specific to an application like Excel. I would avoid reference to
XIRR per se, and simply form the question in terms of "the IRR, which
takes actual dates of transactions into account".

 

[Carl LaFong]

What I DO need is a method that mirrors the Vanguard example such that a
hypothetical 10k beginning balance x q1 x q2 x q3 x q4 gives the same
result
as a hypothetical 10k beginning balance x XIRR annual rate (20.11 in this
particular case).

10K(!)? Well, you do say "hypothetical" 10K. If this is for a class
or for your personal edification, fine.

Joe:

I was referring to a hypothetical 10,000 dollar investment, not to a 10k
filing with the Securities and Exchange Commission. Sorry for the confusing
shorthand.

I am digesting your earlier lengthy response, which is appreciated. I have
replicated in Excel and agree with nearly all of it and will post my
comments shortly.

 

[joeu2004]

PS....

The [annual] 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.
[....]
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.

Looking at the two problems algebraically, I think it is "obvious"
that that is exactly the explanation, at least analogously.

The quarterly rates of return are the solutions to the following
equations:

0 = SUM(CF[k]/(1+i1)^k, k=0,...,2)
0 = SUM(CF[k+3]/(1+i2)^k, k=0,...,2)
0 = SUM(CF[k+6]/(1+i3)^k, k=0,...,2)
0 = SUM(CF[k+9]/(1+i4)^k, k=0,...,2)

The key thing to notice is that the exponent of (1+i[q])^k is never
more than 2.

But the annual rate is the solution to the following equation:

0 = SUM(CF[k]/(1+i)^k, k=0,...,11)

Thus, for the example, for cash flows in the 2nd quarter, the exponent
of 1/(1+i)^k is 3 through 5, not 0 through 2, significantly reducing
the influence of those cash flows on the total solution. So off-hand,
I see no reason to expect that (1+i1)*...*(1+i4) would equal 1+i.

Yet we know that the quarterly formulas correctly find the quarterly
rates of return. Recall that they match the rates that I used to
construct the hypothetical example.

So I think it is fair to say that we should, in fact, not expect that
there is some simple function of the quarterly rates -- much less
their product -- that would equal the annual rate, because the two
sets of rates are based on very different assumptions. But there is
nothing wrong with either the quarterly or annual rates computed using
XIRR in the manner that I prescribed.

Similarly, there is no simple function of subgroup averages -- like
(a1+...+a4)/4 -- that equals the average of the whole, in the case
when the subgroups differ in size. We have to compute (n1*a1+...
+n4*a4) / (n1+...+n4). That does not inhibit from reporting the
individual subgroup averages along with the average of the whole.

That is hardly a rigorous proof. But I hope it is a convincing
argument.

Then again, I could be completely wrong <wink>.


PS: I have a little off-by-one error in my previous XIRR
constructions. But I think you can get the idea.

 

[Carl LaFong]

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?

 

[joeu2004]

I was referring to a hypothetical 10,000 dollar investment, not to a 10k
filing with the Securities and Exchange Commission. Sorry for the confusing
shorthand.

Of course you were. Silly me! No apology needed -- except mine. Mea
culpa!

This has been a fun exploration. I learned a lot from it, having
taken a lot of things for granted for many years. It will be
interesting to see what holes people poke into my thoughts here.

Errata....

Annual XIRR construction:

12/31/2006 -10000 (initial investment; previous year-end value)
1/1/2007 -100
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
12/31/2007 13,055.39 (year-end value)
17.4488% (annual XIRR)

Quarterly XIRR construction:

12/31/2006 -10,000.00 (initial investment; previous year-end value)
1/1/2007 -100.00
2/1/2007 -100.00
3/1/2007 -100.00
3/31/2007 10,228.62 (quarter-end value)
-0.7000% (quarterly XIRR)
2.2862% (endBalance/startBalance - 1)

3/31/2007 -10,228.62 (previous quarter-end value)
4/1/2007 -100.00
5/1/2007 -100.00
6/1/2007 -100.00
6/30/2007 11,101.95 (quarter-end value)
5.5000% (quarterly XIRR)
8.5381% (endBalance/startBalance - 1)

6/30/2007 -11,101.95
7/1/2007 -100.00
8/1/2007 -100.00
9/1/2007 -100.00
9/30/2007 11,740.86
3.0000%
5.7549%

9/30/2007 -11,740.86
10/1/2007 -100.00
11/1/2007 -100.00
12/1/2007 -100.00
12/31/2007 13,055.39
8.5000%
11.1963%

Product of quarterly XIRRs: 17.0762%

Product of quarterly endBalance/startBalance - 1: 30.5539%
Annual endBalance/startBalance - 1: 30.5539%

 

[joeu2004]

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