EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Financial Functions in Excel MIRR Formula in Excel
Secondary Sidebar
Excel Functions
  • Financial Functions in Excel
    • PPMT Function in Excel
    • NPER Function in Excel
    • NPV Formula in Excel
    • MIRR Formula in Excel
    • Excel RATE Formula
    • Excel PV Formula
    • Excel PRICE Function
    • MIRR Excel Function
    • PV Excel Function
    • YIELD Excel Function
    • NPV Function in Excel
    • RATE Function in Excel
    • IRR Function in Excel
    • FV Function in Excel
    • PMT Function in Excel
    • XIRR In Excel
    • IPMT in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel for Finance Course Certification
  • Excel Data Analysis Training
  • Excel for Marketing Training

MIRR Formula in Excel

By Pradeep SPradeep S

MIRR in Excel

MIRR in Excel (Table of Contents)

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

MIRR in Excel

MIRR function under the financial category is a unique statistical function that provides the interest rate on invested amount and finds a series of net income against the invested amount. For using MIRR, we must have a series of investments, finance rate, and reinvest rate, and also argument should contain at least one positive and one negative value as well.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,412 ratings)

MIRR Formula in Excel

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

MIRR Formula in Excel

The MIRR function syntax or formula has the 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 also be entered as a decimal value, i.e. 0.07 for 7%

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

Note: It can also be entered as a 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 us understand the working of MIRR in Excel by Some Examples.

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

Example #1

In the below-mentioned example, the Table contains the 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 the MIRR Function.

Example 1

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

MIRR Example 1-1

Select the cell “D17” where the 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.

MIRR Example 1-2

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

MIRR Example 1-3

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%

increase decimal

MIRR Example 1-4

Example #2

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.

Example 2

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

MIRR Example 2-1

Select the cell “D34” where the 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.

MIRR Example 2-2

The above step is simultaneously applied in cell “E34.”

Double click on the 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

MIRR Example 2-3

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

MIRR Example 2-4

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.

increase decimal

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

 Modified Internal Rate of Return (MIRR)

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

Things to Remember

  • 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 a 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).
  • The main Difference between MIRR & IRR is MIRR considers the interest received on the reinvestment of cash, whereas the IRR does not consider it.

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 examples and downloadable excel templates. You may also look at these useful functions in excel –

  1. TRUE Function in Excel
  2. MAX Excel Function
  3. Write Formula in Excel
  4. TRIM Formula in Excel
Popular Course in this category
Excel for Finance Training (18 Courses, 7+ Projects)
  18 Online Courses |  7 Hands-on Projects |  85+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download MIRR Function Excel Template

EDUCBA

Download MIRR Function Excel Template

EDUCBA

डाउनलोड MIRR Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more