As you invest, it is important to know about how you are doing. It is easy to see how your individual investments are doing each year. They report to you their time-weighted return. Of course, that isn’t the return you generate. You get a dollar-weighted return. It turns out that the dollar-weighted return for most investors is far lower than the time-weighted return of their investments. This occurs mostly due to performance chasing and the buying high/selling low phenomenon that results from it.

While I don’t think you need to look at your investments every day, or even every month, from time to time you ought to check in and see how you are doing. If you don’t know what returns you have been getting, it is hard to gauge how well you are progressing toward your goals. It is also easier to get sucked into investments that promise a high return, but don’t actually deliver. In short, being able to calculate your own return empowers you as an investor.

The best way to calculate your return is to use the Excel XIRR function (also available with other spreadsheets and financial calculators). This gives you a dollar-weighted return because it takes into account the timing and amount of your cash flows into and out of your retirement funds. It is surprisingly easy to calculate. All you need to know is the amounts you have put in or taken out of the account and the dates on which you did that. Here’s a quick tutorial:

Put the amounts of your cash flows into column A. Amounts you contributed to retirement and other investment accounts are positive. Amounts you took out are negative. The last entry should be the current amount you have, as a negative number. Put the dates of the cash flows into column B. You need to use the excel DATE function to do this. It looks like this: =DATE(2004,8,16) where 2004 is the year, 8 is the month, and 16 is the date.

Now, in another cell, put in the XIRR function. It looks like this: =XIRR(A1:A10, B1:B10, 5%) where your cash flows are in cells A1 to A10, your date functions are in cells B1 to B10, and 5% is your estimated return. (If left blank, it defaults to 10%.)

Now for an example:

If you want, click on the button in the lower right and follow the directions if you want to download this file to play with it.

Remember that 9.75% is an annualized return, so it means that between 7/11/07 and 5/19/11 this investment returned 9.75% PER YEAR. If your period of time is less than one year, it will also annualize the return. For example, if your period is 6 months, and your return is 5%, then XIRR would return 10%.

If you would like to calculate a year to date return and/or calculate out your return for each calendar year you have had the investment, it only gets a little more complicated. First, you’ll need to add in the value of the investment at the end of each year. I use two entries, the first negative and the second positive, both with the date of 12/31 of the given year. Then, run the XIRR function from the positive entry on 12/31 of one year, to the negative entry on 12/31 of the next. See the next spreadsheet for details of how to calculate returns for partial years, full years, and the year to date.

Play around with it for a few minutes and you’ll figure it out. If you’re a spreadsheet junkie, you can also break it down for each retirement account, or even each individual investment. All you need are your inflows and outflows, and the corresponding dates. If you want to calculate the yearly returns or the year to date return, you’ll also need the year end values of the investments. But you don’t have to take into account fees, commissions, or any dividends or capital gains that are reinvested. Of course, if you don’t reinvest dividends, those should be considered withdrawals from the account, just like fees paid from a separate account, should be considered contributions to the account. XIRR is a powerful function that will allow you to calculate your portfolio’s overall returns, no matter how many different retirement accounts you have.

Thanks for the info. I played around with XIRR function, but I have a question: how does one deal with dividends?

That’s one of the beauties of XIRR. You don’t have to deal with reinvested dividends. All you need is the beginning value and ending value. If you’re spending the dividends, count them as withdrawals from the account.

Sorry to “reply” to a post, but I couldn’t find a way to simply make a comment.

I am hoping you can help me. I am having a debate with a colleague about XIRR. We work in private equity with real estate investments and use the XIRR function to report our realized investment returns. Our typical deal stretches over several years and involves a rather large up front investment, several smaller returns from operational cash flows, and a large final return from the sale of the asset. However, we often have a holdback distribution that can take several years to come back and is usually a very small cash flow.

I have assured my colleague that the holdback distribution, no matter how small and no matter how much time passes, will not serve to lower the XIRR that was calculated at the time of sale. I have gone so far as to show him an extreme example of a holdback distribution that takes 250 years to come back, and the XIRR simply remains unchanged. He argues this is illogical and quotes exactly what you have in this blog, “Remember that 9.75% is an annualized return, so it means that between 7/11/07 and 5/19/11 this investment returned 9.75% PER YEAR.” Therefore, if the final distribution occurs in the year 2261, the XIRR should be diluted to account for the 254 year investment time frame. However, it is not.

I argue that XIRR is actually an “annualized” return, not an “annual” return. The distinction being that an annual return does tell you the rate of return for every year during the entire life of the investment. An annualized return take into account both the size and timing of the investment and condenses them over the yield curve as if the entire investment occurred over a one year period. Therefor, the final sale proceeds (which are the largest monies distributed) have the largest weighting, and a small holdback distribution many years later has such little impact as the curve expands to infinite, that it results in no change whatsoever to the XIRR. (Only if the final holdback distribution were extremely large would it impact the XIRR, and in that case it would only serve to increase the XIRR, never to decrease the XIRR.)

I would appreciate if you would weigh in and help us to determine what is actually occuring with the XIRR when the investment period becomes significantly lengthened by the final holdback distribution. Thank you.

I agree with you. It’s really a dollar weighted return, not a time-weighted return. So if you invest $1000, get $2000 back in year 10, and then get $10 back in year 100, that $10 just isn’t going to affect the XIRR significantly.

Two questions,

Why do you have to “guess” a 10% or 5% return? What is that doing in the formula?

Do you have to put in the date 2012,10,23, or could you put just the cell numbers that the date range is in?

Thanks, really appreciate the info.

The guess is initiating a search. Lots of higher level mathematics have no closed form solution (eg an equation that gives you a definite answer in a finite number of steps) instead they have answers that either involve an infinite amount of steps or can only be approximated. Other answers are too difficult to exactly solve so using an approximate solution requires much fewer calculations than a “true” answer.

The branch of mathematics that deals with efficient search algorithms is known as numerical analysis. Inputting a reasonable guess will either save machine calculations or allow an actual answer. If your guess is too unreasonable the algorithm may not converge to an answer. With what you are using it for this is unlikely even with a really bad initial guess; however the underlying algorithm is known as “solver” in excel and this is one of the functions that calls solver up. In multidimensional searches etc… the starting guess would be much more important. Hope that helps.

FWIW,

Regarding the 5% or 10% guess discussion, I used the YTD formula from the author’s row 28 example and played around with the different guess return numbers. I used, 5%, 10%, 100%, 1000%, or used the formula with no guess percentage. For all results, including not entering a guess percentage in the YTD formula, all results were exactly the same.

Brian

nice!

I’m not sure I’m enough of a math whiz to answer your first question. It has to do with the algorithm the function follows. You do have to use the “date” function, but if you put in the cell number and a date function is in the cell that’s okay.

Those who know a little about investing know that 9% consistent returns over 2 years is better than 18% one year and 0% the next, still with an average of 9%. I used this function to help figure out what the equivalent “consistent return” on a mutual fund might be. You know, they will advertise the better of the 2 numbers: The average annual performance. So I took a Vanguard fund (big Vanguard fan) that had 9.02% average annual performance over 10 years, and I used the XIRR function to see what would be the equivalent CONSISTENT return over the 10 years. Turns out it was 7.2%. In other words, a non-volatile fixed investment of 7.2% would return the same as this fund that was volatile but averaged 9% annually. Really hammered home this idea that the stock market has NOT returned 10-11% over time ….. This is the average of its yearly returns, which is not the number we really want when we’re estimating how much our money will grow with time.

An excellent point. The difference between the arithmetic mean and the geometric mean and the significance of it is pretty depressing.

I’ve just discovered XIRR and came across this, which was by far the clearest article on the subject. Thank you!

I know I don’t have to enter reinvested dividends and that makes sense because it’s not “my” money going in as a contribution and is internal to the account. It’s not clear to me why, then, dividends not reinvested should be removed, but I honestly don’t care about that scenario as all stocks in my portfolio are in DRIPs.

The article mentions about not having to take fees into account. Consider this: I send in $100. They take $3.10 in fees, etc., actually investing $96.90 for me. Do I enter $96.90 for the amount of that transaction, or $100? My initial thought was $96.90, because that’s what actually went towards the purchase and I continued onward, but I am now second-guessing myself.

I disagree. You should be putting in $100. That’s what it cost you. Otherwise you’re calculating your return without regard to fees, which isn’t really what you should care about.

Dividends not reinvested don’t stay in the account, that’s why you treat them as withdrawals. As long as they’re reinvested you can ignore them for the calculation.

You make a nice explanation of the XIRR function, but I have a few questions since I’m not a math or Excel whiz, but would love to test it on my DRIP’s and mutual funds. So…my questions:

1. When explaining above the first spreadsheet, you say “The last entry should be the current amount you have, as a negative number.” What’s the current amount? Is it the market value of my investment on a certain day (e.g. 100 share x $105/sh.on 10/15/12 = $10,500 as a negative value for XIRR funcion)?

2. PP kind of explained why dividends reinvestments should be excluded from the data input, but even though he/she doesn’t care to know why not reinvested dividends have to be showed as cash out-flows, I would like to know. Why reinvested dividends need to be excluded from the data input, but if I choose to not reinvest, it has to be entered as a negative number (what date to use for it?)?

. How to calculate a XIRR of a portfolio consisting of say 5 DRIP’s & 3 mutual funds, when weekly and monthly investments occur? Do I have to combine all contibutions in one file in chronological order? What’s the current amount of all 5 investments? Add market values of all?

I think I’ve got more q. but it’s enough for starters :-)).

1. Yes. Exactly.

2. If you reinvest the dividend it isn’t money taken out of the investment, so you don’t have to include it. If you don’t reinvest it, it’s money taken out, so you need to include it as a negative number. Use the dividend date.

3. Yes, you have to include all contributions with their relevant dates. I don’t think they actually have to be in order, but I keep mine in order. The current amount is the market value of all. I keep track of my entire portfolio that way. A separate XIRR for each investment and a grand total XIRR for the total.

Is the grand total XIRR just an average? A weighted average?

It’s the annualized dollar weighted return. When you say Average, what do you mean?

Whoops, should have been more clear. I was referring to calculating a grand total XIRR across multiple accounts.

So, to rephrase the question:

Say one has three different accounts, each with a different XIRR. HOw does one calculate a “grand total” XIRR, or a return that takes into consideration all of the accounts? Would it be a weighted average the XIRRs of all three accounts?

I keep a running total of all additions and withdrawals to my entire portfolio (and the dates) in a separate column, and XIRR it.

Ah – genius. Thanks!

If you want to get really technical about it you can count the dividends as contributions. In that scenario you’re picking apart the returns, so you can really see how much capital growth you’ve got as compared to passive income.

I personally like to just lump it all together because I have no need to manipulate the returns that I should (as asset managers would). For me it makes the most sense to see what I put in VS what is it now, and to account for the timing of investments.

You can only count the dividends as contributions if you first count them as withdrawals. As you might imagine, not counting them at all does the exact same thing.

How do you account for changing asset allocations? Thanks.

It’s all one pot of money. Run the numbers on the whole pot no matter what investments you’re using.

Thank you for your reply!

I’ve been a stock picker (value large cap) for 12 years now investing in both canada/us markets with AA varying from 100% to 70% equities. After calculating my IRR, what benchmark would be relevant to evaluating my performance since benchmarks are usually all equity? Thanks again.

I suppose I’d run it against a benchmark composed of a large cap value index fund 85% or so and 15% in whatever else you put your money into when it’s not in large cap value stocks.

Great summary. I have not kept up with the dates of my prior contributions over the years, and I don’t want to go back and do that. If I start the list with my total account amount and today’s date, then add contributions as I make them, will the formula work to calculate returns from here on out? Thank you!

Yes. Your first date will be today and the first value will be your total account value today. It would probably be relatively easy to at least go back to the first of the year.

I’m still not clear about a few things.

1. Should the XIRR cell formatted as Percentage? My XIRR project has been put on the back burner for the time being, but yesterday I decided to test on one of my DRIP accounts and I got an EXTREMELY SAD result 🙂 (that it’s really making me laugh instead…I’m not on dope though). After I entered =XIRR(A1:A10, B1:B10, 5%)I got something like 0.13, I think. I changed 5% to 0.05, same answer. While driving to work today, I started thinking that maybe the cell is formatted as a General number or Accounting, and I should re-format to percentage. Could be the case here? But if it was formatted as a percentage cell, then my DRIP won’t be funny anymore.

2. I’d like some clarification from your exchange with the FutureMedStudent in June’13 about the grand total XIRR.

If I understand you correctly, you calculate a XIRR for each investment (in its own worksheet perhaps) and then then you have a combo worksheet containing ALL your investment accounts (all taxable investments, all IRA’s and/or Roth IRA’s, 401k’s etc.) in chronological order and calculate the ‘grand’ XIRR in that way. Is this correct? I’m asking because I’ve read somewhere people calculating a XIRR of many XIRR’s which I found quite confounding.

3. I’m also curious how you do yourself and if it makes sense or not to do it. So, with regards to the question 2, do you calculate yearly XIRR and keep it for your future reference (to see how your investments did well or bad in hindsight) or after each passing year you delete prior year’s XIRR and calculate a new cumulative XIRR for a new period. E.g. Let’s I started investing in Oct’05, so it’s a XIRR for 2005. Then I continue investing in 2006, 2007, 2008. Does it make sense to keep XIRR of 2005, 2006, 2007 and 2008, or one XIRR for Oct’08 through 2008 suffices to keep my sanity since it’s annualized return. Your thoughts?

Thanks so much. I think your XIRR explanation is the best out there.

I’ve sent you a copy of an XIRR worksheet I used years ago that probably answers your questions.

1) I do format the actual XIRR cell as a percentage.

2) Exactly. It doesn’t even have to be chronological order (although mine is). Look at the example I sent you by email.

3) I do both. Again, look at my worksheet I sent you. Feel free to adapt it to your own use. You can make your own as complicated or as simple as you like.

Thanks for your prompt answer and I appreciate the worksheet. I’ll check it out at home. I’ll get back if I have more questions.

Re your response to my 3rd question, I tend to see the big picture of a matter at a very beginning, but as soon as I start ‘drilling’ I’m prone to losing the sight as if I’m seeing the trees only and not the forest anymore :o).

Hi,

Last few questions I hope (after a quick review of your personal spreadsheet). I noticed you had an MM account. Not sure it was included in the total portfolio column along with other investment or not, because I couldn’t tie totals by their dates. Anyway, you don’t really need to answer this, but just noticing the MM account on your spreadsheet, it got me contemplating what a person has to account for his/her CD’s/I-bonds/Saving accounts. They don’t really have transaction dates. So, all I have to do is enter their beginning values when a CD or I-bond was started (as a positive number?) and then their values at the end of the year as negative, correct?

Just now I came up with a situation.

Your article says “If you don’t reinvest dividends, those should be considered withdrawals from the account, just like fees paid from a separate account, should be considered contributions to the account.” So, with regards to taxable accounts, working people in accumulation phase pay yearly taxes on dividends, cap. gains, and interest from their working income, should those taxes be included as withdrawals on the total portfolio at the YE? Or is this going way too nitty gritty? It would involve preparation 1040 taxes twice: with passive income and without to learn the difference in taxes. After this I should be all set. Thanks so much.

I don’t try to include taxes because of the hassle but mostly because the vast majority of my portfolio is tax-protected. I tried it once a decade ago and it just wasn’t worth the hassle.

While collecting raw data of my Roth, I got another question. How to treat exchanges between funds? Technically I didn’t get money out, but it seems like a withdrawal to me.

If I wanted to XIRR each fund separately, shouldn’t I show Exchanges/transfers out of fund as withdrawals (negative numbers) and inflows as positive numbers into a new fund? If not, why?

If I wanted to XIRR my Roth as a whole pot, it seems that maybe it’s not necessary to show exchanges but a gut feeling says I should just to account for timing of money since you say it’s time AND money weighted annualized return. Thanks again.

Yes, and yes. I think you’ve got a good handle on it.

I calculated one account, but not sure how to interpret the result. The yearly statement from the TRPrice says 6.82% “Your account Return since 8/31/04” (through 12/31/12). I guessing the firm calculates time-weighted annualized return. My calculated XIRR says 7.35%. So, which result is correct or would it imply that they’re both right because the %-ages are pretty close? In my mind, if it’s ANNUALIZED return, than either I did 0.53% better or worse.

The above leads to my next question: how or where could I learn how SP500,Wilshire5000, etc. did for that period or any other chosen period of time for that matter? It wouldn’t really reflect a correct benchmark in this case, but still. This account consists of TRRDX and a portion of small stock trust.

Sorry to bug you will my questions, but I hope other readers can benefit as well.

The TR Price return is probably time-weighted. The XIRR return is dollar-weighted. I find dollar-weighted more useful and thus “more accurate.” When I want to compare returns, I usually use Vanguard’s index funds for that time period. A quicker way is to plug Vanguard’s funds into Yahoo or Google financial, but keep in mind those financial sites usually don’t count the dividends.

Hello, thanks for the informative post. I tried it out myself and it works.

Here’s a tip for Mint users: you can export your transactions from Mint into Excel (or Google spreadsheet which is what I use) and calculate the XIRR. In Mint, go to the “Transactions” tab and click each investment account on the left vertical menu, one by one, to view the transactions, and export them.

Mint outputs the transaction with the date, amount, a debit/credit called “Transaction “Type”, the fund, and some other information. Mint will label each transaction as a “debit” (sell) or “credit” (buy) in the Transaction Type field, and the “Amount” column will (in my experience) always be a positive number. So, to make the “buys” be positive and the “sells” be negative, what I did was create a new column called “Adjusted Amount” and make it a formula to multiply the amount by -1 if “sell” and leave the number alone if “buy”. For example, if Column E contains the debit/credit, and Column D has the amounts, then in your new “Adjusted Amount” column put this:

=if(E2 = “debit”, D2*-1, D2)

Also, Google has the same XIRR function but Google has you put the Amount range in the first parameter and the Date range in the second parameter, which is the opposite of how Excel does it.

So, after exporting all my Mint transactions, I manually added rows for my starting balances of my various accounts at the beginning of the date period (I started at 1/1/13) and the ending balances at the end of the period. The starting balances I indicated as a “credit” so they would be positive, and the end balances I indicated as a “debit” so they would be negative.

After doing this, I ran the XIRR function and it worked (after I realized and corrected the fact that Google and Excel do the parameters in reverse). At least I assume it worked. My overall portfolio is fairly close to a particular Vanguard target retirement date fund and I found that my calculated YTD return was very close to the Vanguard fund’s YTD return, so I think the number is right.

Actually, a correction to my post. I was wrong about the XIRR function and the parameter order. Both Google and Excel put the numbers first and the dates second. I was mistaken.

Hello Brother,

XIRR DOESN’T SEEM TO WORK. I TRIED THE SAME EXAMPLE AS OF THE SNAP SHOT OT GAVE ME AN ANSWER AS 0.115. CAN U PLEASE GUIDE ME.THANKS

You’re going to have to be a little more specific. Perhaps you have a typo. Try inputting exactly as instructed. It seems to have worked for many other people.

XIRR works fine with me when I have a positive annualized gain. But when I play around with the numbers to try to get a negative annualized gain, it gives me “#NUM!”. any reason for this? thanks,

Adam

I have found that at times you need to change the “guess”. Try -5% for it and it may work out for you.

I’ve been messing around a bit with the function and I was wondering if there is any way you can incorporate the inflation rate into the annualized return rate? In my mind it seems like you can look up the inflation rate of that particular year and subtract that amount from the second Dec 31st entry (positive number) and run the formula as described above. However, just wanted to see if this is possible or correct. Love the site, thanks!

What I do to correct for inflation is I first calculate my XIRR. Then I go to inflationdata.com, get the CPI-U numbers, and calculate out the inflation rate for that time period. Then I subtract it from the return. Voila-after inflation returns. If you just want a ball park figure, use 2% inflation for the recent past, and 3% for the long run.

Ok, I have done that for the annual returns, but is it possible for the annualized return rate? Or am I confused and that is what you mean above?

You can either figure it out as an annualized number or as an annual number. When I have done that exercise, I did it annualized. For example, if you wanted to know the rate of inflation from January 2005 to January 2014, you go here: http://inflationdata.com/Inflation/Inflation_Calculators/Cumulative_Inflation_Calculator.aspx and see that the cumulative inflation is 22.66%. Since that is a period of 9 years, you need to solve for X. 122.66=X^9 It comes out to about 1.023, or 2.3% inflation. Subtract that from your annualized return (XIRR). So if your XIRR was 8%, your after-inflation value is 5.7%.

Can you expand on the calculation to work out the inflation figure please?

I have a cumulative inflation figure of say 16.39% over the last 3 years and 2 months. How do I work out the annual?

I would have done (16.39/38)*12

This is a good place to use the “XIRR” calculation from Excel. Here are your values:

1/1/2010 100

3/1/2013 -116.39

Here’s the calculation: =XIRR(Q7:Q8,P7:P8,4%) = 4.91%

So the annualized inflation figure is 4.91% per year. Your proposed equation would have given 5.18%. Not too far off, but not accurate.

Brilliant thanks. I was just a bit confused when you mentioned “the cumulative inflation is 22.66%. Since that is a period of 9 years, you need to solve for X. 122.66=X^9 It comes out to about 1.023, or 2.3% inflation.”

I get what you’re saying now.

I took a bit of a shortcut. Probably should have XIRRed the inflation too.

Also, I assume you count employee match contributions as regular inflows, correct?

Depends. I could understand an argument either way. Depends on if you look at the match as part of your salary or part of your investment return.

I used to exclude matching funds because I liked seeing the huge number. At one point I even calculated what the difference in take-home pay was and used that instead. The returns were HUGE! But I found that at the end of the day it’s not very helpful because there’s no benchmark you can compare it to.

How to calculate single annualized return for a mixed period within 2 calender year say Oct 2013 to April 2014?

Just use XIRR. It doesn’t matter that you don’t start at the calendar year beginning.

Thanks for yet another awesome post!

Couple of Questions:

1) in your second example you had a more complicated use of XIRR for the partial year of 2007 (and also for the YTD 2011). Is this to show a time-weighted return? The example:

$5000 – 7/11/2007 (acct was opened with $5K on this date)

(5544) – 12/31/2007 (year-end value with no additional contributions)

Your example lists the 2007 return as 10.89% – but to keep the annual returns comparable isn’t it more appropriate to just use the XIRR function which shows 24.36%? Just checking to make sure I’m not missing something….

2) Do you try to also group returns by asset class or account? Or just 1 sheet per account + the grand total sheet? I’m wrestling with how to set this up for my use.

Thanks!

1) The 24.36% is the annualized return. The 10.89% is the actual return I earned. If it had been 12 months at that rate, it would have been 24%.

2) I don’t do it by account, nor asset class. I have each individual investment, plus the retirement/HSA/529 totals, plus the everything total. You can do whatever you want, of course.

Hello,

Thanks for creating such an easy to understand method for tracking investments. I think I have most of this figured out but I do have a couple of questions/clarifications.

1. Is there a formula to figure a better overall actual return for an investment that is started sometime during a year and the current YTD? For example say I started an investment on July 22, 2013 and I want to know what my actual return is as of the current day, say May 8, 2014. I know you could use a straight XIRR calculation, but it seems like there should be a more precise one as you use a different XIRR calculation to determine the total return for 2013 and YTD of 2014 due to those being less than one year. Doesn’t the same philosophy apply in this situation?

2. After setting up a spreadsheet for my different accounts and an overall total, I enjoy it so much I want to set one up for each individual investment within each of those accounts. My question is in regards to moving or rebalancing monies from one investment to another, but still within the same account (410(k)). Here is what I have based of my Quicken Transaction:

Transferred out 2.77 shares at $58.217, for a total of $100.12. Then there is a realized gain of $57.06 which between the two total $157.18.

I figure I need to account for the withdrawal of $100.12, but do I need to-and if so-how, figure in this realized gain for this individual investment when I am moving the monies from this individual investment to another within my 401(k)? And then if so how does this affect, if at all, the investment it was moved to?

Hope that makes sense, I’m new to all this.

Matt

After thinking about it I am pretty sure the answer to #2 is that I count the realized gains as a withdrawal correct?

1) Yes

2) No, you don’t need to figure the realized gain. The calculation figures all that out. All you need is money in and the date and money out and the date. It doesn’t affect the investment it was moved to.

Thank you for the quick response.

So if I wanted to figure a precise XIRR, for example, from the dates March 21, 2008 to May 10, 2014 would the formula look like this:

=SUM(((1+XIRR(D42:D181,E42:E181,10%))^(((DATE(2014,5,10)-(DATE(2008,3,21)))/2555))-1))

I figure you need to change the number of days from 365 to 2555 (number of days in 7 years, NOT figuring in leap years at this point) for this to work? Am I correct in this?

Thanks again,

Matt

XIRR is an annualized return. What kind of return are you trying to get? If you want an XIRR for those dates, use the simple formula:

=XIRR(D42:D181,E42:E181,10%

The only time I mess with the formula is if I want a YTD return or some period less than one year.

Bear with me as I am new to all this stuff and here is the way I am thinking about it:

If you adjust for YTD or holding an investment for a time shorter than a year, why would you not want to adjust for the “longer” picture as well?

For example, let’s say I have an investment that I have held since 9/10/09 and I want to know what my rate of return as of this point today, 5/10/14 is. If I did a simple XIRR calculation then I would come up with a percentage that could be considerably than using my “adjusted” formula. Using the basic XIRR calculation, does it not figure that I held this investment for an additional 252+129=381 days, which would skew the numbers as it would appear I have help this investment for an additional year?

I am probably totally of base here, so hopefully you can straighten me out!!

So you want the total return, not an annualized return, right? I’m not sure why, but I think you’re doing it the right way.

Hello, I have another question that I hope someone can help me with. I see that one should not include reinvested dividends as contributions. Would the same apply to fees? In my case, in my 401(k) plan, my account fees are paid by reducing my number of shares every so often. This is almost like a reverse dividend. Would I just leave those transactions out of my XIRR equation, since ultimately these losses will be reflected by the slightly lower ending balance (compared to what I would have had if those shares were not taken away). Thanks.

That’s correct, leave them out.

I just wanted to say thanks for this excellent tutorial and examples on using XIRR (much better than the Microsoft explanation of XIRR). I’ve been looking for a way to calculate CAGR when start and current value dates are varied (most other sites assume one always makes purchases only on Jan 1). I ran the XIRR on each of my stocks as well as my various portfolios. This is outstanding and exactly what I was looking for.

Thanks!!

Another day that I can say “I learned something new today.”

Thanks, WCI. Wish I could give you a big ol’ cyber ((hug)).

How do I figure this out using the XIRR function to calculate in excel?

Question:

Google common stock closed at $282.05 per share on 11/25/2008 and at $1,159.96 on 2/6/2014. Calculate the annual rate of return during this period on Google common stock.

-282.05

1159.9

Is the first column. Use the date functions in the second column. Then put in the XIRR function.

Hi, I’m going through this exercise for a taxable brokerage account. I was planning to do the following to adjust the return because of paying taxes: on December 31 of each year to ADD a contribution equivalent to marginal tax rate times the sum of ordinary dividends and capital gains (line 9a and 13 of IRS 1040 form). Taxes are not paid out of this account, yet that money is lost to taxes, and I care to know the after-tax performance. Is this correct, or is there a better way to do this? Thank you!

That sounds like a reasonable way to get an after-tax return, but remember to adjust tax rates for the various types of distributions- LTCG, STCG, qualified dividends, unqualified dividends etc.

I’ve got to grips with this pretty well and use it to calculate the annualised XIRR as a basic =XIRR(A1:A15,B1:B15,10%). I have the annualised returns for each year to show how it has performed on a yearly basis such as =SUM(((1+XIRR(A1:A3,B1:B3,10%))^(((DATE(2011,12,31)-(DATE(2011,7,8)))/365))-1))

However, what I am struggling with is to work out the total overall IRR since inception to date.

I use this: =SUM((1+XIRR(A1:A15,B1:B15))^((DATE(2014,10,30)-DATE(2011,7,8))/365)-1)

Assuming start date as 08th July 2011 and current value.

The problem I am encountering is this value is different to the IRR value posted on the site where I hold my funds. Mine came out as 12.31% and the sites came out at 15.31%.

However, each yearly annualised return matched up exactly, apart from the 1st year – mine calculated as -8.76% and the site reported -8.81%. Although I believe this isn’t related to the main issue above.

Any ideas what could be going on?

I’ve ran it on two other accounts and one matched up exactly and the other came out as 28.09% on the spreadsheet and 27.97% on the site.

I can’t explain why your brokerage firm can’t run XIRR, no. 🙂 I’m not sure why you’re running it the way you are to get an overall XIRR. That’s the easiest one of all to get. It annualizes itself, so it should just look like =xirr(A1:A15,B1:B15).

Lol I think you missed the point – they can, and do run IRR calculations – just haven’t told me how they do it (yet) – I’ve asked – let’s see what they come up with….

I suppose I was hoping you might spot something in my calculation that is incorrect.

I’m not after the annualised figure. I have this already, I learnt that from your lesson 101 right at the top.

I’m trying to work out the total performance of the investment since inception.

Say for example, between 8th of July 2011 to 30th October 2014 – the (overall) investment performance as calculated by the IRR is 28.00% – I can then compare this (if I so wish) with the relevant benchmark, or another portfolio I hold, over the same time period for comparisons.

Where this comes in useful is when rebalancing is taking place during the lifecycle of the portfolio. I can readily work out through a piece of software what my current or original portfolio mix has done over (say) the last 3 years but of course I might not have held the exact same mix of underlying assets for that time period.

If I can work out the IRR of my actual portfolio I can then compare this with the current portfolio mix – this could tell me if my rebalancing efforts have paid off or if I’d stuck to the same portfolio would I have been better off.

Ahh…I see. Honestly, I’ve never tried to use XIRR to get that. Guess I was more interested in the annualized number. Let me know if you figure it out.

I’ve figured it out, I think….

The site was using a start date of 06/10/2010 (I believe this was the date the account was opened)- however, no money was actually invested until 08/07/2011.

If I try to amend my spreadsheet to include a start date of 06/10/2010 and a value of £0.00 – it doesn’t work – the XIRR inputs 0.0%. If I put a starting value of £0.01 it matches the site – however this is incorrect as no money was actually invested.

If I change the start date on the site to the day the actual money was invested it matches the values produced on the spreadsheet.

I think the site needs to be able to work it out from the day the actual money was invested and not the day the account was opened.

Thanks, White Coat Investor. This was one of the cleanest explanations of XIRR that I’ve found.

I’ve incorporated it into my dividend portfolio tracker here: http://www.twoinvesting.com/2014/12/calculating-your-annualized-return-xirr-function/

Excellent blog, by the way. I’m a white coat investor as well. Radiology resident with about 1.5 years left including fellowship.

Thank you, I have been reading a number of articles about calculating personal returns and this is by far the most helpful, and accurate.

I have a question I hope you can answer. It is about the treatment of dividends. The dividends I receive are automatically re-invested into the security from which it was paid out. But it is very rare that 100% of the dividend is re-invested back into the security. To take an example, I may be paid a dividend of $100, and the price of the security at that time might be $80. In this case the dividend is automatically re-invested to buy one share at $80, leaving me with $20. I will keep this $20 as cash within the fund, and eventually I will use it to buy another security. My question is: how should I treat income like this? Should I treat the $20 as an outflow? Or ignore it like other dividend income given it is, eventually, re-invested? If I want to calculate a XIRR for each security, how would I treat the $20 then?

Many thanks for your help.

The $20 is a withdrawal/outflow with XIRR. It’s not in the fund, it’s in your sweep or money market account. So you withdrew it from the investment. I guess if that $20 never goes anywhere but back into the investment, you could ignore it, but realize it will likely lower your calculated returns since you’ll have a “cash drag” on the investment.

Thanks. Yes it can be ignored if it goes back into the investment – with the risk that there would be a cash drag effect for as long as the money sloshed about as cash and not invested. I think this works reasonably well when calculating the XIRR for my entire investment fund.

I also calculate the XIRR for each individual security within my investment fund. This is the bit I can’t figure out. I would need to treat the $20 as a withdrawal from the security, because I can’t guarantee the money would ever go back to that particular security. But what I can’t figure out is how to treat this accumulated cash under XIRR. When I do re-invest it into another security, I don’t want to treat it as new money going into the investment fund because it is not new money I am adding to the portfolio – it is proceeds of the investment. Any thoughts?

I have two columns. The first is my total investments. This $20 wouldn’t be a withdrawal from that. The second is a column for each investment, including a money market/sweep/cash account. So when you got $20, it would be a withdrawal from the investment and an addition to the cash account. Then when it is reinvested, it is a withdrawal from the cash account and an addition to whatever investment it is put into.

Hope that helps.

Yes, that is helpful. Thanks very much for the advice. Do you find that the two XIRR calculations for each column produce the same number? I suppose the only difference between the two is any “cash drag” suffered and not calculated under the first column.

Maybe I’m not clear. I should have said 3 columns. One overall column, then one for each investment including cash.

The true returns of any portfolio will include all cash flows and I have found the XIRR function in excel to be the best to calculate annualized returns.

Thanks for your clear explanation of the XIRR function. I have recorded my investments since 1st October 2014. For each investment made I record the cost as a negative value along with the date and for each investment sold I record the sale as a positive value along with the date. For dividends received I record as positives along with the date. At the foot I insert my current portfolio valuation and date, calculating the XIRR at that point. Should I be recording the dividends received in this fashion?

If dividends are reinvested, you don’t include them. If not, they are considered withdrawals from the account.

Thanks again, I see the point about dividends being accounted for in the end valuation (finally!). Of course using negatives for amounts invested and positives for sales and end valuation ends up with the same percentage for XIRR. I have always just preferred using negatives for money out!

Kind regards

I suppose it works either way. Now that I look at my spreadsheet, I’m using positives for contributions and negatives for withdrawals and end valuation. Sorry for misleading you!

Hi WCI,

I have been reading this: http://www.merriman.com/investing-101/performance-time-weighted-return-vs-internal-rate-of-return/

This seems to suggest that the TWR is a more meaningful measure than the IRR return. Particularly for periods greater than 3 months.

Can you please clarify why you prefer the IRR method over the TWR? or are we saying both are good to know as they both represent two different things?

TWR is interesting for the investment, not for the investor. You can only spend dollar weighted returns, i.e. the IRR, so that’s what I use.

Conversely, you can only spend what your investment has produced.

I think I’ll keep an eye on both.

A couple questions

1) should I include my employer contributions or contributions from a profit share in a 401k? I’m assuming the answer is “yes.”

2) how much would it effect the formula if instead of adding my contributions every two weeks (as in my 401k) I did it once every 3 or 4 months. Should it be the same regardless?

3) when figuring the per year returns in your second sheet, why are the formulas for the first year and the YTD so much different?

Thanks, love the blog!

1. I would.

2. When you add the contributions to the account, I’d put them in the spreadsheet to be most accurate.

3. Because that’s what it took to make them accurate I suppose. There may be another way to do it, of course. I’m open to suggestions if you know a better way.

Ok thanks.

And no, I don’t know a better way. I just had a hard time wrapping my head around those two formulas. Just wanting to understood better why I was typing in what I was typing in, as opposed to just copying and pasting your work.

Thanks!

Couple philosophical questions:

1) Last time I wrote you about this, you said you would include 401k employer match and profit-sharing in your XIRR. I agreed with you at the time. Now I’m not so sure. While they don’t represent “traditional” investment growth, both profit-sharing and employer matches DO represent growth to my account that I didn’t pay a dime for. One argument for contributing to 401k employer match instead of paying down debt is that it’s “free” money. Why consider it “free” when justifying using it, but consider it a contribution of your own when figuring your rate of return?

2) Any advice on how to navigate the psychological ups and downs that come with checking your investments on a regular basis? One thing that passive investing touts as a benefit and especially over at bogleheads is simplicity – both the investments and the amount of time you spend checking them. I’ve gone from checking my investments a few times a year to a couple times a month now that I’m trying to do my XIRR accurately, and it’s caused a bit of stress to see the numbers on a more regularly basis. How often do you check your investments, and what’s your advice for “ignoring” them when you do?

Thanks

1. I disagree. You did pay for those contributions in the form of a lower salary. It’s not a return, it’s a contribution. Not putting your money in the 401(k) is the equivalent of leaving some of your salary on the table. But I would include any employer match as a contribution on my XIRR form.

2. Don’t check. I look at the markets once a month when I do the monthly newsletter. I probably update my spreadsheet 6 times a year or so. I’ve become much more lax about keeping things carefully balanced. It’s not worth the effort. I just direct the new money where it needs to go.

I think I’m doing the formulas correctly in my spreadsheet, but I’m getting an XIRR of around 43% for my data going back to 2013. When breaking it down year-by-year my return in 2014 was about 213%, which may explain the high XIRR. Nearly all of the increase in my end-of-year account balances were from new contributions though (wife and I just started investing for retirement at that time, maxed out our Roth IRAs), so can I still use this to interpret my returns?

My returns for the other years, while still making contributions to the Roths and our 403b’s:

2013 +6.9%

2015 -0.5%

2016 YTD +14.6%

Are you including all your contributions in your formula? 213% is an awfully good return. I bet you made an error inputting something there, maybe forgot a contribution or something.

I’ll just e-mail the spreadsheet to you to look at.

I have always just accepted the number given to me by Vanguard under the “Personal Performance” link. Is that number incorrect? What do additional information does XIRR give that Vanguard does not?

I don’t know if Vanguard’s number is accurate or not, but it doesn’t work for me because lots of my assets aren’t at Vanguard.

Newbie here. Just went through the Excel XIRR calculations for portfolio of about 8-9 years ago and Vanguard’s personal performance value correlates!

Just went through XIRR and it correlates with Vanguard’s personal performance.

I have a question regarding the XIRR and the MIRR formulas. It looks like when I change the percentage rate number, the end result does not change. I must be mis-understanding what this number means or how it applies to the formula. My understanding was that this was the percentage rate/borrowing rate that the initial monies were borrowed at. (FYI, changing the reinvestment rate in the MIRR formula changes the outcome). This should change the results if this number changes. Can you clarify what this number means and why it is included in the formula if it is not necessary for calculation? Thank you for your help.

The rate is just an estimate. It doesn’t actually go into the formula. A lot of times (most of the time) you can leave it out and it calculates just fine.

So to make sure that I understand, if we are looking at a real estate investment, the net cashflow (which is used in the equation) for the period should already include the payment on the loan, so the interest expense has already been deducted, thus rendering the original borrowing rate un-needed. Correct? I still think it is confusing that they would include it in the equation, but as long as I am understanding correctly.

Whereas in the MIRR formula, a “re-investment” rate is defined and that does enter the equation. Correct.

One last question, assuming the above understandings are correct, have you ever heard of the XMIRR Formula? Do you know how I can get my excel to recognize it? Seems to be what I really want.

Honestly I’ve never used MIRR or XMIRR. The rate in XIRR is an estimate of the rate of return and has nothing to do with the rate on your loan. All XIRR includes is the cash flows in and out and the beginning and end value of the investment.

My compliments on the continuing value of this blog. I read it frequently, but think I missed this post. Just clicked on a link from somewhere else to this post and the comments / example XIRR. Been investing in RE for 15+ years and always ball parked IRR bc I could not master how IRR really works.

This entry and comment feed explained it all for me!

Thank you!

I am hoping for confirmation on the correct formula on Security Level Inception to Date Annualized ROR. Do you use the Portfolio Inception Date or, if the security is purchased into the portfolio later, do you use the first trade date of the security in that account, even if the account moves completely in and out of the security over time, and even owns it again at a later time?

I don’t know wha tyou mean by “security level inception to date annualized rate of return.” But if I were going to calculate the return on an account, I would count the inflows and outflows to the account. If I was going to calculate the return on a security, I would count the inflows and outflows of that security. XIRR can handle you selling completely out of a security and still give you your return on that particular security over the years.

How do you calculate returns of private real estate deals. I am investing a dollar amount and then getting quarterly returns. Is my YE total my total contributions minus that years return? Thanks for e/t…big fan!!

Same way as anything else. Use XIRR. The initial investment is a positive number. Put that next to the date. Each dividend paid is money out, or a negative number. Put the date for each of those next to that. The amount the investment is worth is a negative number at the bottom with today’s date. This is what it looks like for one of my real estate investments:

$5,000.00 7/9/15

-$144.37 10/8/15

-$5,000.00 12/31/15

$5,000.00 12/31/15

-$168.75 1/7/16

-$168.75 4/8/16

-$168.75 7/13/16

-$168.75 10/17/16

-$5,000.00 12/31/16

$5,000.00 12/31/16

-$168.75 1/17/17

-$5,000.00 03/07/17

Then run the XIRR function on that: =XIRR(BS35:BS234,BT35:BT234,10%) = 12.50%

That’s an annualized return.

I like using MarketXLS for this. It’s great for me.

I’m trying to figure out the significance of reporting returns this way. In your spreadsheet, you calculate an XIRR of 9.75%. But, the CAGR required to get the end value of $15,038.68 (assuming all negatives in your spreadsheet are cash balances and/or “withdrawals”) is 7.09% (7.08671941596% to be exact).

Why is there a discrepancy and what is the significance of the XIRR in this example?

I suspect you (or I, but probably you given how long this post has been up without anyone pointing it out) have a math error somewhere.

The XIRR is the CAGR.

Great example. Thanks for sharing.

Question regarding cash flows…

In regards to individual stocks are the purchases and sells the cash flows or should I use account value (shares * share price)?

Great article and chain of questions and answers. I get an error when I try to download the two examples near the top. Can you make your spreadsheet available or email me a copy please?

[email protected]

There is nothing to download in the post. The examples are available right in the spreadsheet integrated into the page. You can cut and paste it directly into Excel and play around with it if you like.

Also keep in mind XIRR function is not very reliable. There is a known bug which can (and does) produce more than one answer, but you will never know it unless you start debugging the calculation itself. In the example WCI posted above, it shows the investment compounding at over 9% annually. In reality, if you shoved that money into an investment each year, this money compounds at more like 7% annually by the final year. As long as you know the beginning and ending values, and the portfolio value BEFORE adding more cash flow, you can see the gimmick of XIRR fall apart.

Does anyone compare their individual results to the major benchmarks (S&P, Russell, etc)? If so, do you use the CAGR? Do you find an annualized return with dividends re-invested? I’m not sure exactly what annualized return figure of the S&P, Russell, etc to use since you get several different results when searching online. I’ve found that these can vary significantly (for example when you search for annualized return for S&P during the year 2011 you’ll find values that range from -3% to 2%).

I’m mostly interested in doing this to see how my performance compares to the S&P. If it’s close (or worse), then I’m considering changing my strategy to investing nearly 100% of my portfolio in an S&P ETF and calling it a day.

Thanks!

If you’re comparing, then be sure to include dividends and use annualized returns for both your returns and those of whatever benchmark you feel is appropriate.