Internal Rate of Return Metrics
On today’s show we’re talking about how to measure the financial merit of an investment. How do you compare two investments that pay the investor according to differing formulas? Some investments pay greater cash flow. Others have more aggressive loan principal pay down schedules. Others still are creating equity through forced appreciation. We’re not going to even touch the topic of risk, since that’s an entirely other subject. So how do you compare these dissimilar investments?
Yesterday we talked about the equity multiple as a metric for evaluating the merits of an investment. It’s a simple metric to calculate and involves adding up all the cash flow from an investment and dividing by the initial investment. It has the major drawback that it neglects time. Getting a 3x equity multiple in one year is clearly a better investment than one that takes 50 years to achieve a 3x multiple. Perhaps a rate of return calculation would be more meaningful. But here too, there are multiple calculations that you can perform. The most comprehensive is the internal rate of return.
The simplest investment to understand is a fixed income security. Think of a certificate of deposit with your bank. You put in $100. A year later the bank allows you to redeem the certificate and you get $103 back. The annualized rate of return is a simple 3%. The math to calculate the rate of return for that simple cash flow is about as simple as it gets.
But if you have a real estate investment that is appreciating 2% per year, with 4:1 bank leverage, paying out a 5% preferred return, and has a one-time forced appreciation of 30% in value in year 2 of the investment which you predict to hold for 5 years. What’s the rate of return now? If your head is spinning, you’re probably not alone.
Enter the Internal Rate of return metric.
Calculating the internal rate of return involves quite a bit of complex math. It’s a time value of money calculation for a stream of cash flows over the life of the investment. Payments happening now are considered to be worth more than payments in the future.
For the pure mathematicians in the audience, I’m not going to go into all the math that the internal rate of return calculation entails, nor the related net present value calculation. I can assure I’ve done all those calculations many times as part of my engineering degree.
Fortunately, most spreadsheet programs including Google Sheets, Microsoft Excel, and Apple Numbers have a built in function that calculates the internal rate of return without requiring you to do a ton of math.
The IRR function assumes that you have a stream of cash flows that occur on a regular schedule. So for example if you expect regular monthly payments from a project, the IRR function can handle that with no problem. The payment amount can vary each month, and as long as the time element remains regular. If you have a month with zero cash flow, that’s no problem. If you have a month of negative cash flow, that’s no problem. If you have a large lump sum payment upon the sale of an asset or a cash distribution in the middle as the result of a refinance, that’s no problem. The IRR function is one of the most powerful tools for measuring financial rates of return.
In order to assist in that process, I’ve created a simple Excel tutorial which you can get for free. Simply send me an email to firstname.lastname@example.org with the word IRR, just three letters in the subject line. I’ll send you a copy of the Excel file and a short two minute youtube video that walks you through the Excel file.