XIRR - Non-reinvested dividends

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
JustinR
Posts: 1451
Joined: Tue Apr 27, 2010 11:43 pm

XIRR - Non-reinvested dividends

Post by JustinR »

Are non-reinvested dividends considered withdrawals?
User avatar
White Coat Investor
Posts: 17413
Joined: Fri Mar 02, 2007 8:11 pm
Location: Greatest Snow On Earth

Re: XIRR - Non-reinvested dividends

Post by White Coat Investor »

Yes. I have a tutorial on XIRR demonstrating it, but basically, it's simply a withdrawal.
1) Invest you must 2) Time is your friend 3) Impulse is your enemy | 4) Basic arithmetic works 5) Stick to simplicity 6) Stay the course
pshonore
Posts: 8212
Joined: Sun Jun 28, 2009 2:21 pm

Re: XIRR - Non-reinvested dividends

Post by pshonore »

Depends on circumstances. If you're working with a single stock or fund, then yes, it's a withdrawal.

If you're working with a total portfolio balance and the dividends go to a Money market fund, etc which is included in the total portfolio balance, then no it is not a withdrawal.
stan1
Posts: 14246
Joined: Mon Oct 08, 2007 4:35 pm

Re: XIRR - Non-reinvested dividends

Post by stan1 »

pshonore wrote: Mon Aug 14, 2017 7:37 am If you're working with a total portfolio balance and the dividends go to a Money market fund, etc which is included in the total portfolio balance, then no it is not a withdrawal.
This is what I do. I calculate XIRR on my total portfolio not individual holdings or accounts. I have dividends in my taxable account pay into a money market fund and then I reinvest once or twice a year as needed to maintain my asset allocation. I consider the money market fund to be part of my portfolio so there is no withdrawal from my portfolio. I would consider dividends transferred to my checking account as a withdrawal if I did that.
Warning: I am about 80% satisficer (accepting of good enough) and 20% maximizer
User avatar
goingup
Posts: 4910
Joined: Tue Jan 26, 2010 12:02 pm

Re: XIRR - Non-reinvested dividends

Post by goingup »

Yes, it is a withdrawal from the fund.

Sometimes someone will post that their fund's return does not match Vanguard's return number. The immediate question to the poster is "what did you do with dividends?". If the answer is "I spent them!" then the mystery is solved. That's a fund withdrawal.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

pshonore wrote: Mon Aug 14, 2017 7:37 am Depends on circumstances. If you're working with a single stock or fund, then yes, it's a withdrawal. If you're working with a total portfolio balance and the dividends go to a Money market fund, etc which is included in the total portfolio balance, then no it is not a withdrawal.
To illustrate this, assume one has a brokerage account with two funds: Vanguard TSM Admiral Shares (VTSAX) and a money market mutual fund (MMMF) where dividends are deposited. Assume on 12/31/2016 VTSAX has a balance of $10,000 and the MMMF a balance of $1,000. The following shows how dividends are considered when calculating the separate returns of VTSAX (12.64%) and the MMMF (1.00%); but are not considered (i.e., they net to $0) when calculating their combined return (11.50%):

Code: Select all

      Col A     Col B   Col C      Col D      Col E       Col F
               ---- VTSAX ---  -----------  A m o u n t  ----------
Row   Date     Div/sh   Price      VTSAX       MMMF      Total Acct
---  --------  ------   -----  -----------  ----------  -----------
  5  12/31/16           50.79  (10,000.00)  (1,000.00)  (11,000.00)
  6  03/15/17   0.234               46.07      (46.07)        -   
  7  06/14/17   0.228               44.89      (44.89)        -   
  8  09/13/17   0.262               51.58      (51.58)        -   
  9  12/20/17   0.354               69.70      (69.70)        -   
 10  12/31/17		56.08   11,041.54    1,223.03    12,264.58
                                ---------    --------    ---------
 11  XIRR                          12.64%       1.00%       11.50%
Notes:
  • Prices are for 12/31/2015 and 12/31/2016 from the VTSAX Price History and the dividends per share are for 2016 from the VTSAX Distributions page. However, I'm showing the dates one year later to avoid a quirk in the XIRR function. It calculates the return per 365 days. Therefore it slightly understates the annual return for a leap year.
  • The VTSAX dividends ($46.07 ... $69.70) and ending balance ($11,041.54) are calculated for 196.88915 shares which is backed into by dividing $10,000 by the opening price per share ($50.79).
  • The return for VTSAX in cell D11 is calculated as follows:
    D11: 12.64% = XIRR(D5:D10, $A5:$A10) -- and then copied right to cells E11 and F11.
    This is close to the 12.66% return shown for 2016 on the VTSAX Historical returns page.
  • I forced the MMMF ending balance so that XIRR would return exactly 1%.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

goingup wrote: Mon Aug 14, 2017 8:38 am Yes, it is a withdrawal from the fund.

Sometimes someone will post that their fund's return does not match Vanguard's return number. The immediate question to the poster is "what did you do with dividends?". If the answer is "I spent them!" then the mystery is solved. That's a fund withdrawal.
The XIRR function should give you the same Internal Rate of Return (IRR) in either case. In mutual fund parlance IRR is often called "Total Return" assuming reinvestment of dividends. The ending vale is different but the IRR (Total Return %) is the same.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

Doc wrote: Mon Aug 14, 2017 10:22 am
goingup wrote: Mon Aug 14, 2017 8:38 am Yes, it is a withdrawal from the fund.

Sometimes someone will post that their fund's return does not match Vanguard's return number. The immediate question to the poster is "what did you do with dividends?". If the answer is "I spent them!" then the mystery is solved. That's a fund withdrawal.
The XIRR function should give you the same Internal Rate of Return (IRR) in either case. In mutual fund parlance IRR is often called "Total Return" assuming reinvestment of dividends. The ending vale is different but the IRR (Total Return %) is the same.
In my experience, the XIRR of a fund account with dividends taken in cash is not the same as the Total Return reported by the fund.

With a Vanguard Total Stock Market Admiral Shares (VTSAX) account that I have, the XIRR for 2016 with the distributions taken in cash being cash flows from the account, the XIRR is 12.60%. Vanguard reports the total return for the fund for 2016 as 12.66%. The two returns are not the same.

The two returns are not very different, in part, because the distribution amounts were a small percentage of the value of the account.

In general, the XIRR value is not the same as the total return. Even when there are no intermediate cash flows, the XIRR for a year will not be the same as the total return for a year when the year is a leap year. XIRR treats all years as having 365 days.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

FactualFran wrote: Mon Aug 14, 2017 1:48 pm In my experience, the XIRR of a fund account with dividends taken in cash is not the same as the Total Return reported by the fund.
Darn I was hoping #cruncher would chime in. Now I have to figure this out myself. Will report back.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

Doc wrote: Mon Aug 14, 2017 1:53 pm Now I have to figure this out myself. Will report back.
Image

This is not the same thing that #cruncher was doing. He was investing dividends in another fund not spending them.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

FactualFran wrote: Mon Aug 14, 2017 1:48 pmIn my experience, the XIRR of a fund account with dividends taken in cash is not the same as the Total Return reported by the fund.
Yes; while the two might be the same; in general they will differ since fund total returns assume dividends are reinvested. To illustrate, consider the following hypothetical case:
  • Have 1,000 shares of a fund priced at $10.00 per share for a total value of $10,000.
  • One year later the fund pays a $1,000 dividend which is not reinvested.
  • One year after that the price is $11.00 per share for a total value of $11,000.
XIRR (or IRR since the dates are exactly one year apart) show the return to be 10.00%:

Code: Select all

12/31/2016    (10,000)
12/31/2017      1,000
12/31/2018     11,000
XIRR or IRR    10.00%
Now assume the dividend is reinvested. The total return will depend on the price at which the reinvestment occurs. Three possibilities are shown below. In the first, the price is $10.00 / share and the total return is 10%, the same as when dividends were not reinvested. But in the second case the price is $11.00 / share and the total return is less than 10%. And in the third case the price is $9.00 / share and the total return is more than 10%.

Code: Select all

                      $1,000 Div                 XIRR
          12/31/2016  12/31/2017  12/31/2018    or IRR
Price         10.000      10.000      11.000
Shares     1,000.000   1,100.000   1,100.000
Value    (10,000.00)       0.00   12,100.00     10.00%

Price         10.000      11.000      11.000
Shares     1,000.000   1,090.909   1,090.909
Value    (10,000.00)       0.00   12,000.00      9.54%

Price         10.000       9.000      11.000
Shares     1,000.000   1,111.111   1,111.111
Value    (10,000.00)       0.00   12,222.22     10.55%
Topic Author
JustinR
Posts: 1451
Joined: Tue Apr 27, 2010 11:43 pm

Re: XIRR - Non-reinvested dividends

Post by JustinR »

goingup wrote: Mon Aug 14, 2017 8:38 am Yes, it is a withdrawal from the fund.

Sometimes someone will post that their fund's return does not match Vanguard's return number. The immediate question to the poster is "what did you do with dividends?". If the answer is "I spent them!" then the mystery is solved. That's a fund withdrawal.
I did an XIRR spreadsheet and Vanguard is off by 0.56%. Total Return from inception to the last "Total Return" date (7/31/17). I'm not sure who's right.

And I'm considering "INCOME DIVIDEND ACH" lines as withdrawals.

Edit: I WAS WRONG. For one year, I forgot to make all the dividend withdrawals negative. Vanguard was 100% correct. Trust Vanguard's Total Return number, it matches exactly.
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: XIRR - Non-reinvested dividends

Post by longinvest »

This wiki page and spreadsheet might be of interest to some readers: wiki: Calculating personal returns.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

There is some confusion about the difference in "Internal Rate of Return" and the "Growth of $10k" charts we are all familiar with.

From Microsoft Office Support the XIRR function
Description
Returns the "internal rate of return" for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.
https://support.office.com/en-us/articl ... n-US&ad=US

Note that the difference of XIRR and IRR is only that the former allows for cash flows that are not necessarily periodic.

The question in the OP concerns the effect of reinvested dividends on the XIRR. Reinvestment of dividends does effect the final value but has no effect on the Internal Rate of Return itself.

Some examples have purported to show that reinvestment of dividends at different prices makes a difference in the final IRR. In demonstrating this idea different values are put into the XIRR function to show the effect of different dividend amounts. But there is no difference because there is no cash flow from reinvested dividends. All the money you get from the dividend no matter how much it is, is put back into the investment. The net cash flow for these dividend is zero and those terms should be zero no matter the price of the shares that are reinvested.

Part of the misunderstanding here is because different types of financial investments use different terms in discussing the IRR. All of these terms are based on the same discounted cash flow equations. "Only the names have been changed to protect confuse the innocent." (Dragnet TV series 1951-59) Here is a start for understanding the differences for those of you that are interested.
Financial ratios and multiples – including metrics like debt-to-equity ratio, price-earnings ratio and return on equity – provide a quick way for investors to determine the general value of a stock compared to other investments in the market. If you want to estimate the absolute value of a company, however, discounted cash flow (DCF) analysis can come in handy. It takes into account the time value of money – the idea that the money that’s available today is worth more than the same amount in the future because of its potential earning capacity.
Read more: Discounted Cash Flow Analysis http://www.investopedia.com/university/ ... z4prbjUS25
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
Topic Author
JustinR
Posts: 1451
Joined: Tue Apr 27, 2010 11:43 pm

Re: XIRR - Non-reinvested dividends

Post by JustinR »

longinvest wrote: Tue Aug 15, 2017 8:18 am This wiki page and spreadsheet might be of interest to some readers: wiki: Calculating personal returns.
What's the benefit of your spreadsheet over XIRR?
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

Doc wrote: Tue Aug 15, 2017 5:06 pmThe question in the OP concerns the effect of reinvested dividends on the XIRR. Reinvestment of dividends does effect the final value but has no effect on the Internal Rate of Return itself.
Really? Doc, please enter the following into cells A1:D3 of a blank spreadsheet:

Code: Select all

Row    Col A     Col B     Col C     Col D
---   --------  --------  --------  --------
  1   12/31/16  (10,000)  (10,000)  (10,000)
  2   12/31/17        0         0         0
  3   12/31/18   12,100    12,000    12,222
Now, please enter the following two formulas into cells B4 & B5 and copy them right to column D.

Code: Select all

B4: =XIRR(B1:B3, $A1:$A3)
B5: =IRR(B1:B3)
Are not the values in cells B4, C4, and D4 10.00%, 9.54%, and 10.55%? Are not the values in cells B5, C5, and D5 also 10.00%, 9.54%, and 10.55%? And lastly do not these numbers correspond to the three cases in my previous post where the $1,000 dividend was reinvested at $10 / share, $11 / share, and $9 / share?
Doc in same post wrote:Some examples have purported to show that reinvestment of dividends at different prices makes a difference in the final IRR. In demonstrating this idea different values are put into the XIRR function to show the effect of different dividend amounts.
Doc, please note that in the three cases from my previous post the dividend amount is the same. Only the reinvestment prices differ. This causes different number of shares to be purchased in each case; which, one year later, makes the ending values differ. Finally, these different ending values are what makes the Internal Rates of Return (IRR) differ.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

#Cruncher wrote: Tue Aug 15, 2017 10:39 pm Doc, please note that in the three cases from my previous post the dividend amount is the same. Only the reinvestment prices differ. This causes different number of shares to be purchased in each case; which, one year later, makes the ending values differ. Finally, these different ending values are what makes the Internal Rates of Return (IRR) differ.
I think this is where the problem comes from. The discounted flow equations IRR or XIRR have an inherent assumption or even definition that here is only one rate involved. The effect of changing the prices is to change the rate of return in different time periods. If this is allowed by the security in question you can get differences in the rate of return between reinvesting dividends or taking them in cash. Which comes out ahead depends on when the rate is increasing or decreasing. This does not happen with a bond but certainly happens with equity funds and can occur with bond funds albeit to a lesser extent.

This could explain differences between results that come from a $10k growth chart and the result found using the XIRR function for the same data. The fund company is adding the actual dollars in dividends to the then current total value of the fund balance at that time to get a new balance. At the end of the period the "Total Annualized Return" is (Ending$/Beginning$)^(1/n) -1 . No XIRR function required.
JustinR wrote: Tue Aug 15, 2017 7:28 am Trust Vanguard's Total Return number, it matches exactly.
I compared the ten year Total Annual Return percent for Vg TBM VBTLX for the ten year period ending 6/30/2017 using VG's data and M* growth chart and using the compound interest formula from above and both gave 4.45%.

Hopefully this answers the question about XIRR and non-reinvested dividends: "Total Annualized Return" from a fund is not the same as the "Internal Rate of Return" calculated by using the XIRR function.

I don't have anymore time to work on this. Cheers.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

Doc in previous post wrote:The discounted flow equations IRR or XIRR have an inherent assumption or even definition that [t]here is only one rate involved.
Of course; how could it be otherwise? The Internal Rate of Return (IRR) is the single discount rate that, when applied to all the cash flows, produces a series that sums to zero.

Doc in same post wrote:If [changing price] is allowed by the security in question you can get differences in the rate of return between reinvesting dividends or taking them in cash.
So, does this mean you are accepting my argument, Doc?

Doc in same post wrote:This does not happen with a bond ...
Yes, it does -- if one ignores the granularity of bond purchases by allowing purchases of any amount of a bond, not just in $1,000 increments. Consider a two-year bond that pays 10% interest at the end of each year. If one buys $10,000 at par when issued, one year later one will receive $1,000 in interest. One year after that, one will receive another $1,000 in interest plus $10,000 in principal on the original purchase. If one does not reinvest the first year's interest, the Internal Rate of Return (IRR) will be 10%, as one would expect.

However, if one does reinvest the $1,000 first year interest in additional principal of the same bond, the IRR will depend on what the price of the bond is when the first year interest is reinvested -- just like with a mutual fund. The price will vary inversely with the yield. Here are three cases where the bond's yield is 10%, 5%, or 15% after one year. This means that the reinvestment of the first year interest will be at par, at a premium, or at a discount. As shown below the IRR for the three cases will be 10.00%, 9.77%, or 10.23%.

Code: Select all

Yield one year before maturity                   10%            5%           15%
Price one year before maturity              100.000%      104.762%       95.652%
Principal purchased with year 1 interest    1,000.00        954.55      1,045.45 
Principal plus interest at redemption      12,100.00     12,050.00     12,150.00
Cash flow
  Year 0                                  (10,000.00)   (10,000.00)   (10,000.00)
  Year 1                                        0.00          0.00          0.00
  Year 2                                   12,100.00     12,050.00     12,150.00 
IRR                                           10.00%         9.77%        10.23%
Here are the formulas for the 5% and 15% cases:

Code: Select all

104.762% = 1.1 / 1.05              95.652% = 1.1 / 1.15
  954.55 = 1000 / 104.762%        1,045.45 = 1000 / 95.652%
  12,050 = 10954.55 * 1.1           12,150 = 11045.45 * 1.1
   9.77% = IRR({-10000, 0, 12050})  10.23% = IRR({-10000, 0, 12150})
Doc in same post wrote:At the end of the period the "Total Annualized Return" is (Ending$/Beginning$)^(1/n) -1 . No XIRR function required.
Yes, using the XIRR function (or IRR function when there's a whole number of periods) isn't necessary in this case -- but they still work and give the same result.

Doc in same post wrote:Hopefully this answers the question about XIRR and non-reinvested dividends: "Total Annualized Return" from a fund is not the same as the "Internal Rate of Return" calculated by using the XIRR function.
Whoa! This is just wrong. "Total Annualized Return" is the "Internal Rate of Return". And further their value depends only on the cash flows, not on the formula or functions used to compute them.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

#Cruncher wrote: Wed Aug 16, 2017 3:06 pm Whoa! This is just wrong. "Total Annualized Return" is the "Internal Rate of Return". And further their value depends only on the cash flows, not on the formula or functions used to compute them.
We've now come full circle. That's what I thought in the past. Your examples of different reinvestment prices convinced me that I was wrong. And I had to put in the the idea that there was a single IIR but you could have multiple reinvestment rates in the "TAR" and that's how you get to reinvestment of dividends matter.

I don't think of "Total Annualized Return" because I don't reinvest dividends as a general principle. I am usually thinking of bonds. And I can "reinvest" the interest payment at the original rate by buying a principal only zero.

We apparently don't agree about something but I'm not sure what it is.

Question: When you are reinvesting your dividends at different NAV are you calculating the shares purchased, adding those shares to the share total and then calculating the ending balance using the total shares purchased including the reinvestment and the ending price? This isn't clear from your presentation.

As an aside: What the heck is "IRR({-10000, 0, 12050})" ? You must be a lot younger than me. I earned my discounted cash flow bones using a slide rule not some kind of computer thingy. Now I use the little calculator that comes with Windows because I gave my slide rule to my oldest grandson as a "what do you think this is" puzzle. :D
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
User avatar
Oicuryy
Posts: 1959
Joined: Thu Feb 22, 2007 9:29 pm

Re: XIRR - Non-reinvested dividends

Post by Oicuryy »

#Cruncher wrote: Wed Aug 16, 2017 3:06 pmWhoa! This is just wrong. "Total Annualized Return" is the "Internal Rate of Return". And further their value depends only on the cash flows, not on the formula or functions used to compute them.
The time-weighted rate of return and the dollar-weighted rate of return can be different. Mutual funds typically report the time-weighted return. But they are required by the SEC to call it the "average annual total return". IRR and XIRR calculate the dollar-weighted return. See these pages from Gummy.

http://www.financialwisdomforum.org/gum ... nd-you.htm
http://www.financialwisdomforum.org/gum ... eturns.htm

Ron
Money is fungible | Abbreviations and Acronyms
alex_686
Posts: 13320
Joined: Mon Feb 09, 2015 1:39 pm

Re: XIRR - Non-reinvested dividends

Post by alex_686 »

Doc wrote: Wed Aug 16, 2017 4:59 pm
#Cruncher wrote: Wed Aug 16, 2017 3:06 pm Whoa! This is just wrong. "Total Annualized Return" is the "Internal Rate of Return". And further their value depends only on the cash flows, not on the formula or functions used to compute them.
We've now come full circle. That's what I thought in the past. Your examples of different reinvestment prices convinced me that I was wrong. And I had to put in the the idea that there was a single IIR but you could have multiple reinvestment rates in the "TAR" and that's how you get to reinvestment of dividends matter.
Technically speaking "Total Annualized Return" is not the "Internal Rate of Return". Time weighted or chained returns is what mutual funds report on. However this is a bit academic. IRR (and by extension XIRR) should deliver equivalent return. If they don't you have issues. Trivia of the day - did you know that IRR is not mathematically "closed". You can generate multiple valid IRR for the same sets of cash flows.
Former brokerage operations & mutual fund accountant. I hate risk, which is why I study and embrace it.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

Doc wrote: Wed Aug 16, 2017 4:59 pmQuestion: When you are reinvesting your dividends at different NAV are you calculating the shares purchased, adding those shares to the share total and then calculating the ending balance using the total shares purchased including the reinvestment and the ending price?
That's exactly right, Doc. To illustrate, here's how I get the 12.66% 2016 return reported by Vanguard for TSM (VTSAX). This assumes a $10,000 starting balance, the dividends reported here, and the starting and ending prices reported here.

Code: Select all

      Col A     Col B    Col C  Col H    Col I   Col J      Col K
     Reinvest   Div /   Price /  Div     --- Shares ---
Row    Date     share    share  Amount   Purch   Total     $ Value
---  --------   -----    -----  ------   -----  -------   ---------
  5  01/01/16            50.79                  196.889  -10,000.00
  6  03/15/16   0.234    49.94   46.07   0.923  197.812 	
  7  06/14/16   0.228    51.67   45.10   0.873  198.685 	
  8  09/13/16   0.262    53.86   52.06   0.966  199.651 	
  9  12/20/16   0.354    56.63   70.68   1.248  200.899 	
 10  12/31/16            56.08                  200.899   11,266.42
Notes:
  • The 12.66% return can be calculated two different ways:
    12.66% = 11266.42 / 10000 - 1
    12.66% = XIRR(K5:K10, A5:A10)
  • $11,266.42 is also what $10,000 grows to on Morningstar for a date range 1/1/2016 - 12/31/2016.
  • I set the starting date to 1/1/2016 instead of 12/31/2015 since 2016 is a leap year and XIRR works better with 365 day "years".
Doc in same post wrote:As an aside: What the heck is "IRR({-10000, 0, 12050})"?
The braces denote an Excel array constant. Excel arrays are something I don't use and barely understand. I put it in my post just to indicate the parameters to the IRR function. If the three numbers were stored in cells A1, A2, & A3, I'd actually write the formula as "=IRR(A1:A3)".
Doc in same post wrote:You must be a lot younger than me. I earned my discounted cash flow bones using a slide rule ...
Not much younger, Doc. In my first job after college I started out using one of those monster Friden mechanical calculators. I was one of the first in the office to get an electronic desktop calculator. It had neon tubes to display the digits and cost as much as a desktop computer does today.


Oicuryy wrote: Wed Aug 16, 2017 6:19 pmMutual funds typically report the time-weighted return. ... IRR and XIRR calculate the dollar-weighted return.
Mutual fund returns are based on "cash flows" that consist of only two numbers: a starting value and an ending value. In this case the time-weighted return and the dollar-weighted return are the same and the XIRR function can be used to compute it. See the first note to the table above for an illustration.


alex_686 in previous post wrote:Technically speaking "Total Annualized Return" is not the "Internal Rate of Return".
I used the term "Total Annualized Return" to refer to the return reported by mutual funds because that is the term Doc used. Vanguard just calls it "Returns before taxes" on this page where it reports an 18.47% return for VTSAX for the year ending 6/30/2017. Morningstar shows $10,000 growing to $11,846.84 from 7/1/2016 to 6/30/2017, the same 18.47% return. All I meant was that the return reported by mutual funds (whatever you want to call it) is the Internal Rate of Return. As such it can be calculated with the XIRR function. For example:

Code: Select all

Row    Col A       Col B
---  --------   ----------
  1  06/30/16   -10,000.00 
  2  06/30/17    11,846.84
18.47% = XIRR(B1:B2, A1:A2)
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

Total Return

Expressed in percentage terms, Morningstar's calculation of total return is determined by taking the change in price, reinvesting, if applicable, all income and capital gains distributions during the period, and dividing by the starting price
.
http://www.morningstar.com/InvGlossary/ ... eturn.aspx

M*'s number is identcle with Vanguard's. See prior for ten year TBM fund.

This # is not IRR because the total return takes account of varying return while IRR assume a single "average" rate. In the single rate (IRR) case reinvestment of dividends or not doesn't make any difference as I showed previously. In the variable return (Total Return) case reinvestment of dividends or not can make a difference as #cruncher showed previously.

And yes I was aware of the multiple solutions to the XIRR function. That's why it has "guess" as a parameter.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
User avatar
Oicuryy
Posts: 1959
Joined: Thu Feb 22, 2007 9:29 pm

Re: XIRR - Non-reinvested dividends

Post by Oicuryy »

(To see the math expressions below in math notation you can install the bookmarklet presented here in your browser and then run it on this page.)

Let `f_0,f_1,f_2,...f_n` be a series of cash flows for an investment. Think of `f_0` as the beginning balance and `f_n` as the ending balance.

Let `r_1,r_2,r_3,...r_n` be the percent gains of the investment between cash flows. So, for example, `r_1` is the percent gain between `f_0` and `f_1`.

The time-weighted rate of return is the value of `i` that solves this equation.
`(1+i)^t=prod_(j=1)^n(1+r_j)`
Where `t` is the time in years and fractions of a year from the first cash flow to the last.

The dollar-weighted rate of return is the value of `i` that solves this equation.
`f_n=sum_(j=0)^(n-1)f_j(1+i)^(t_j)`
Where `t_j` is the time in years and fractions of a year from the `j`th cash flow to the last cash flow.

Note that the time-weighted rate depends only on the percent gains. This means the time weighted rate is the same whether or not distributions are reinvested.

The dollar-weighted rate depends only on the cash flows. It will be different if distributions are or are not reinvested. XIRR calculates the dollar-weighted rate.

In the special case of only two cash flows, `f_0` and `f_n`, the same value of `i` solves both equations. That lets us argue over whether mutual funds are reporting the time-weighted rate or the dollar-weighted rate with all distributions reinvested.

Yes, I know there can be multiple solutions for `i` in the dollar-weighted equation. Polynomials can have multiple roots.

Edit #1: Changed times from days to years. XIRR treats 1 leap year as 366/365 = 1.0027 years. See FactualFran's post below.

Ron
Last edited by Oicuryy on Thu Aug 17, 2017 4:32 pm, edited 1 time in total.
Money is fungible | Abbreviations and Acronyms
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: XIRR - Non-reinvested dividends

Post by longinvest »

Oicuryy wrote: Thu Aug 17, 2017 10:28 am Note that the time-weighted rate depends only on the percent gains. This means the time weighted rate is the same whether or not distributions are reinvested.

The dollar-weighted rate depends only on the cash flows. It will be different if distributions are or are not reinvested. XIRR calculates the dollar-weighted rate.
Exactly!
Oicuryy wrote: Thu Aug 17, 2017 10:28 am... argue over whether mutual funds are reporting the time-weighted rate or the dollar-weighted rate with all distributions reinvested.
I've always held the belief that mutual funds reported time-weighted returns. Doing otherwise wouldn't make sense.

For practical purpose, the wiki: Calculating personal returns spreadsheet calculates both types of returns (time-weighted and money-weighted) for a personal portfolio spread across multiple investment accounts.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: XIRR - Non-reinvested dividends

Post by longinvest »

Let's consider two consecutive periods, P1 and P2, three prices (start, middle, and end), and two distribution amounts paid at the end of P1 and P2, respectively.

In other words:

Code: Select all

Period P1: start_price = SP, end_price = MP, and distribution = MD
Period P2: start_price = MP, end_price = EP, and distribution = ED
The single-period time-weighted returns for each of P1 and P2 are:

Code: Select all

RP1 = ((MP + MD) / SP) - 1
RP2 = ((EP + ED) / MP) - 1
The cumulative time-weighted return over both periods is thus:

Code: Select all

CumP1P2 = ((1 + RP1) X (1 + RP2)) - 1
This will be true even if P1 and P2 have different lengths.

I've always assumed that mutual funds did such a calculation to report annual fund returns, using as many periods as there were distributions during the year (+1, if the last distribution was not on the last day of the year).
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

Oicuryy wrote: Thu Aug 17, 2017 10:28 amNote that the time-weighted rate depends only on the percent gains. This means the time weighted rate is the same whether or not distributions are reinvested.
Never having calculated a time-weighted return, I was skeptical of this claim. So I applied it against the 2016 prices and dividends for Vanguard TSM Index Fund Admiral Shares (VTSAX). I used the 2nd spreadsheet referenced above by Oicuryy to see how a time-weighted return is calculated. Lo and behold, the claim is correct! Whether dividends are re-invested or not, the return is 12.6642%. :!:

Here are the results with dividends not reinvested:

Code: Select all

          Div /                 --- # Shares ---
  Date    share  Price   Div $   Purch    Total  Withdraw   Balance     Factor
--------  -----  -----  ------  ------  -------- --------  ---------   --------
01/01/16         50.79                  196.8892           10,000.00
03/15/16  0.234  49.94  46.072     -    196.8892   46.072   9,832.64   0.987872
06/14/16  0.228  51.67  44.891     -    196.8892   44.891  10,173.26   1.039207
09/13/16  0.262  53.86  51.585     -    196.8892   51.585  10,604.45   1.047455
12/20/16  0.354  56.63  69.699     -    196.8892   69.699  11,149.83   1.058002
12/31/16         56.08                  196.8892           11,041.54   0.990288
                                                                       --------
                                                        Product --->   1.126642
                                           Time weighted return --->   12.6642%
And now with dividends reinvested:

Code: Select all

          Div /                 --- # Shares ---
  Date    share  Price   Div $   Purch    Total  Withdraw   Balance     Factor
--------  -----  -----  ------  ------  -------- --------  ---------   --------
01/01/16         50.79                  196.8892           10,000.00
03/15/16  0.234  49.94  46.072  0.9225  197.8117      -     9,878.72   0.987872
06/14/16  0.228  51.67  45.101  0.8729  198.6846      -    10,266.03   1.039207
09/13/16  0.262  53.86  52.055  0.9665  199.6511      -    10,753.21   1.047455
12/20/16  0.354  56.63  70.676  1.2480  200.8991      -    11,376.92   1.058002
12/31/16         56.08                  200.8991           11,266.42   0.990288
                                                                       --------
                                                        Product --->   1.126642
                                           Time weighted return --->   12.6642%
Each of the factors on the far right is the sum of "Withdraw" and "Balance" divided by the previous "Balance". For example:
0.987872 = (46.072 + 9832.64) / 10000 = (0.00 + 9878.72) / 10000
Note that each factor is the same whether dividends are reinvested or not.

Of course one doesn't have to go through this when dividends are reinvested. One can just calculate the annual return from the starting balance to the ending balance.
12.6642% = (11266.42 / 10000) ^ (365 / 365) - 1

Although overkill in this case, one could also calculate it with the Excel XIRR function. For example if 1/1/2016 and 12/31/2016 are stored in cells A1:A2 and -10,000 and 11,266.42 are stored in cells B1:B2 then
12.6642% = XIRR(B1:B2, A1:A2)
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

#Cruncher wrote: Wed Aug 16, 2017 10:12 pm That's exactly right, Doc. To illustrate, here's how I get the 12.66% 2016 return reported by Vanguard for TSM (VTSAX). This assumes a $10,000 starting balance, the dividends reported here, and the starting and ending prices reported here.

Code: Select all

      Col A     Col B    Col C  Col H    Col I   Col J      Col K
     Reinvest   Div /   Price /  Div     --- Shares ---
Row    Date     share    share  Amount   Purch   Total     $ Value
---  --------   -----    -----  ------   -----  -------   ---------
  5  01/01/16            50.79                  196.889  -10,000.00
  6  03/15/16   0.234    49.94   46.07   0.923  197.812 	
  7  06/14/16   0.228    51.67   45.10   0.873  198.685 	
  8  09/13/16   0.262    53.86   52.06   0.966  199.651 	
  9  12/20/16   0.354    56.63   70.68   1.248  200.899 	
 10  12/31/16            56.08                  200.899   11,266.42
Notes:
  • The 12.66% return can be calculated two different ways:
    12.66% = 11266.42 / 10000 - 1
    12.66% = XIRR(K5:K10, A5:A10)
  • $11,266.42 is also what $10,000 grows to on Morningstar for a date range 1/1/2016 - 12/31/2016.
  • I set the starting date to 1/1/2016 instead of 12/31/2015 since 2016 is a leap year and XIRR works better with 365 day "years".
The return for a year is calculate from the end of the previous year, in this case 12/31/15, not 1/1/16. If you were to do the same calculation from 1/1 to 12/31 for a year that is not a leap year, then the XIRR would not be the same as the total return reported by the fund. Here are the results for VTSMX for 2016 (a leap year).

Code: Select all

VTSMX              Dist. Per  TR Account      TR       Dist. In Cash
   Date      NAV     Share     Balance     Cash Flows	Cash Flows
					
2015-12-31  50.78             $10,000.00   $10,000.00	$10,000.00
2016-03-14  49.92    0.222     $9,874.36                   -$43.72
2016-06-13  51.66    0.216    $10,261.26                   -$42.54
2016-09-12  53.84    0.250    $10,743.94                   -$49.23
2016-12-19  56.62    0.336    $11,365.74                   -$66.17
2016-12-31  56.06             $11,253.33  -$11,253.33  -$11,039.78
					
                           Total Return:       12.53%	
                                   XIRR:       12.50%       12.47%
The Total Return and the XIRR calculated on the total return cash flows are not the same. The Historical Returns web page for the fund' has 12.53% as the 2016 Total Return for the fund.

A comma separated version with formulas is

Code: Select all

VTSMX,,Dist. Per,TR Acct.,TR,Dist. In Cash
Date,NAV,Share,Balance,Cash Flows,Cash Flows 

2015-12-31,50.78,,10000,=d4,=d4
2016-03-14,49.92,0.222,=d4*(b5+c5)/b4,,=-d$4/b$4*c5
2016-06-13,51.66,0.216,=d5*(b6+c6)/b5,,=-d$4/b$4*c6
2016-09-12,53.84,0.250,=d6*(b7+c7)/b6,,=-d$4/b$4*c7
2016-12-19,56.62,0.336,=d7*(b8+c8)/b7,,=-d$4/b$4*c8
2016-12-31,56.06,,=d8*(b9+c9)/b8,=-d9,=-d$4/b$4*b9

,,,TotRet:,=(-e9/e4)-1
,,,XIRR:,=xirr(e4:e9;a4:a9),=xirr(f4:f9;a4:a9)
Here are the results for VTSMX for 2013 (not a leap year).

Code: Select all

VTSMX              Dist. Per  TR Account      TR       Dist. In Cash
   Date      NAV     Share     Balance     Cash Flows	Cash Flows
					
2012-12-31  35.64             $10,000.00    $10,000.00  $10,000.00
2013-03-21  38.81    0.166    $10,936.03                   -$46.58
2013-06-21  39.85    0.176    $11,278.68                   -$49.38
2013-09-20  43.18    0.196    $12,276.63                   -$54.99
2013-12-19  45.58    0.227    $13,023.52                   -$63.69
2013-12-31  46.67             $13,334.97   -$13,334.97 -$13,094.84
					
                              Total Return:     33.35%	
                                      XIRR:     33.35%     33.35%
Total Return and XIRR calculated on the total return cash flows are the same, as they should be for a year that is not a leap year. Rounded to two places after the percent decimal point, the XIRR calculated on the cash flows by taking the distributions in cash is the same as the other two returns. However, if additional digits were shown, that XIRR value would not be the same as the other two returns. The Historical Returns web page for the fund' has 33.35% as the 2013 Total Return for the fund.
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

#Cruncher wrote: Thu Aug 17, 2017 2:19 pm
Oicuryy wrote: Thu Aug 17, 2017 10:28 amNote that the time-weighted rate depends only on the percent gains. This means the time weighted rate is the same whether or not distributions are reinvested.
Never having calculated a time-weighted return, I was skeptical of this claim. So I applied it against the 2016 prices and dividends for Vanguard TSM Index Fund Admiral Shares (VTSAX). I used the 2nd spreadsheet referenced above by Oicuryy to see how a time-weighted return is calculated. Lo and behold, the claim is correct! Whether dividends are re-invested or not, the return is 12.6642%. :!:
In other words, the Total Return is independent of the cash flows. However, the total return of an account in a fund is generally not the same as the Total Return reported for the fund if the fund account was opened after the starting date or closed before the ending date of the period of the Total Return reported for the fund.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

FactualFran wrote: Thu Aug 17, 2017 3:25 pm In other words, the Total Return is independent of the cash flows. However, the total return of an account in a fund is generally not the same as the Total Return reported for the fund if the fund account was opened after the starting date or closed before the ending date of the period of the Total Return reported for the fund.
You can use Morningstar charts which allow specifying start and end dates. In a limited sample M* and Vg were in agreement.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

Doc wrote: Thu Aug 17, 2017 3:43 pm
FactualFran wrote: Thu Aug 17, 2017 3:25 pm In other words, the Total Return is independent of the cash flows. However, the total return of an account in a fund is generally not the same as the Total Return reported for the fund if the fund account was opened after the starting date or closed before the ending date of the period of the Total Return reported for the fund.
You can use Morningstar charts which allow specifying start and end dates. In a limited sample M* and Vg were in agreement.
Do you get the 12.53% annual return for VTSMX reported by Vanguard for 2016 when you do a Morningstar chart for an account opened Jan. 15 2016 and closed on Apr. 15, 2016?
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

Oicuryy wrote: Wed Aug 16, 2017 6:19 pmMutual funds typically report the time-weighted return.
Thanks for pointing this out, Ron. After working on this in my previous post, I now see how to calculate the annual return for a mutual fund much easier than I've always done it before. There is no need to keep track of account value, shares, dividend amounts, or dividend dates. All we need are the starting and ending dates and prices, the dividends per share, and the fund prices used for reinvesting those dividends. I illustrate this below.
FactualFran wrote: Thu Aug 17, 2017 3:13 pmHere are the results for VTSMX for 2016 (a leap year). ... The Total Return and the XIRR calculated on the total return cash flows are not the same.
We can count the number of leap days between the starting and ending dates and, therewith, produce a true annualized return rather than a return per 365 days. Here's an illustration using your example of Vanguard's VTSMX which reports a 12.53% total return for 2016.

Code: Select all

       Col F    Col G   Col H     Col I
Row    Date    Div/sh   Price    Factor
---  --------  ------   -----   --------
 16  12/31/15           50.78 	
 17             0.222   49.92   0.987436  = (0.222 + 49.92) / 50.78
 18             0.216   51.66   1.039183  = (0.216 + 51.66) / 49.92
 19             0.250   53.84   1.047038  ...
 20             0.336   56.62   1.057875  ...
 21  12/31/16           56.06   0.990110  = (0.000 + 56.06) / 56.62
 22                             1.125333  <-- product of factors   [*]
 23                                    1  <-- # of leap days       [*]
 24                             12.5333%  <-- time weighted return [*]
* Here are the formulas for product of factors, # of leap days, and time weighted return using the Excel PRODUCT, INT, YEAR, and DATE functions:

Code: Select all

I22: 1.125333 = PRODUCT(I17:I21)
I23:        1 = INT((YEAR(F21)-IF(F21<DATE(YEAR(F21),2,29),1,0))/4)-INT((YEAR(F16)-IF(F16<DATE(YEAR(F16),2,29),1,0))/4)
I24: 12.5333% = I22 ^ (365 / (F21 - F16 - I23)) - 1
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

#Cruncher wrote: Thu Aug 17, 2017 10:03 pm
FactualFran wrote: Thu Aug 17, 2017 3:13 pmHere are the results for VTSMX for 2016 (a leap year). ... The Total Return and the XIRR calculated on the total return cash flows are not the same.
We can count the number of leap days between the starting and ending dates and, therewith, produce a true annualized return rather than a return per 365 days. Here's an illustration using your example of Vanguard's VTSMX which reports a 12.53% total return for 2016.

Code: Select all

       Col F    Col G   Col H     Col I
Row    Date    Div/sh   Price    Factor
---  --------  ------   -----   --------
 16  12/31/15           50.78 	
 17             0.222   49.92   0.987436  = (0.222 + 49.92) / 50.78
 18             0.216   51.66   1.039183  = (0.216 + 51.66) / 49.92
 19             0.250   53.84   1.047038  ...
 20             0.336   56.62   1.057875  ...
 21  12/31/16           56.06   0.990110  = (0.000 + 56.06) / 56.62
 22                             1.125333  <-- product of factors   [*]
 23                                    1  <-- # of leap days       [*]
 24                             12.5333%  <-- time weighted return [*]
* Here are the formulas for product of factors, # of leap days, and time weighted return using the Excel PRODUCT, INT, YEAR, and DATE functions:

Code: Select all

I22: 1.125333 = PRODUCT(I17:I21)
I23:        1 = INT((YEAR(F21)-IF(F21<DATE(YEAR(F21),2,29),1,0))/4)-INT((YEAR(F16)-IF(F16<DATE(YEAR(F16),2,29),1,0))/4)
I24: 12.5333% = I22 ^ (365 / (F21 - F16 - I23)) - 1
The quote from me that you included contained: "The Total Return and the XIRR calculated on the total return cash flows are not the same." What you posted was only about the total return and did not include the XIRR. The XIRR of a VTSMX account from 12/31/15 to 12/31/16 where the only transactions were reinvesting the dividends is 12.4970% (using the same number of digits as you used for the total return).

The "We can count the number of leap days between the starting and ending dates and, therewith, produce a true annualized return rather than a return per 365 days" looks like an ad hoc modification used only when leap years are involved to force the XIRR result to be a desired "true" value.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

Esoteric alert! :wink: This post concerns only the effect of leap years on calculating annual return. It will interest few people.
FactualFran in previous post wrote:What you posted was only about the total return and did not include the XIRR.
That's correct. But for the case you referred to with only two "cash flows", a starting and ending balance, the Excel XIRR function is not required. The return can be calculated with a formula using only standard arithmetic operators. What I showed was how such a formula could be modified to produce a true annual return whether or not the period includes any leap years.

The XIRR function can't be modified the way my formula was to handle leap years. However, its result can be adjusted to do so in a separate calculation. To illustrate, consider $10,000 growing to $11,000 over three different periods. The table below shows
  • On row 6 the annual return calculated without using the XIRR function.
  • On row 7 the 365 day return calculated with the XIRR function.
  • On row 9 the result from XIRR on row 7 adjusted to be a true annual return.

Code: Select all

                                Col B      Col C     Col D     Col E
Row                           Cash Flow  Period 1  Period 2  Period 3  Formula in column C
---                           ---------  --------  --------  --------  ---------------------------------
  2  Start of period           (10,000)  12/31/15  12/31/14  12/31/15
  3  End of period              11,000   03/13/16  12/31/15  12/31/16
  4  Elapsed days                              73       365       366  = C3 - C2
  5  Leap day adjustment                        0         0         1    (see below)
  6  Annual return without XIRR           61.051%   10.000%   10.000%  = ($B3/-$B2)^(365/(C3-C2-C5)) - 1
  7  365 day return with XIRR             61.051%   10.000%    9.971%  = XIRR($B2:$B3, C2:C3)
  8  Overall growth                       1.10000   1.10000   1.10000  = (1 + C7) ^ ((C3 - C2) / 365)
  9  XIRR return annualized               61.051%   10.000%   10.000%  = C8 ^ (365 / (C3 - C2 - C5)) - 1

Code: Select all

B5: =IF(C3-C2<366,0,INT((YEAR(C3)-IF(C3<DATE(YEAR(C3),2,29),1,0))/4)-INT((YEAR(C2)-IF(C2<DATE(YEAR(C2),2,29),1,0))/4))
Notes:
  • The 73 days in Period 1 is exactly 1/5 of 365 days so 61.051% = 1.1 ^ 5 - 1
  • Formulas in cells C4:C9 should be copied right to column E.
  • I modified the formula to calculate the leap day adjustment (in row 5) from what it was in my previous post to always be zero for periods of 365 days or less, even when the period does contain February 29th as in Period 1 in the table.
  • Using the XIRR function and then adjusting it, isn't needed for the simple case of only two "cash flows". (It's easier to just calculate the annual return as I do on row 6.) However, the method for adjusting it would be useful when there are more cash flows. In such a case, first use XIRR as usual. Then use the formula on row 5 to calculate the number of leap days between the first and last dates. Then use the formulas on rows 8 and 9 to adjust the result from XIRR to a true annual basis.
FactualFran in same post wrote:[#Cruncher's adjustment for leap days] looks like an ad hoc modification used only when leap years are involved to force the XIRR result to be a desired "true" value.
Since my formula works for periods with or without leap years, it is not ad hoc. In some of my earlier posts I did make an ad hoc adjustment. For example, in this post I purposely made the opening date 1/1/2016 instead of 12/31/2015 so the number of days to 12/31/2016 would be 365. But with my formula for calculating leap days, no such fudging is necessary.
User avatar
Oicuryy
Posts: 1959
Joined: Thu Feb 22, 2007 9:29 pm

Re: XIRR - Non-reinvested dividends

Post by Oicuryy »

#Cruncher wrote: Fri Aug 18, 2017 8:20 pmEsoteric alert! :wink: This post concerns only the effect of leap years on calculating annual return. It will interest few people.
Well, I'm interested in how rates of return are calculated.

To my mind, an investment that takes 366 days to grow 10% is growing at a slower rate than one that only takes 365 days. I don't see the point of pretending that leap days do not happen.

Ron
Money is fungible | Abbreviations and Acronyms
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

#Cruncher wrote: Fri Aug 18, 2017 8:20 pm
FactualFran in same post wrote:[#Cruncher's adjustment for leap days] looks like an ad hoc modification used only when leap years are involved to force the XIRR result to be a desired "true" value.
Since my formula works for periods with or without leap years, it is not ad hoc. In some of my earlier posts I did make an ad hoc adjustment. For example, in this post I purposely made the opening date 1/1/2016 instead of 12/31/2015 so the number of days to 12/31/2016 would be 365. But with my formula for calculating leap days, no such fudging is necessary.
My comments were not about your formula, but about whether the result of the XIRR spreadsheet function is the same as the total return when there are no intermediate cash flows. I tend to avoid esoterica, such as adjusting the results of a standard function, like the XIRR spreadsheet function, to get a value that can be simply calculated.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

FactualFran wrote: Sat Aug 19, 2017 12:42 pm My comments were not about your formula, but about whether the result of the XIRR spreadsheet function is the same as the total return when there are no intermediate cash flows. I tend to avoid esoterica, such as adjusting the results of a standard function, like the XIRR spreadsheet function, to get a value that can be simply calculated.
This was the discussion that I was having with #cruncher. If you assume a constant rate of return then the XIRR spreadsheet returns the same value as the total return whether or not you reinvest dividends. But as #cruncher has demonstrated you allow the return to very during the period then the two may not be equal. If the differences in rates of return are only slightly different over the course it probably doesn't make mush of a difference.

You also need to ask yourself what your are using the numbers for. Do you want to say that fund A is better than fund B? If so by how much? I suggest the reinvestment question is likely to be much smaller than the choice of beginning and end points. In any case I think neither Total Return or IRR is a good metric to use to make the decision. Looking at a rolling return chart can give you a whole lot of data points that you can observe visually without doing any math at all. And you don't have to worry about leap year unless you have to sell on February 29th in 2019.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
User avatar
Oicuryy
Posts: 1959
Joined: Thu Feb 22, 2007 9:29 pm

Re: XIRR - Non-reinvested dividends

Post by Oicuryy »

How many years are there in the 366 day period from 12/31/15 to 12/31/16?

XIRR always uses 365 days as the length of a year. So XIRR calculates 366/365 = 1.0027397 years in that period. To annualize the 12.5333% gain over that period, XIRR calculates 1.125333^(1/1.0027397) -1 = 12.4970% annual rate of return.

Apparently Vanguard decided there was exactly 1 year in that period. They calculated 1.125333^(1/1)-1 = 12.5333% annual rate of return.

XIRR and Vanguard answered the question at the start of this post differently and got different annual rates of return for the same gain over the same time period.

Ron
Money is fungible | Abbreviations and Acronyms
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: XIRR - Non-reinvested dividends

Post by #Cruncher »

Warning! This post will get even more esoteric. I wonder if the board has a policy against discussing epistemological issues? After all, we don't want people coming to blows over the meaning of words like "year". :wink:

Oicuryy in previous post wrote:How many years are there in the 366 day period from 12/31/15 to 12/31/16?
A fair question. But what if we pose it as "How many years are there in the year 2016?" I'm sure with a calculator we can all agree on how many minutes, hours, and days there are. [*] But we seem to differ on how many years there are! Maybe I'm just a simpleton, but my answer is ... drum roll ... "one". And I'm also simple enough to interpret "annual" as meaning "per year", not "per 365 days".

Oicuryy in same post wrote:XIRR always uses 365 days as the length of a year. So XIRR calculates 366/365 = 1.0027397 years in that period.
The XIRR function doesn't assume there are 365 days in a year. It simply produces a return per 365 days. Whether we want to take that as the return per year is up to us. I'm aware that this description of the Excel XIRR function says "All succeeding payments are discounted based on a 365-day year. " and that it shows this formula where each period is divided by 365:
Image

But Bill Gates would probably have fired any programmer who actually calculated it that way. There is no need to divide every cash flow interval by 365 as one is iteratively zeroing in on the result. I'd bet that XIRR is actually calculated the same way as the IRR function which calculates the return per period without caring what that period is. Then, when XIRR is done with this hard work, it simply converts the return per period to the return per 365 periods [ xirr = (1 + irr) ^ 365 - 1 ].

Oicuryy in same post wrote:Apparently Vanguard decided there was exactly 1 year in that period.
I think you got it backward here, Ron. Vanguard didn't start with the objective to compute a return for the period 12/31/2015 - 12/31/2016 and then decided that period was one year long. Instead its intent was to compute the return for the year 2016. To do so, it just picked the closing share prices for 2015 and 2016 as the starting and ending prices for its return computation.

Oicuryy in same post wrote:XIRR and Vanguard answered the question at the start of this post differently and got different annual rates of return for the same gain over the same time period.
As I've tried to explain above, neither the Excel XIRR function nor Vanguard addressed (or needed to address) the question of how many years are in the period 12/31/2015 - 12/31/2016. As I also tried to explain, XIRR doesn't get an annual return. It gets a return per 365 days. Whether or not we take this to be an annual return is up to us.

* I left out seconds. Apparently a leap second was added at the end of 2016 so there were 31,622,401 seconds in the year. So, maybe I'm being hasty in saying we can all agree on how many minutes, hours, and days there are in the year 2016. Oicuryy might say there are 366 + 1 / 86,400 days. :wink:
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

#Cruncher wrote: Sun Aug 20, 2017 10:03 am I left out seconds. Apparently a leap second was added at the end of 2016 so there were 31,622,401 seconds in the year. So, maybe I'm being hasty in saying we can all agree on how many minutes, hours, and days there are in the year 2016. Oicuryy might say there are 366 + 1 / 86,400 days.
You also probably didn't remember if February 29, 2000 existed or not.
In the Gregorian calendar, years that are divisible by 100, but not by 400, do not contain a leap day. Thus, 1700, 1800, and 1900 did not contain a leap day; neither will 2100, 2200, and 2300. Conversely, 1600 and 2000 did and 2400 will.
. https://en.wikipedia.org/wiki/February_29
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

Doc wrote: Sat Aug 19, 2017 3:50 pm This was the discussion that I was having with #cruncher. If you assume a constant rate of return then the XIRR spreadsheet returns the same value as the total return whether or not you reinvest dividends. But as #cruncher has demonstrated you allow the return to very during the period then the two may not be equal. If the differences in rates of return are only slightly different over the course it probably doesn't make mush of a difference.
I don't make any assumption about a constant rate of return. I use the results of the XIRR spreadsheet function as is. The fact that the result of the XIRR is consistent with a constant daily rate of return is a detail that I am usually not concerned about.

In general, the result of the XIRR spreadsheet function is not the same as the Total Return reported by a mutual fund. A condition under which they are the same is when distributions are reinvested, the period is an integral number of years, and the number of days in the period is 365 times the number of years.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

FactualFran wrote: Mon Aug 21, 2017 3:58 pm I don't make any assumption about a constant rate of return. I use the results of the XIRR spreadsheet function as is
The XIRR function is bases on a constant rate of return. That's the way the classic discounted cash flow equations are based on. When you try to compare the XIRR constant average return with a fund's total return calculation which allows for a varying return you may get different answers. The assumptions are built into the two calculation methods. If you use either one you also assume the average or varying return built into the functions.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: XIRR - Non-reinvested dividends

Post by FactualFran »

Doc wrote: Tue Aug 22, 2017 5:59 am
FactualFran wrote: Mon Aug 21, 2017 3:58 pm I don't make any assumption about a constant rate of return. I use the results of the XIRR spreadsheet function as is
The XIRR function is bases on a constant rate of return. That's the way the classic discounted cash flow equations are based on. When you try to compare the XIRR constant average return with a fund's total return calculation which allows for a varying return you may get different answers. The assumptions are built into the two calculation methods. If you use either one you also assume the average or varying return built into the functions.
The above quote from me was only the first sentence of three sentence paragraph. The third sentence of that paragraph was: "The fact that the result of the XIRR is consistent with a constant daily rate of return is a detail that I am usually not concerned about." In other words, I understand that the result of XIRR is based on a constant daily rate of return.

The main point of that post was the second, and only other, paragraph:
In general, the result of the XIRR spreadsheet function is not the same as the Total Return reported by a mutual fund. A condition under which they are the same is when distributions are reinvested, the period is an integral number of years, and the number of days in the period is 365 times the number of years.
Getting back to the topic (XIRR - Non-reinvested dividends), in general, the result of the XIRR spreadsheet function is not the same when distributions are reinvested and when distributions are taken in cash. It is possible to construct examples where they are the same.
User avatar
Doc
Posts: 10607
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: XIRR - Non-reinvested dividends

Post by Doc »

FactualFran wrote: Tue Aug 22, 2017 1:39 pm Getting back to the topic (XIRR - Non-reinvested dividends), in general, the result of the XIRR spreadsheet function is not the same when distributions are reinvested and when distributions are taken in cash. It is possible to construct examples where they are the same.
If the yield is constant they are the same as in the coupon bond and zero coupon bond. It's arithmetic.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
Post Reply