MIRR Excel Function

MIRR in Excel (Table of Contents)

• MIRR in Excel
• MIRR Formula in Excel
• How to Use MIRR Function in Excel?

MIRR in Excel

Modified Internal Rate of Return (MIRR): It’s a pre-built integrated function in excel that is categorized under Financial function.

• It’s a financial measure of an investment’s or it indicates the potential profit of a project or investments
• MIRR Function is better than the internal rate of return (IRR) in terms of investment performance indicator & it’s more precise
• Most commonly used function by financial analysts in investment banking, equity investment, real estate project & project financial assessment
• MIRR Function is used as both worksheet & VBA function

Definition 

MIRR Function returns a modified internal rate of return for a series of periodic cash flows supplied. The internal rate of return is calculated by using or considering both costs of the investment and interest received by reinvestment of cash.

MIRR Formula in Excel

The Formula for the MIRR Function in Excel is as follows:

The MIRR function syntax or formula has below-mentioned arguments:

• Values: It is a cell reference or array which refers to the schedule of cash flows including the initial investment with a negative sign at the start of the stream

Note: The array must contain at least one negative value (initial payment) and one positive value (returns). Here negative values are considered as payments whereas Positive values are treated as income.

• Finance_rate: It’s a cost of borrowing or interest rate paid on the money used in the cash flows (Negative cash flow)

Note: It can be also entered as decimal value i.e. 0.07 for 7%

• Reinvest_rate: It’s an interest rate which you receive on the reinvested cash flow amount (Positive cash flow)

Note: It can be also entered as decimal value i.e. 0.07 for 7%

MIRR FUNCTION helps out in separating out negative & positive cash flows & discounting them at the appropriate rate.

How to Use MIRR Function in Excel

MIRR Function in Excel is very simple and easy to use. Let understand the working of MIRR in Excel by Some Examples.

Example #1

In the below-mentioned example, Table contains below-mentioned details.

Initial investment: 10000, Finance rate for MIRR is 9% & Reinvestment rate for MIRR is 7%

Positive cash flow: Year 1: 4000

Year 2: 6000

Year 3: 2500

I need to find out the investment’s Modified Internal Rate of Return (MIRR) after three years by using MIRR Function.

Let’s apply MIRR function in cell “D17”.

Select the cell “D17” where MIRR function needs to be applied, Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “MIRR” in the search for a function box, MIRR function will appear in select a function box.

Double click on MIRR function, A dialog box appears where arguments for max function needs to be filled or entered i.e. =MIRR(values, finance_rate, reinvest_rate

values argument: It is a cell reference or array which refers to the schedule of cash flows including the initial investment with a negative sign at the start of the stream. To select an array or cell reference, click inside cell D12 and you’ll see the cell selected, then Select the cells till D15. So that column range will get selected i.e. D12:D15

finance_rate: It is the Interest paid on an initial amount or Annual interest rate for the 10000 loans i.e. 9% or 0.09

reinvest_rate: Interest earned from net income reinvested or the Annual interest rate you receive for the reinvested profits i.e. 7% or 0.07

Click ok, after entering the three arguments. =MIRR(D12:D15,9%,7%) i.e. returns the investment’s Modified Internal Rate of Return (MIRR) after three years. i.e. 10.52% in the cell D17

Note: Initially it will return the value 11%, But to get a more precise value We have to click on increase decimal place by 2 points. So that it will give an exact investment’s returns i.e. 10.52%

Example #2 – Project selection based on Highest returns

In the below-mentioned table, Suppose I want to choose one project from two given projects with the same initial investment of 100000.

Here I need to compare the Five-year modified internal rate of returns based on a 9% finance rate & reinvest_rate of 7% percent for both the project with an initial investment of 100000.

Let’s apply MIRR function in cell “D34” for project 1 & “E34” for project 2.

Select the cell “D34” where MIRR function needs to be applied, Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “MIRR” in the search for a function box, MIRR function will appear in select a function box.

Above step is simultaneously applied in cell “E34”

Double click on MIRR function, A dialog box appears where arguments for max function needs to be filled or entered =MIRR(values, finance_rate, reinvest_rate) for project 1.

values argument: It is a cell reference or array which refers to the schedule of cash flows including the initial investment with a negative sign at the start of the stream. To select an array or cell reference, click inside cell D27 and you’ll see the cell selected, then Select the cells till D32. So that column range will get selected i.e. D27:D32

finance_rate: It is the Interest paid on an initial amount or Annual interest rate for the 10000 loans i.e. 9% or 0.09

reinvest_rate: Interest earned from net income reinvested or the Annual interest rate you receive for the reinvested profits i.e. 7% or 0.07

A similar procedure is followed for project 2 (Ref: Below mentioned screenshot)

Note: To get a more precise value We have to click on increase decimal place by 2 points in cell D34 & E34 for both project 1 & 2 So that it will give an exact investment’s returns.

For project 1

=MIRR(D27:D32,9%,7%) i.e. returns the investment’s Modified Internal Rate of Return (MIRR) after three years. i.e. 6.34% in the cell D34 

For project 2

=MIRR(E27:E32,9%,7%)i.e. returns the investment’s Modified Internal Rate of Return (MIRR) after three years. i.e. 5.74% in the cell E34

 

Inference: Based on the MIRR calculation, Project 1 is preferable, it has given better returns compared to project 2

Things to Remember About MIRR in Excel

• Values must contain at least one negative value & one positive value to calculate the modified internal rate of return. Otherwise, MIRR returns the #DIV/0! error
• If an array or reference argument contains empty cells, text or logical values, those values are ignored
• #VALUE! error occurs if any of the supplied arguments is not a numeric value or non-numeric
• Initial investment needs to be in negative value, otherwise, the MIRR Function will return an error value (#DIV/0! Error)
• MIRR Function takes into consideration both the cost of the investment (finance_rate) and the interest rate received on cash reinvestment (reinvest_rate).
• Main Difference between MIRR & IRR is MIRR considers the interest received on the reinvestment of cash, whereas the IRR does not consider it.

You can download this MIRR Function Excel template here – MIRR Function Excel Template

Recommended Articles

This has been a guide to MIRR in Excel. Here we discuss the MIRR Formula in Excel and how to use MIRR Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

1. Guide to TEXT Function in Excel
2. TANH Function in Excel
3. Examples of TRUE Function in Excel
4. SUBTOTAL Function in Excel