XIRR  Nonreinvested dividends
XIRR  Nonreinvested dividends
Are nonreinvested dividends considered withdrawals?
 White Coat Investor
 Posts: 13008
 Joined: Fri Mar 02, 2007 9:11 pm
 Location: Greatest Snow On Earth
Re: XIRR  Nonreinvested dividends
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
Re: XIRR  Nonreinvested dividends
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.
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.
Re: XIRR  Nonreinvested dividends
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.
Re: XIRR  Nonreinvested dividends
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.
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.
Re: XIRR  Nonreinvested dividends
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%):pshonore wrote: ↑Mon Aug 14, 2017 7:37 amDepends 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.
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%
 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%.
Re: XIRR  Nonreinvested dividends
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.goingup wrote: ↑Mon Aug 14, 2017 8:38 amYes, 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.
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.

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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.Doc wrote: ↑Mon Aug 14, 2017 10:22 amThe 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.goingup wrote: ↑Mon Aug 14, 2017 8:38 amYes, 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.
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.
Re: XIRR  Nonreinvested dividends
Darn I was hoping #cruncher would chime in. Now I have to figure this out myself. Will report back.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.
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.
Re: XIRR  Nonreinvested dividends
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.
Re: XIRR  Nonreinvested dividends
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: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.
 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.
Code: Select all
12/31/2016 (10,000)
12/31/2017 1,000
12/31/2018 11,000
XIRR or IRR 10.00%
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%
Re: XIRR  Nonreinvested dividends
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.goingup wrote: ↑Mon Aug 14, 2017 8:38 amYes, 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.
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.

 Posts: 2518
 Joined: Sat Aug 11, 2012 8:44 am
Re: XIRR  Nonreinvested dividends
This wiki page and spreadsheet might be of interest to some readers: wiki: Calculating personal returns.
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
Re: XIRR  Nonreinvested dividends
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
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 195159) Here is a start for understanding the differences for those of you that are interested.
From Microsoft Office Support the XIRR function
Description
https://support.office.com/enus/articl ... nUS&ad=USReturns 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.
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 195159) Here is a start for understanding the differences for those of you that are interested.
Read more: Discounted Cash Flow Analysis http://www.investopedia.com/university/ ... z4prbjUS25Financial ratios and multiples – including metrics like debttoequity ratio, priceearnings 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.
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.
Re: XIRR  Nonreinvested dividends
What's the benefit of your spreadsheet over XIRR?longinvest wrote: ↑Tue Aug 15, 2017 8:18 amThis wiki page and spreadsheet might be of interest to some readers: wiki: Calculating personal returns.
Re: XIRR  Nonreinvested dividends
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
Code: Select all
B4: =XIRR(B1:B3, $A1:$A3)
B5: =IRR(B1:B3)
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.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.
Re: XIRR  Nonreinvested dividends
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.#Cruncher wrote: ↑Tue Aug 15, 2017 10:39 pmDoc, 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.
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.
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 nonreinvested 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.
Re: XIRR  Nonreinvested dividends
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 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.
So, does this mean you are accepting my argument, Doc?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.
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 twoyear 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.Doc in same post wrote:This does not happen with a bond ...
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%
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})
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:At the end of the period the "Total Annualized Return" is (Ending$/Beginning$)^(1/n) 1 . No XIRR function required.
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.Doc in same post wrote:Hopefully this answers the question about XIRR and nonreinvested dividends: "Total Annualized Return" from a fund is not the same as the "Internal Rate of Return" calculated by using the XIRR function.
Re: XIRR  Nonreinvested dividends
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.
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.
Re: XIRR  Nonreinvested dividends
The timeweighted rate of return and the dollarweighted rate of return can be different. Mutual funds typically report the timeweighted return. But they are required by the SEC to call it the "average annual total return". IRR and XIRR calculate the dollarweighted return. See these pages from Gummy.
http://www.financialwisdomforum.org/gum ... ndyou.htm
http://www.financialwisdomforum.org/gum ... eturns.htm
Ron
Money is fungible 
Abbreviations and Acronyms
Re: XIRR  Nonreinvested dividends
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.Doc wrote: ↑Wed Aug 16, 2017 4:59 pmWe'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.
Re: XIRR  Nonreinvested dividends
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.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?
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
 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 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:As an aside: What the heck is "IRR({10000, 0, 12050})"?
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.Doc in same post wrote:You must be a lot younger than me. I earned my discounted cash flow bones using a slide rule ...
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 timeweighted return and the dollarweighted 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.
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:alex_686 in previous post wrote:Technically speaking "Total Annualized Return" is not the "Internal Rate of Return".
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)
Re: XIRR  Nonreinvested dividends
.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.
Re: XIRR  Nonreinvested dividends
(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 timeweighted 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 dollarweighted rate of return is the value of `i` that solves this equation.
`f_n=sum_(j=0)^(n1)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 timeweighted rate depends only on the percent gains. This means the time weighted rate is the same whether or not distributions are reinvested.
The dollarweighted rate depends only on the cash flows. It will be different if distributions are or are not reinvested. XIRR calculates the dollarweighted 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 timeweighted rate or the dollarweighted rate with all distributions reinvested.
Yes, I know there can be multiple solutions for `i` in the dollarweighted 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
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 timeweighted 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 dollarweighted rate of return is the value of `i` that solves this equation.
`f_n=sum_(j=0)^(n1)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 timeweighted rate depends only on the percent gains. This means the time weighted rate is the same whether or not distributions are reinvested.
The dollarweighted rate depends only on the cash flows. It will be different if distributions are or are not reinvested. XIRR calculates the dollarweighted 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 timeweighted rate or the dollarweighted rate with all distributions reinvested.
Yes, I know there can be multiple solutions for `i` in the dollarweighted 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

 Posts: 2518
 Joined: Sat Aug 11, 2012 8:44 am
Re: XIRR  Nonreinvested dividends
Exactly!Oicuryy wrote: ↑Thu Aug 17, 2017 10:28 amNote that the timeweighted rate depends only on the percent gains. This means the time weighted rate is the same whether or not distributions are reinvested.
The dollarweighted rate depends only on the cash flows. It will be different if distributions are or are not reinvested. XIRR calculates the dollarweighted rate.
I've always held the belief that mutual funds reported timeweighted returns. Doing otherwise wouldn't make sense.
For practical purpose, the wiki: Calculating personal returns spreadsheet calculates both types of returns (timeweighted and moneyweighted) for a personal portfolio spread across multiple investment accounts.
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR

 Posts: 2518
 Joined: Sat Aug 11, 2012 8:44 am
Re: XIRR  Nonreinvested dividends
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:
The singleperiod timeweighted returns for each of P1 and P2 are:
The cumulative timeweighted return over both periods is thus:
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).
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
Code: Select all
RP1 = ((MP + MD) / SP)  1
RP2 = ((EP + ED) / MP)  1
Code: Select all
CumP1P2 = ((1 + RP1) X (1 + RP2))  1
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).
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
Re: XIRR  Nonreinvested dividends
Never having calculated a timeweighted 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 timeweighted return is calculated. Lo and behold, the claim is correct! Whether dividends are reinvested 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%
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%
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)

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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).#Cruncher wrote: ↑Wed Aug 16, 2017 10:12 pmThat'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.Notes: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
 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".
Code: Select all
VTSMX Dist. Per TR Account TR Dist. In Cash
Date NAV Share Balance Cash Flows Cash Flows
20151231 50.78 $10,000.00 $10,000.00 $10,000.00
20160314 49.92 0.222 $9,874.36 $43.72
20160613 51.66 0.216 $10,261.26 $42.54
20160912 53.84 0.250 $10,743.94 $49.23
20161219 56.62 0.336 $11,365.74 $66.17
20161231 56.06 $11,253.33 $11,253.33 $11,039.78
Total Return: 12.53%
XIRR: 12.50% 12.47%
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
20151231,50.78,,10000,=d4,=d4
20160314,49.92,0.222,=d4*(b5+c5)/b4,,=d$4/b$4*c5
20160613,51.66,0.216,=d5*(b6+c6)/b5,,=d$4/b$4*c6
20160912,53.84,0.250,=d6*(b7+c7)/b6,,=d$4/b$4*c7
20161219,56.62,0.336,=d7*(b8+c8)/b7,,=d$4/b$4*c8
20161231,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)
Code: Select all
VTSMX Dist. Per TR Account TR Dist. In Cash
Date NAV Share Balance Cash Flows Cash Flows
20121231 35.64 $10,000.00 $10,000.00 $10,000.00
20130321 38.81 0.166 $10,936.03 $46.58
20130621 39.85 0.176 $11,278.68 $49.38
20130920 43.18 0.196 $12,276.63 $54.99
20131219 45.58 0.227 $13,023.52 $63.69
20131231 46.67 $13,334.97 $13,334.97 $13,094.84
Total Return: 33.35%
XIRR: 33.35% 33.35%

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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.#Cruncher wrote: ↑Thu Aug 17, 2017 2:19 pmNever having calculated a timeweighted 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 timeweighted return is calculated. Lo and behold, the claim is correct! Whether dividends are reinvested or not, the return is 12.6642%.
Re: XIRR  Nonreinvested dividends
You can use Morningstar charts which allow specifying start and end dates. In a limited sample M* and Vg were in agreement.FactualFran wrote: ↑Thu Aug 17, 2017 3:25 pmIn 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.
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.

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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?Doc wrote: ↑Thu Aug 17, 2017 3:43 pmYou can use Morningstar charts which allow specifying start and end dates. In a limited sample M* and Vg were in agreement.FactualFran wrote: ↑Thu Aug 17, 2017 3:25 pmIn 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.
Re: XIRR  Nonreinvested dividends
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.
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.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.
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 [*]
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

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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).#Cruncher wrote: ↑Thu Aug 17, 2017 10:03 pmWe 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.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.* 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
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 [*]
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 "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.
Re: XIRR  Nonreinvested dividends
Esoteric alert! This post concerns only the effect of leap years on calculating annual return. It will interest few people.
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
Notes:
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.FactualFran in previous post wrote:What you posted was only about the total return and did not include the XIRR.
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/(C3C2C5))  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(C3C2<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))
 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.
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.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.
Re: XIRR  Nonreinvested dividends
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

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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.#Cruncher wrote: ↑Fri Aug 18, 2017 8:20 pmSince 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.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.
Re: XIRR  Nonreinvested dividends
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.FactualFran wrote: ↑Sat Aug 19, 2017 12:42 pmMy 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.
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.
Re: XIRR  Nonreinvested dividends
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
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
Re: XIRR  Nonreinvested dividends
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".
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 ].
* 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.
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 previous post wrote:How many years are there in the 366 day period from 12/31/15 to 12/31/16?
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 365day year. " and that it shows this formula where each period is divided by 365: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.
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 ].
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:Apparently Vanguard decided there was exactly 1 year in that 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.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.
* 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.
Re: XIRR  Nonreinvested dividends
You also probably didn't remember if February 29, 2000 existed or not.#Cruncher wrote: ↑Sun Aug 20, 2017 10:03 amI 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.
. https://en.wikipedia.org/wiki/February_29In 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.
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.

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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.Doc wrote: ↑Sat Aug 19, 2017 3:50 pmThis 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.
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.
Re: XIRR  Nonreinvested dividends
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.FactualFran wrote: ↑Mon Aug 21, 2017 3:58 pmI don't make any assumption about a constant rate of return. I use the results of the XIRR spreadsheet function as is
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.

 Posts: 497
 Joined: Sat Feb 21, 2015 2:29 pm
Re: XIRR  Nonreinvested dividends
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.Doc wrote: ↑Tue Aug 22, 2017 5:59 amThe 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.FactualFran wrote: ↑Mon Aug 21, 2017 3:58 pmI don't make any assumption about a constant rate of return. I use the results of the XIRR spreadsheet function as is
The main point of that post was the second, and only other, paragraph:
Getting back to the topic (XIRR  Nonreinvested 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.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.
Re: XIRR  Nonreinvested dividends
If the yield is constant they are the same as in the coupon bond and zero coupon bond. It's arithmetic.FactualFran wrote: ↑Tue Aug 22, 2017 1:39 pmGetting back to the topic (XIRR  Nonreinvested 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.
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.