WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » MIRR in Excel

MIRR in Excel

MIRR Function in Excel

MIRR in excel is an in-build financial function used to calculate the modified internal rate of return for the cash flows supplied with a period. This function takes the set of initial investment or loan values and a set of net income values with the interest rate paid on initial amount including the interest earned from reinvestment of earned amount and returns the MIRR (modified internal rate of return) as output.

Syntax

MIRR Function Formula

Parameters

The details of the parameters used in the MIRR formula in Excel are as follows:

  • Values: Here, values are an array of cash flow representing a series of payment amounts or range of references or set of income values, including the initial investment amount.
  • Finance_rate: The finance rate is a rate of interest paid on the amount used during cash flow.
  • Reinvest_rate: Reinvest rate refers to the interest rate earned from the reinvested profit amount during cash flow.

How to use the MIRR Function in Excel? (with Examples)

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

Example #1

Consider an initial loan amount of 25,000 as an initial investment amount (loan amount) with an interest rate of 5% yearly, and you have earned an interest rate of 8% from the reinvested income. In MIRR, the loan amount or initial investment amount is always considered as (-ve) value.

Below are the table shows the details of income after a regular interval of time. Considering the cash flow of income for 1st, 2nd, 3rd, 4th, and 5th years are as follows: 10,911, 14,716, 19,635, 18,700, and 18,477.

Now calculate MIRR in Excel (Modified internal return rate) after 2 years:

=MIRR(B4:B6,B10,B11) and output MIRR is 3%.

MIRR Excel Function (Example - 1)

Similarly, calculate MIRR (Modified internal rate of return) after 3 and 5 years:

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

MIRR after three years will be =MIRR(B4: B7, B10, B11), and output is 25%.

MIRR after five years will be=MIRR(B4: B9, B10, B11), and output is 31%.

MIRR Excel Function (Example - 1-1)

Example #2

Consider an initial loan amount of 10,000 as an initial investment with an interest rate of 5% yearly, and you have earned an interest rate of 8% from the reinvested income.

Below are the table shows the details of income after a regular interval. Considering the cash flow of income for 1st, 2nd, 3rd, 4th, and 5th years are as follows: 7,505, 5,338, 9,465, 5,679, and 6,004.

Now calculate MIRR after 2 years:

=MIRR(B15:B17,B21,B22)and output MIRR is 16%.

Similarly, calculate MIRR after 3 and 5 years:

MIRR after three years will be =MIRR(B15: B18, B21, B22), and output is 34%.

MIRR after five years will be =MIRR(B15:B20,B21,B22) and output is 32%.

MIRR Excel Function (Example - 2)

Things to Remember

  1. The loan amount is always considered as a negative value.
  2. The modified internal return rate is always calculated on a variable cash flow after regular intervals.
  3. Error handling:
  • #DIV/0!:  MIRR excel will return #DIV/0! Exception when the supplied error does not contain at least one negative value of one positive value.

#div Error

  • #VALUE!: MIRR will return this kind of exception when any of the supplied value is non-numeric.

#value error

Recommended Articles

This has been a guide to MIRR Function Excel. Here we discuss how to use the MIRR Excel function along with practical examples and a downloadable template. You can also learn more about excel from the following articles –

  • Examples of IRR
  • IRR vs. ROI
  • Excel NPER Function
  • RANKX Function in Power BI
  • Excel Paste Transpose
2 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download MIRR Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More