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.