Computerized Investing > January 20, 2018

Calculating a Portfolio’s Internal Rate of Return

| | | COMMENTS (7) | A A   Reset

by Matthew Bajkowski

This article makes use of CI’s Portfolio Tracker on Google Sheets. To access this spreadsheet, click here. You will need to make a copy of the spreadsheet for your own use. Click File and choose “Make a copy,” then determine what to name the file and where to save it on your Google Drive. Do not send a “request for edit access” using the View button. For more information on setting up the CI Portfolio Tracker, read CI’s original article on using Google Sheets and the update to that article published in 2017.

Every investor wants to succeed, and every investor needs to know how they are progressing toward their definition of success. Regardless of individual strategy, tracking portfolio performance is one of the tools to measuring success. It’s important to know how realistic your investment goals are compared to your portfolio’s actual performance.

There are many ways to calculate overall portfolio performance, and, on top of that, many different names for similar-looking calculations. This article will deal with finding a portfolio’s realized rate of return, measured by the internal rate of return (IRR) calculation.

The IRR is your personal return and is commonly referred to as the “dollar-weighted return” or “money-weighted return.” It will differ from an exchange-traded fund (ETF) or mutual fund’s return listed on their website. IRR differs from time-weighted rates of return (TWRR) in that it accounts for investors’ behavior by taking into account the impact of flows (withdrawals and deposits) on performance. Its accuracy comes from its ability to track compounding value over time—each change in the value of the portfolio is accounted for.

This is best illustrated with an example: Two years ago, a portfolio was started with $1,000. One year ago, $500 was withdrawn. Today, the investment is worth $1,000. What is the internal rate of return?

A simple calculation would subtract the final value from the beginning value and divide by the beginning amount [($1,000 – 1,000) ÷ $1,000 = $0], providing a rate of return of zero. But this simple procedure ignores the $500 cash flow (withdrawal) occurring in the middle. The internal rate of turn for this portfolio is actually 28%.

A series of articles published in Computerized Investing in the 1980s showed how to calculate a portfolio’s IRR by writing a program in Lotus 1-2-3 and Microsoft Multiplan. In the 1980s articles, arriving at an IRR required writing program macros. Now, Excel and Google Sheets do all the hard work: Calculating a portfolio’s IRR is as simple as taking the time to organize a portfolio tracking spreadsheet. We give you a template with the new IRR tab in the CI Portfolio Tracker on Google Sheets. The setup for calculating an IRR is simple, and all the required data comes from a brokerage account’s transactions record.

What the IRR function actually does is find the rate of return that reduces the net present value (NPV) of a series of cash flows to zero. It answers the question “what discount rate would be used in order to make cash flows from each time period equal to zero at the beginning of the time period?” IRR is very popular in corporate finance; it is used to compare two projects. Generally, the higher a project’s IRR, the more desirable it is to undertake the project. To understand the concept of future cash flows and discount rates, looking up “discounted cash flow” analysis can be helpful.

The Spreadsheet

To access CI’s Portfolio Tracker with Google Sheets, click here. To save the spreadsheet for your use, click on File > Make a copy. (Do not click on View Only.)

In Excel and Google Sheets, the actual function in use is called the XIRR. The XIRR function is able to calculate a realized rate of return for an irregular series of events. Entries don’t have to take place at regularly scheduled intervals, which the first IRR calculations needed. The IRR tab in the spreadsheet has a small table that is filled out as an example (Figure 1).


 

The table on the IRR tab needs three things:

  • The date of a transaction,
  • A description of that transaction and
  • The transaction’s value (positive or negative).

These are all items that come directly from a brokerage account’s transaction record. The IRR equation itself only needs the date of a transaction and that transaction’s value—the description is for added clarity as to what happens in the portfolio.

The first entry in the table is the initial portfolio value, which should be a positive number, like a deposit into a savings account. The portfolio’s initial value can be its value at any given point in the past: For a five-year IRR, the portfolio’s “initial” value would be the total value five years ago.

Each subsequent deposit into the portfolio is a positive value, too. Dividends and interest are added to a portfolio’s cash value if they are not part of an automatic reinvestment program. This means both dividends and interest payments are accounted for in the final portfolio value provided by your brokerage statement and don’t have to be separately added to the table as deposits into the portfolio. Any withdrawals from the portfolio should be entered into the table as negative values.

The last entry should be the current portfolio value, and it should always be entered as a negative value. This represents the final portfolio value if all the holdings were liquidated and all the cash was removed from the account. Also, the IRR function itself requires at least one positive value and one negative value because it is trying to find the figure between the initial portfolio value and the current portfolio value that makes the NPV zero.

The Excel XIRR function consists of three inputs:

  • First: the portfolio’s list of transactions (required),
  • Second: the corresponding entry date for each transaction (required) and
  • Third: the estimated rate of return or guess (optional).

These inputs are put into the XIRR function in the following order:

XIRR(transactions, transaction dates, [estimated rate of return])

Both the transactions and their dates are entered as a range of cells. The estimated rate of return is just a number entered into the equation. Filled out, the XIRR function would look something like this:

XIRR(E5:E12, G5:G12, 0%)

Figure 2 shows the arrangement of the XIRR function as it appears in Google Sheets, with an additional explanation of each input. In Google Sheets, the transactions are called cashflow_amounts, the transaction dates are called cashflow_dates and the rate of return guess is called the rate_guess.


 

The estimated rate of return, or guess, isn’t that important; in most cases it won’t need to be entered because it will be zero. However, in some situations, there may be no single, unique rate of return, as when the series of transactions contains two or more sign changes. For example, for a portfolio with transactions of 100, –300 and 200, each one year apart, both 0% and 100% solve the function as IRRs:

0%          100 × (1+0)2 – 300 × (1 + 0)1 + 200 × (1 + 0)0 = 0

100%      100 × (1 + 1)2 – 300 × (1 + 1)1 + 200 × (1 + 1)0 = 0

With Excel and Google Sheets, this shouldn’t be a problem, but it is good to be aware of why the guess figure is an input for the function. If you are adjusting the table to fit more transactions, make sure the function is relevantly adjusted to encompass all the data ranges, too. The IRR will appear in the box at the bottom of the table, labelled “IRR.”

Reminders:

  • When entering the cash flow values the value that is dated at the end must be negative.

  • The cash flow values can be listed in any chronological order because the corresponding date is provided.

  • If you don’t have at least one positive and one negative cash flow value, the XIRR function will return an error symbol.

  • If you provide an invalid date in the date parameter, the XIRR function will return an error symbol.

  • Google Sheets tries to recalculate the XIRR until the result is accurate.

Conclusion

Measuring the performance of a total portfolio helps assess the accuracy of an individual investor’s long-term assumptions toward asset allocation and also allows for the adjustment of success goals. While knowing a portfolio’s IRR will help inform decisions, don’t overanalyze. Don’t let short-term results or changes to an IRR morph into hasty judgements. Keep in mind you are assessing a long-term strategy.


Discussion

Stan Haley from AZ posted 4 months ago:

Mathew: Are reinvested dividends handled differently than cash dividend payments?


Jared Bessert from WI posted 4 months ago:

When you stopped printing CI I stopped reading it. I've read a few selected online articles that had a link to download a PDF. I then printed and read the article.

Now, it appears you do not provide a PDF download or print feature. Will you bring these printing and PDF output features back?

-- Jared Bessert


Timothy Trowbridge from CO posted 4 months ago:

Jared -
Try this ...
Right click anywhere and select "Print"
Then select "Microsoft Print to PDF"
It's not as clean because you get the ads and such

Tim


Senthil from CA posted 4 months ago:

Thank you for the updated sheet.

If this workbook can be extended to track the closed sales that may summarize the FIFO logic to track the closed security performance that would be awesome!

I understand the current value does reflect the net change to the portfolio and get reflected in IRR.

Thank you


Jared Bessert from WI posted 3 months ago:

Tim,

Thanx for your idea about right clicking, but it did not work. The popup menu I saw had "save page, save page as pocket, send page to device, etc.". The only "Print" comment is off the top-line "File" menu.

Any other suggestion?

-- Jared


Jared Bessert from WI posted 3 months ago:

Tim,

Sorry, I didn't know your suggestion was browser specific. Your suggestion did not work in Firefox or IE, but it did work in Chrome.

Unfortunately, the Chrome pdf is rather ugly compared to the PDFs that AAII provided in the past.

-- Jared


David Bryson from SC posted 1 day ago:

How does the IRR returned from this calculation correspond to APR as seen on banking accounts?


You need to log in as a registered AAII user before commenting.
Create an account

Log In