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.

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.

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%):

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%.

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.

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.

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%.

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.

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.

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.

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.

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.

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%.

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.

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.

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.

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.

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.

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.

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?

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.

$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.

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:

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

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.

(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.

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).

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.

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%.

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 then12.6642% = XIRR(B1:B2, A1:A2)

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.

$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).

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.

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.

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.

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.

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?

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.

Here 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.

Here 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.

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.

Esoteric alert! 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.

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.

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.

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.

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.

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".

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:

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.

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.

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.

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.

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.

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.