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.

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.

So in this example, for the -$5,000 at the end – you say that the last number is “what the investment is worth.” Do you mean what your share is worth in a private real estate deal? What if it is sold at the exit for a large return – is the original amount paid for the share what you put as your last number, or is the new sales price what you should put?

And to take that a step further, how would this work for direct real estate calculations? Put your downpayment/money-in as the first and last number, and make monthly cash flows the “withdraws.” Where do you factor equity pay down into this?

These may not be simple questions, but I’ve had a hard time finding good answers to them online.

The money you receive at the end is the last number. The equity pay down is included in that. It’s just cash flows in and cash flows out.

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.