Once upon a time, in order to explain the use of Excel's XIRR function to calculate portfolio returns, I said that reinvested dividends should be treated as positive cash flows and ...

>Huh? XIRR? XIRR is explained here and here. The idea is to list your investments and withdrawals along with dates and use:     XIRR(investments,dates,a guess) as shown in Figure 1. There are a few important points to remember: The first entry (equal to the portfolio value) must have the earliest date. Later entries may be in any order (so long as they don't have a date earlier than the first entry). Investments are entered as positive amounts and withdrawals as negative amounts. The current/final portfolio is entered as a negative amount. You make some initial guess at the correct answer (like 0.1, meaning 10%). The return generated by XIRR is an annualized return (over the period defined by the dates).

Figure 1

>Annualized?
Yes. Whether the time period is ten years or ten months, an answer like 12.3% means the return is equivalent to an "annual" return of 12.3%.

>And Year To Date return?
The first (and earliest) entry should then be January 1 of this year (meaning the portfolio value as of January 1).
Then an XIRR return of 12.3% means that, at the current rate of return (since Jan 1), you'd make 12.3% over the entire 365-day year.
If, however, only N days have passed since Jan 1, you can get a YTD return using: (1+0.123)^N/365 - 1.

For example, suppose it's May 12 so that 131 days have passed since Jan 1.
If XIRR gives an annualized (365-day) return of 12.3%, your YTD (161-day) return would be:   (1+0.123)^131/365 - 1 = 0.045 or 4.5%.

>And dividends?
Uh ... yes. I originally suggested that reinvested dividends should be treated as (positive) entries, meaning they're new investments. In fact, I was wrong and ...

>You? Wrong!? I can't believe ...
Pay attention.
Consider the following Sam & Sally scenario.
>Sam & Sally? Not again!
Pay attention!

• Sam and Sally each start the year (Jan 1) with $10K and end the year (Dec 31) with $12K.
• Neither Sam nor Sally make any new investments into their portfolio (from out of their pocket)
• Neither do they withdraw any money from their portfolio (to go into their pocket).
• The calculation of their (annual) return is simple. It's:
    (final portfolio)/(initial portfolio) - 1 = 12000/10000 - 1 = 0.20 or 20%.

If Sally tries to use XIRR, entering amounts and dates to incorporate all the activity between Jan 1 and Dec 31, do you think she'll get 20%?

>Unlikely, I'd say, except, of course, reinvested dividends should be entered as ... No! That was my earlier mistake. The only numbers she should enter are the starting and ending portfolio values as in Figure 2.

Figure 2

>$10K and $12K, right?
Right. She should ignore all the activity which was internal to her portfolio, including dividends.
Any dividends would influence that final portfolio value, of course, but they shouldn't appear as XIRR cash flows.

>But what if I withdraw some money, or maybe ...?
Aah, that's a horse of a different hue. Cash flows that are external to your portfolio should definitely be incorpoarated into the XIRR calculation.

>Internal? External? What ...?

Figure 3

What I mean is you should ignore any activity that takes place inside your portfolio. But activity which involves money in to or out of your portfolio must be incorporated into the XIRR calculation. Figure 1, for example, involves new investments (in black) and withdrawals (in red).

>And your current portfolio in blue, eh?
Well ... yes.

A final note:

If XIRR isn't available on your copy of Excel, run the Setup program to install the Analysis ToolPak.
After you install the Analysis ToolPak, enable it by using the Add-Ins command on the Tools menu.

There's a spreadsheet to play with. Just click here.