Calculating quarterly investment returns---XIRR or another function??
- From: "Carl LaFong" <frogare@xxxxxxx>
- Date: Mon, 7 Jan 2008 04:56:44 -0700
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.
.
- Follow-Ups:
- Prev by Date: Re: Conditional statements
- Next by Date: Re: Formula
- Previous by thread: Re: Conditional statements
- Next by thread: Re: Calculating quarterly investment returns---XIRR or another function??
- Index(es):
Relevant Pages
|
