AccountingMS Excel

IRR, XIRR and MIRR Formulas in Microsoft Excel 2013



When you’re deciding between different investment opportunities, it’s always handy to gauge them against different criteria. Veteran investors have tons of different investment criteria and tools at their disposal which they use quite effectively in order to decide if they should invest in certain financing opportunity or simply pass. Similarly, amateur investors might need the help of financial experts (similar to the ones found at Omaha investment management firms) that can help them allocate their funds wisely in order to get maximum returns.

That said, there are some basic “checks” that every experienced investor applies to gain a quick understanding of any potential investment opportunity. Whether it’s going to bear any benefit or incur losses. Among these various financial evaluation tools is IRR or Internal Rate of Return which gives a decisive and clear-cut view if an investor should stake his funds or not in any given investment opportunity.

Since this article deals only with the application of the IRR using Microsoft Excel 2013 and not the details of it from the financial analysis perspective, I will assume that the average reader on this page knows very well about IRR and just wants to learn how he can use it in Excel. So I’ll keep myself fairly limited. If you are wanting to learn about MS Excel on a more general level, then you could take a look at these 20 Best Resources To Learn MS Excel to expand your knowledge further. With Excel, you can create a visual timeline chart and map out a project schedule and phases. This tool allows you to monitor the overall progress of your projects, categorize them according to the assigned employees, and check the status of tasks. You can even use the Excel Action Plan Template, create Gantt chart, which is a popular project management tool because it demonstrates tasks based on the length of time they will take, the start and end dates. However, if you are looking for attaining a certification in Microsoft courses, then you might need to enrol for it and then prepare the same. There are many exam guides and excellent materials for those seeking to do appear for the exams. One could also consider looking into the available Microsoft Free Exam Dump if they need an idea of how the questions are going to be.

In fact, I will discuss not just simple IRR but also a couple of other variations of it which is available in Microsoft Excel 2013 such as XIRR and MIRR. We’ll have a look at all three of them in this article.

So let’s get started.

Also Read: How to Use Microsoft Excel Solver Add-In

IRR, XIRR and MIRR Formulas in Microsoft Excel 2013

Internal Rate of Return (IRR) in Microsoft Excel 2013

What is IRR?

Simply put Internal Rate of Return (IRR) is a discount rate at which the investor’s net cash flow for any particular project is zero. This means that any investment made at IRR neither generates nor consumes any money in the specific period when invested in any particular project.

The reason investors are interested in IRR is so that they know what is the maximum discount rate beyond which they should not expect any positive returns. So they strive for the rate of return which is below the IRR.

In isolation, any investment opportunity which has a discount rate lower than IRR is acceptable. I hope that makes sense.

Traditional IRR Formula

The basic concept of deriving IRR is simple. You take the prevalent discount rate and figure out the Net Present Value (NPV) using that discount rate.

Second, you assume another discount rate and calculate the Net Present Value (NPV) using that supposed discount rate. Obviously, one discount rate may generate lower NPV than the other. You need to keep a record of that.

Ideally speaking, one of the supposed discount rate should generate a positive NPV while the other, negative. This ensures that the resulting IRR is accurate. However, discount rates with both positive or negative NPVs also generate IRR closer to the accurate one. So assuming discount rates with positive and negative NPVs is not absolutely necessary but recommended.

Anyways, the “out-of-excel” or the traditional formula to calculate IRR is:

Internal Rate of Return (IRR) - Formula
Image Courtesy: ACCA Global

However, since we’re talking about the Excel spreadsheet here, applying this formula “verbatim” would pretty much kill the purpose of working on a spreadsheet. Since the main purpose of Microsoft Excel is to speed up your work, there’s a short formula as well when it comes to IRR in Microsoft Excel.

Formula for IRR in Excel 2013

The IRR formula in excel is quite simple. You don’t need to create charts and assume some imaginary discount rates and figure out NPVs using them and then apply the IRR formula using those discount rates and NPVs. All you have to do is to straightaway apply the IRR formula in Excel 2013 on your cash flow projections. That’s it. Microsoft Excel will show you the discount rate at which the Net Present Value (NPV) of the project is zero (or close to zero).

Here’s the IRR formula in Excel:

=IRR(Range of Values, [guess])

In its simplest form, the IRR formula requires just the range of values. This is an array of range of cells or you can enter the values directly in the formula.

Remember, there should be at least one negative value (initial investment or outflow) and one positive value (inflows).

The second argument, [guess] is an assumption by the user of the IRR formula. You can enter any value which you think maybe the IRR. It gives the starting point for excel to work on. However, since this is an optional argument, you may very well skip it. In that case, Excel will assume 10% (or 0.1) as the supposed discount rate.

In order to understand the IRR formula better, let’s perform an exercise. After all, there’s no better way to grasp a concept by actually applying yourself to it.

Also Read: Simple guide to Microsoft Excel Date Function

Example 1:

Suppose a company ABC has laid down an investment plan which bears the following details:

Initial Investment (Year 0): $70,000.00

Cash Inflows

Year 1 : $15,000.00

Year 2 : $25,000.00

Year 3 : $30,000.00

Year 4 : $20,000.00

Requirements:

  1. Calculate the IRR of the project
  2. Decide should the project be undertaken if the target rate of return is 7.00%

Solution 1:

Traditionally speaking, you can go ahead and apply the conventional IRR formula as mentioned above. However, since we need to perform a quick analysis, we don’t have time to calculate two discount rates and their corresponding NPVs and then apply them to the formula. We can quickly figure out our IRR using the shortcut Excel’s formula.

To do that, let us first line up our cash flows and then calculate the IRR. Here’s how it will be done:

IRR Excel 2013 Formula Example 1

As you may see we applied the following formula as you may see we didn’t bother about the second argument though.

=IRR(A4:E4)

The IRR calculated above is 10.3%. This means that at this rate company ABC will break-even. So based on this solution, we can conclude if the company should undertake the project or not.

Solution 2:

That’s the easy one. Since the IRR is 10.3% the project should be accepted if the target return is 7.00% as stated above.

Also Read: How to Download, Install and Remove Microsoft Excel Add-Ins

XIRR

What is XIRR?

In the above example, you assumed that all the cash flows will take place evenly throughout the period of 5 years. However, in the real world, this may not be the case. The timing of the payments you receive may vary year to year. In that case, Excel provides another formula to calculate the IRR of your project assuming that the cash flows will not be even throughout the project’s lifetime.

XIRR Formula in Excel 2013

The XIRR formula in Excel takes one more argument when compared to the basic IRR formula above and that’s of the range of dates. So the formula would become:

=XIRR(Range of Values, Range of Dates, [guess])

Needless, to say that you will have to add a column of the dates on which you project the cash flows to occur.

Let’s do an example to understand the XIRR formula better. We’ll continue the example from above.

Example 2:

Everything remains same just like the first example above except for the dates on which the cash flows occur. So let’s add the dates below

Initial Investment (Year 0): $70,000.00

Date 0: January 1, 2018

Cash Inflows

Year 1 : $15,000.00

Date 1: June 25, 2019

Year 2 : $25,000.00

Date 2: December 31, 2019

Year 3 : $30,000.00

Date 3: August 10, 2020

Year 4 : $20,000.00

Date 4: December 31. 2021

Requirements:

  1. Calculate the XIRR of the project
  2. Decide should the project be undertaken if the target rate of return is 11.00%

Solution 1:

After arranging our data in tabular format below, the XIRR can be calculated as below:

=XIRR(B2:B6, C2:C6)

XIRR Excel 2013 Formula Example 2

Note that the XIRR of the project is 10.5%

Solution 2:

Given our target rate return is 11.00% whereas the XIRR of the project is 10.5%, undertaking project will incur a loss hence ABC company should avoid investing in this project.

Also Read: How to calculate Excel Date difference using the DATEDIF() function

Modified Internal Rate of Return (MIRR) in Excel 2013

What is MIRR?

The MIRR attempts to add a grain of reality in the IRR function.

In the real world, cash flows don’t occur out of thin air. You need to invest some funds and in most projects requiring heavy capital expenditures, you acquire those funds through borrowings.

Needless to say that you pay a certain amount of interest on your borrowings.

Similarly, when you start receiving cash flows from a project, there will hardly be a case when you will place all those funds in your petty cash.

A wise businessman reinvests those funds in order to maximize his returns.

So the MIRR function in Excel attempts to find the IRR of a project when you have to pay the finance cost on your borrowings as well as when you reinvest your inflows and gain a return on it.

The MIRR Formula in Excel 2013 goes like this:

MIRR Formula in Excel 2013

=MIRR(Range of Values, Finance Cost, Reinvestment Rate)

The Range of Values is the series of cash inflows and outflows over the project’s lifetime.

Finance Cost is the borrowing or interest rate at which you acquire the funds to invest in the said project.

Reinvestment Rate is the rate of return you will gain by reinvesting your cash flows from the said project.

Example 3:

Just to give a final touch and gain more understanding of MIRR, let’s continue the very first example after adding the finance cost and the Reinvestment Rate as 5.00% and 4.00% respectively.

Requirement:

  1. Calculate the MIRR of the project

Solution 1:

As mentioned above, the MIRR takes 3 arguments. Cashflows, Finance Cost and the Reinvestment Rate. Since we have all 3 of them, our formula for MIRR would be:

=MIRR(A3:E3, B5, B6)

MIRR Formula in Microsoft Excel 2013

This gives us the MIRR of 7.97% which means that following the above cash flow projections, your NPV will be zero if your discount rate is 7.97% and you borrowed the funds at 5.00% and reinvest the surplus at 4.00%.

I hope that makes sense.

Final Word

I hope this article gives you valuable insights about calculating IRR, XIRR and MIRR in Microsoft Excel 2013. You may have also noticed that how easy and quick it is to calculate IRR and its other variations using Excel.

However, having said that, one point to keep in mind is that even though Microsoft Excel does provide us the facility of quickly calculating the IRR but an effective financial modeling involves extensive documentation and having each cash flow, no matter how minute, in black and white. So the longer and traditional form of calculating IRR would still be preferable in that case. However, knowledge of doing something quickly and swiftly won’t hurt a bit. In fact, it can serve as a quick confirmation tool to check if your calculations and figures are indeed valid.