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.

Pingback: Designing a doctor's portfolio- Part 7 | The White Coat Investor- Investing And Personal Finance Information For Physicians, Dentists, Residents, Students, And Other Highly-Educated Busy Professionals

Pingback: A Discussion of the White Coat Investor's Personal Portfolio | The White Coat Investor- Investing And Personal Finance Information For Physicians, Dentists, Residents, Students, And Other Highly-Educated Busy Professionals

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

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.

Pingback: Calculating Your Annualized Return – XIRR Function - Two Investing

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!