MBA Monday – Really Cool Equation: Internal Rate of Return

I thought I’d start up the MBA Monday series again (NPV, Accounting, Investing and all sorts of other goodies) to bring you a really cool calculation you can do in excel that you may not have heard of. It’s called Internal Rate of Return. It’s important because it provides you with an investment return when the inputs aren’t simple to calculate.

How Internal Rate of Return Works:

A simple hypothetical investment like buying Apple stock at $250 and selling it a year later at $500 is simple. Apple pays no dividend and you doubled your money, so it’s a 100% return (annualized).

• But what if you bought $1000 worth of Apple stock every quarter for the past 3 years and sold it all tomorrow?
• Or what if you bought a high yielding stock that’s been paying dividends monthly?
• Or what about a real estate investment where you take distributions once a year and then sell it 10 years later?

How the heck do you figure out the annualized return then? You can’t do it with a simple pencil and paper. This is where the Internal Rate of Return comes into play.

Internal Rate of Return Example – Real Estate Purchase:

I highlighted previously how I invested in college real estate (all the details here) with $50,000 borrowed from my 401(k). Let’s make some basic assumptions for example purposes and you’ll see these reflected in the spreadsheet below. We assume I invested $50,000 in year 1 and then started taking distributions in future years which increase by $1,000 each year due to rent increases. We further assume that at the end of 10 years, net of closing costs, my stake is worth $75,000 due to principal paydown and perhaps some slight capital appreciation. For now, we’ll ignore all the tax benefits like deductions, depreciation, etc. along the way. Without the IRR function, I’d have no way of figuring out my actual return across the 10 year investment horizon. But here it is!

(click to enlarge)

So, to get my answer, is used the “XIRR” function, and captured the Date and Total column and also enter a “guess”. This is required for more complex situations where there could be multiple solutions. We don’t have to worry about that for my example, so I just entered “.15” or “.19” or whatever, representing 15%, 19% guesses (which yield the same answer by the way).  Don’t stress about the guess entry when doing simply calculations like this.

As you can see, this investment shows a pretty nice return at 23.4% annualized!

It’s hypothetical, and as you’ll see in my followup real estate post later this week, we already have some challenges, but the key here was to share this tool and how it can be used to calculate annualized returns for more complex situations.

Where Else Can You Use Internal Rate of Return?

• 401(k), IRA and more – Some retirement plan providers will provide you with an actual investment return of various time periods but others won’t. I find mine to be annoying since it only gives a “year to date”, but not prior year performance. Since plans like this often have monthly inflows coming in, you’d need to understand what your annualized return is with investments across various time periods. The way to do it is with XIRR.
• Dividend Reinvestment Programs – Similarly, if you’re buying stock partial shares, reinvesting dividends, etc. on a recurring basis, you can’t figure out your return without using the XIRR function.

Did You Find the Internal Rate of Return to be Useful?