How To Calculate IRR in Excel

The PropertyClub Team
Jul 7th 2020
Calculating the internal rate of return manually can be a headache. Luckily, there's a function to calculate IRR in Excel, and in this short guide, we'll go over exactly how you can do that.

Trying to calculate IRR manually can be off-putting, especially if mathematics isn’t your strong suit. But luckily, there’s a function in excel that is designed to calculate the Internal Rate of Return of investment. The IRR is calculated as a percentage that is expected from an investment, which allows businesses or companies to determine which projects might be worth investing money in. Typically speaking, you will want to select projects where the IRR is higher than the cost of capital (that is the money initially invested in the project). 

To calculate the IRR using a math formula, you need to calculate the NPV for a series of cash flow amounts, guessing the interest rates using trial and error. Thankfully, Excel has an IRR function that can allow you to efficiently perform the necessary equation. 

IRR in Excel

Excel uses three different functions to determine IRR. These are IRR, XIRR, and MIRR. To use the formulas, simply select the “accounting” drop-down menu shown at the top of your screen and search for the formula that you want to use. Excel will then prompt you to enter in the necessary values and generate a calculation. When calculating IRR in Excel,  you should first determine which of the three formulas you will need to use to get the most accurate result. Below is a breakdown of when to use which formula.

IRR Formulas in Excel

  1. IRR calculates the rate of return for cash flows assuming equal size payment periods. In the example shown, the IRR is 9%. However, this result will be less accurate, as some months have more than 30 days, and some have less. In our example, the IRR is 9% 
  2. XIRR calculates a rate of return that takes into consideration different size time periods, which makes it more accurate. To use the XIRR function, you need to plug in both the cash flow amounts and the dates on which those amounts were paid (as shown in the example). Here, we get an XIRR of 10%
  3. MIRR stands for Modified Rate of Return works similarly to the IRR function except that it also allows you to calculate the financing and reinvestment rate, which usually results in a rate that is significantly different from IRR and XIRR, which is shown in our example where the MIRR is 14%

 

To use IRR in Excel, you will need to have at least one positive and one negative number for cash flow. The negative number is considered the initial investment in the project. So, in our example, we invested a total of $250,000. 

When using the MIRR or IRR functions, you will need to multiply the results by twelve to show what the yield would come out to annually. However, the XIRR function will automatically generate an annual yield, meaning you do not need to multiply your results. 

Excel allows you to quickly calculate IRR, so long as you have numbers to enter in as cash flow, you should be able to determine your Internal Rate of Return, which in turn will give you some idea about the potential of an investment. It’s useful to note that the more information and data you have, the easier and more accurate your calculations will tend to be.

There are also options for online calculators if you are unsure about using Excel to calculate IRR. While these calculators can work, they do tend to be a little less accurate than entering your information into Excel. And the exact algorithms used are not always clear either. Since Excel has the ability to calculate using three different functions and allows you to enter as much data as you need and play with the numbers, it’s likely to yield a much more accurate result than other online calculators will.