Reply to thread

Message: [QUOTE="Carl LaFong, post: 3742141"] 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. [/QUOTE]

Verification