How To Calculate IRR In Excel

If you’re looking to calculate the internal rate of return, but aren't sure how in Excel, here are three methods that are simple to incorporate.

Kevin Pocock

Last Updated on September 2, 2022

IRR or the Internal Rate of Return calculates a series of cash flows. This is assuming there are equal-sized periods of payment.

Today we’ll look at how to calculate IRR and how to interpret the results.

How To Calculate IRR

There are three ways to calculate IRR. We recommend using formulas as doing manual calculations can incur human error and be time-consuming.

This is why you are using Excel in the first place so you may as well make use of its functionality!

Using Excel, the formulas you will be using are IRR, XIRR, and MIRR.

In our example, the first column represents Year, the second column represents the Date Of Payment, and the third column represents Payment.

Starting with IRR and using our sample table from above, the IRR formula you would use would be “=IRR((C2:C10,.1)*12,” which will give an internal rate of return.

In certain examples where the period of time is not going to be equal, i.e. when you use months in which some have 31 days, whilst others have 30 days or less, the monthly periods are not going to be exactly the same in length.

This means that the IRR is going to return a somewhat incorrect result. Add this over multiple months, i.e. a year and you can expect this to be consistent.

If we look at Excel’s XIRR function, we will be able to calculate a more accurate rate of return. This is because it will take into consideration a different sized time period.

In order to use this functionality, you will need to apply a cash flow amount and a specific date on which those payments are going to be made. Hence our second column is; Date Of Payment.

So in our example from above, if we wanted to calculate using XIRR we would use “=XIRR(C2:C10,B2:B10,.1)” which would yield an internal rate of return.

Finally, Excel’s IRR function, which stands for modified internal rate of return, will work similarly to the IRR function, although this function will take the cost of borrowing the initial investment funds into consideration.

It will also use the compounded interest which was earned by reinvesting wash cash flow.

What makes the MIRR function so useful is that it has enough flexibility to accommodate separate interest rates, concerning borrowing and investing.

As compound interest is calculated, the internal rate of return is going to be different from those of the original internal rate of return, but also the XIRR function.

Taking into consideration our example and that C14 represents Financing Rate and C15 represents Reinvestment Rate, the formula for MIRR is “=MIRR(C2:C10,C14,C15)*12” for our final internal rate of return.

Which Is Best?

So which method should you use in your spreadsheet?

Well, the simple answer is that you should use all three. Especially if you have a third party such as a financial advisor who is going to consider them.

What Is The Difference Between IRR And NPV?

The one major difference is that NPV is an actual numerical amount, and IRR is simply the interest yield which is calculated as a percentage expected from an investment.

In terms of investors, they will typically go with an IRR that is greater than the cost of the capital investment.

Although this could increase the risk of return on investment greater than a weighted average cost of capital.