MIRR Excel Formula

Author Zaheer    Category Formulas     Tags ,

The ever progressing technology introduced brand new techniques of accomplishing any kind of assignment quickly. It also assists people in producing and maintaining files in a clear way. The field of accounting is not exempted to this. It has acquired great advancements with the aid of the advanced technology.

Microsoft Excel is spreadsheet software that plays an important role in the field of accounting. In general, you can save a lot of your time with the use of spreadsheets whether you use them to keep track of your inventory, project tax, cash flow or even expenses. Excel features several formulas and functions that are useful to this field. And one of these is the MIRR function. This article will discuss and overview of the function and will provide an example so it will be easy for you to learn the usage of the MIRR function.

Basic Description of the MIRR Function in Microsoft Excel

The MIRR Function in Excel is used to return the Modified Internal Rate of Return (MIRR) for a series of periodic cash flows that is supplied in the formula of the function (i.e. a set of values which incorporates a value of the initial investment and a series of net income values). The correct syntax of the MIRR function is:

MIRR(Values, Finance_rate, Reinvest_rate)

The arguments in the formula are the following:

  • Values – This argument refers to the a reference of an array of cells which contains the series of cash flows (net income and investment values) that take place at regular periods.

You have to take note that the values should contain at least one positive value that corresponds to income and at least one negative value that corresponds to payment.

  • Finance_rate – This is a required argument and it refers to the interest rate on the money that is used on the cash flows.
  • Reinvest_rate – This is a required argument and it refers to the interest rate paid on the reinvested cash flows.

Example on the Usage of the MIRR Function in Excel

This particular example will illustrate to you the computation of the MIRR after 3 years and the MIRR after 5 years.

For example, type in -$100.00 in cell B2 as the initial investment. Then supply these income values from year 1 to year 5 into cell B3 to B7 respectively: $12.00, $14.00, $11.00, $13.0 and $16.00. The values for the finance rate and reinvest rate are 5.5 and 5, respectively.

You can now supply all the required values in the formula of the MIRR function. Type in this formula in any cell where you want the result to be displayed, =MIRR(B2:B5, 5.5, 5). The function will return the MIRR after 3 years which is 74.02%.

Type again this formula in any cell, =MIRR(B2:B7, 5.5, 5). The function will return the MIRR after 5 years which is 185.79%.

Common Errors in the MIRR Function

If you obtain an error from the function, this is possibly one of the following:

  • #DIV/0! – This error value is displayed if the range of values that is supplied in the formula doesn’t contain at least one positive and at least one negative value.
  • #VALUE! – This error value is showed if any of the argument that is supplied in the formula is not identified by Excel as numeric values.

Post comment