What is the MIRR Function?
The MIRR function is categorized under Financial functionsFunctionsThe MIRR function is categorized under Financial functions. The function will R, which is the initial investment value and a series of net income values. MIRR uses a schedule of payments, including an initial investment and a series of net income payments, to calculate the compounded return, assuming the Net Present. The function will R, which is the initial investment value and a series of net income values. MIRR uses a schedule of payments, including an initial investment and a series of net income payments, to calculate the compounded return, assuming the Net Present Value (NPV)Net Present Value (NPV)Net Present Value (NPV) is the value of all future cash flows (positive and negative) over the entire life of an investment discounted to the present. NPV analysis is a form of intrinsic valuation and is used extensively across finance and accounting for determining the value of a business, investment security, of the investment is zero.
The function is very helpful in financial analysisFinancial Analyst Job DescriptionSee a real Financial Analyst Job Description including all the skills, experience, and education required to be the successful candidate for the job. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models for an investment as it helps to calculate the return an investment will earn based on a series of mixed cash flows.
We can also use MIRR in financial modelingWhat is Financial ModelingFinancial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. A 3 statement model links income statement, balance sheet, and cash flow statement. More advanced types of financial models are built for valuation, plannnig, and, although the common practice is to use IRRInternal Rate of Return (IRR)The Internal Rate of Return (IRR) is the discount rate that sets the net present value of an investment equal to zero. This guide to calculating IRR will give several examples and who why it's used in capital budgeting, private equity and other areas of finance and investing. If IRR is greater than cost of capital, as transactions are studied in isolation. MIRR is different from IRR as it helps set a different reinvestment rate for cash flows received. Thus, the MIRR function considers the initial cost of the investment and also the interest received on the reinvestment of cash, whereas the IRR function does not.
Formula
=MIRR(values, finance_rate, reinvest_rate)
The MIRR function uses the following arguments:
- Values (required argument) – It is an array or a reference to cells that contain numbers. The numbers are series of payments and income that incur negative values. Payments are represented by negative values and income by positive values.
- Finance_rate (required argument) – It is the interest rate that we pay on the money that was used in the cash flows.
- Reinvest_rate (required argument) – It is the interest that we receive on the cash flows as we reinvest them.
Notes on the arguments:
- The argument value should contain at least one positive and one negative value to calculate MIRR.
- The payment and income values should be in the sequence we want and with the correct signs, which is positive values for cash received and negative values for cash paid.
- If n is the number of cash flows in values, frate is the finance_rate, and rrate is the reinvest_rate, the formula for MIRR is:
How to use the MIRR Function in Excel?
To understand the uses of the MIRR function, let’s consider a few examples:
Example 1
Let’s assume we need to choose one project from two given projects. The initial investment of both the projects is the same. Year-wise, the cash flows are as follows:
In the screenshot above, C5 and D5 are the initial investment amounts in Project I and Project II. Cells C6:C10 and D6:D10 show the cash inflow for Project I and Project II, respectively.
The finance rate and the reinvestment rate of the project is 6% and 5%, respectively. The formulas to be used are as follows:
Remember that the initial investment needs to be in negative form, else the formula will return an error. The initial investment is a negative value, as it is an outgoing payment, and the income payments are represented by positive values.
We get the result below:
As we can see above, Project I is more preferable.
Things to remember about the MIRR Function
- #DIV/0! error – Occurs when the given value array does not include at least one positive value and one negative value.
- #VALUE! error – Occurs when any of the given arguments is non-numeric.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts