# How to Calculate XIRR for Annualized Returns

Pick the best value stocks with our Stock Ranks, screening and valuation tool. Try the live demo today.

Written by

Jae Jun

follow me on

Facebook

Twitter

To get this kind of information and other exclusive articles before regular readers, get on the VIP Mailing List today.

********

What You Will Learn

• How to use XIRR to easily calculate annualized returns
• How to use the free spreadsheet to calculate annualized returns

#### Calculate XIRR Fast and Easy

The current portfolio tracking spreadsheet has its shortcomings as it doesn’t include non invested cash when calculating returns.

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.

If calculating returns was as simple as taking the beginning balance and ending balance and then calculating the absolute return, tracking investment returns would be so much easier.

But there is time value in money and once you start depositing or withdrawing cash and receiving dividends, it makes calculating annualized returns that much more difficult.

Investing \$1,000 in January certainly is much different to investing \$1,000 in December, before the year end.

That’s where the XIRR feature in Excel comes in.

#### Using XIRR to Calculate Annualized Returns

With XIRR you can calculate annualized returns even when cash flow for your account is irregular.

As an example, the starting balance is \$10,000 with regular deposits and some gains totaling a portfolio balance of \$15,000 on Jun 27 2010.

At first glance, without taking into account the cash deposits, you could be fooled into thinking the return is 50%. However, if you use the XIRR function, the calculated annualized return is much less at 28.8%.

As you can see, if the cash flows become longer and irregular with different cash flows, the calculation by hand becomes virtually impossible.

Before we get deep into the topic, just click on the image below to get my Piotroski Score spreadsheet. You’ll also get exclusive content and resources we don’t share anywhere else.

#### Free Spreadsheet to Calculate Annualized and Cumulative Returns

Now you can calculate your returns quickly and easily with this free spreadsheet to calculate your portfolio returns.

Enter your beginning balance at the very top with deposits as positive values and withdrawals as negative values.

Enter the date next to each corresponding cash flow and if you need to calculate the return for any particular date, enter it into the third column.

Just make sure the ending balance is negative with a date to prevent errors.

The results will then look like the image below.

Full credit goes to a site called Gummy Stuff that is unfortunately no longer online for giving me this idea.

#### To Calculate XIRR easily, Download the Portfolio Return XIRR Spreadsheet

• No special plug ins required.
• Just enable macros and run.

To get this kind of information and other exclusive articles before regular readers, get on the VIP Mailing List today.

********

### What is Old School Value?

Old School Value is a suite of value investing tools designed to fatten your portfolio by identifying what stocks to buy and sell.

It is a stock grader, value screener, and valuation tools for the busy investor designed to help you pick stocks 4x faster.

Check out the live preview of AMZN, MSFT, BAC, AAPL and FB.

### 16 responses to “How to Calculate XIRR for Annualized Returns”

1. Jacob Chakola says:

In the downloaded files, cannot figureout the excel file.It contains 4 folders,(-rels,docprops,xl,content types), using excel 2007 version

2. Paul says:

Gummy Stuff is still around.
http://www.financialwebring.org/gummy-stuff/gummy_stuff.htm

3. Jae Jun says:

@ Jacob,
If you look at the download link, it is an XLSM file, not a zip. Rename the zip to .XLSM

It’s a windows thing.

@Paul,
Thanks!

4. Saw it on Fat Pitch Financials as well. One quick question: if we split XIRR by the number of days in a year and then multiply by the number of days past from this year, do we get a number that we can compare to the YTD return of mutual/hedge funds/indexes?

Thanks!

5. Jacob says:

Get it right.
Thanking you

6. Jae Jun says:

@ David,

No that won’t work. What you are asking is much more complex and requires a different formula which is on the spreadsheet, but is hidden in the free version.

1. Andy says:

If you stopped trading after Jun 30, 2010, your annualized return should be 50% by 12/31/2010. But the XIRR shows only 6.20% (I changed the start date to 1/1/2010 with \$10,000 in balance.) Why?

2. Jae Jun says:

@ Andy,
It’s because portfolio returns should not be calculated on absolute returns. That’s what I used to do but it’s not a good way. Plus if you deposited money inbetween, you really didn’t earn 50%.

You and I could drive a total distance of 500miles from A to B.
But if you did it in 5 hours while it took me 10 hours, there is clearly a difference in performance.

3. Assaf Nathan says:

Hello,

I think you got it wrong. XIRR is used to calculate internal return, and it uses “guesses” to guess the return.

There is no replacement to straight-forward calculations.

If you intend to do money management, you cannot use this function.

Let me give you an example:

I have 1000\$ to invest from a first investor that i’ll manage the money to.
I invest the money in January 1st, and lose 99% up to 31st of June.
So I’m left with 10\$ out of the initial 1000\$.
Now I get another deposit of 1000\$ from another investor.
Till December 31st, i do 100% on the portfolio and end up with 2000\$.
Xirr will show you 0% internal return, while your return us actually
(-98%) (minus 98) for the year.

XIRR does not take into account fluctuations in the inrerest rate of the principal, it takes a constant rate of return.

If you will use XIRR for latter example, you are creating some kind of a “ponzi” scheme in which the second investor paid for the first’s losses.

Thanks
Assaf

4. Assaf Nathan says:

Hello,

I think you got it wrong. XIRR is used to calculate internal return, and it uses “guesses” to guess the return.

There is no replacement to straight-forward calculations.

If you intend to do money management, you cannot use this function.

Let me give you an example:

I have 1000\$ to invest from a first investor that i’ll manage the money to.
I invest the money in January 1st, and lose 99% up to 31st of June.
So I’m left with 10\$ out of the initial 1000\$.
Now I get another deposit of 1000\$ from another investor.
Till December 31st, i do 100% on the portfolio and end up with 2000\$.
Xirr will show you 0% internal return, while your return us actually
(-98%) (minus 98) for the year.

XIRR does not take into account fluctuations in the inrerest rate of the principal, nor fluctuations in the principal itself. it takes a constant rate of return.

If you will use XIRR for latter example, you are creating some kind of a “ponzi” scheme in which the second investor paid for the first’s losses.

Thanks
Assaf

5. Assaf Nathan says:

In other words:
To calculate your return, you must calculate the return on a CONSTANT principal.

If you get some kind of a cash movement (withdrawal or deposit) you MUST freeze – calculate the return before the transaction, record it, and start fresh with the new principal.
You can do it on a daily basis, monthly, yearly, or combined, as long as you calculate every period in which there was no money movement independently.

Later, you just multiply the yields to generate a yearly yield.

For instance, in my previous example, you multiply
-99% by 100% and get:
(1+(-0.99))*(1+1.00) – 1 = -0.98 = -0.98%

You can also do this calculation for 1 day, then 1 month, 6 months, 1 day again, etc, till you complete a year. Your yearly yield will be the multiplication.

Unfortunately, this is the ONLY way to calculate returns on a portfolio with money that cones in and out – investments and withdrawals in case of a fund or expenses and salaries in case of a personal portfolio.

Using XIRR will generate erratic numbers in ALL cases, the error magnitude depends on the magnitude of cashflows comparing to the principal and on the volatility of your portfolio.

But then again, do you really want errors when you measure your results? 🙂
Just dont use XIRR and use the plain pencil and notebook method, just like Warren Buffett did.

6. Jae Jun says:

Hi Assaf,

Thanks for the heads up and advice. I’ll look into it again.
But regarding XIRR, since most people only need to measure one portfolio, XIRR is a perfectly fine shorthand method of automating all the calculations.

But you are right about the calculations when multiple accounts and transactions are involved. There must be a quicker and easier way though.

7. Derek E says:

If I want to compare my returns versus the market stated returns, or versus a mutual funds stated return for a given year is this the way to do my calculations?

8. Financial Guy says:

Excel XIRR function screw up sometimes when the sum of cash flow is negative or the annualized return result is negative. I found a handful app in Apple App Store called “Universal Investment Return Calculator”. It can calculate like XIRR function does but it is more robust (i.e. it doesn’t screw up with negative results) and it does not need you to feed a guessed result.

9. Connelly Barnes says:

From the perspective of a private investor who made both of these investments, XIRR calculates exactly the right return since the net profit is \$0 and so the internal return is 0%. You’re right that funds often use other metrics such as Modified Dietz method.

10. Spot on. If you run a fund, then this won’t work because you have different deposit dates. For individuals, it is 100% satisfactory.