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

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.

On Jan 7, 3:56*am, "Carl LaFong" wrote:
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.

Errata....

On Jan 7, 8:38*am, I wrote:
On Jan 7, 3:56*am, "Carl LaFong" wrote:
=((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!

Errata*2....

On Jan 7, 8:54*am, I wrote:
On Jan 7, 3:56*am, "Carl LaFong" wrote:
=((1+XIRR(B7:B14,A7:A14,0))^((A14-A7)/365)-1)*100
[....]
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

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.

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.

On Mon, 7 Jan 2008 14:40:47 -0700, "Carl LaFong" wrote:

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

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.

On Jan 7, 1:40*pm, "Carl LaFong" wrote:
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".

"joeu2004" wrote in message
...
On Jan 7, 1:40 pm, "Carl LaFong" wrote:
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.