Reply to thread

Message: [QUOTE="joeu2004, post: 3742234"] 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) ) [/QUOTE]

Verification