The MIRR function calculates the modified internal rate of return for a series of periodic cash flows.

Description of the MVSD function

Returns the modified internal rate of return for a series of periodic cash flows. The MVSD function takes into account both the costs of attracting investment and the interest received from reinvesting funds.

Syntax

=MVSD(values; finance_rate; reinvest_rate)

Arguments

values ​​rate_finance rate_reinvest

The values ​​must contain at least one positive and one negative value. Otherwise, the MVSD function returns the error value #DIV/0!.

If an argument that is an array or a reference contains text, booleans, or empty cells, those values ​​are ignored; cells containing zero values ​​are counted.

Required. The rate of interest paid on money in circulation.

Required. The rate of interest received when reinvesting funds.

Notes

Examples

Example1 Example task

Task
Let's say you've been fishing for five years. Five years ago you borrowed 150,000 rubles at 10 percent interest per annum to buy a boat. Revenue for each year was respectively 44,000 rubles, 32,000 rubles, 25,000 rubles, 33,000 rubles. and 48,000 rub. During these years, you reinvested your profits at 13 percent interest per annum.

Solution
Let's enter the initial data:

Internal rate of return (IRR) and its modified version (MIRR)

A method within the framework of cost analysis, which is more often used in practice compared to NPV (as empirical studies show), is the determination of the internal rate of return, or internal rate of return (IRR). The value of the IRR criterion shows what return (profitability) the investor will receive , if it implements an investment project with a net present value NPV equal to zero. With a real positive NPV value of the investment project being implemented, IRR means the minimum return that an investor can count on (the real return will be higher). There is another interpretation of the value of the internal rate of return criterion: IRR is equal to the maximum acceptable price of capital attracted to finance an investment project, at which the project’s return will not fall below expected. The essence of the method is to calculate the annual average rate of return that the investment project provides , and comparing it with the range of alternative investment rates on the market. The value of this criterion is equal to the bet calculated as a result of the following equation:

The biggest difficulty here is the correct calculation of the average return for a certain period (for example, a year), when monetary benefits come over a number of periods and are not equal to each other. Calculation using the formula of arithmetic average return over the years is not suitable. Analysts find the answer in the internal rate of return formula, which equates current investments with a given estimate of benefits. Note that equalization is possible not only at time t = 0. Such a relationship can be considered for any future point in time. For example, the IRR value can be calculated by comparing future estimates of investment costs (at the time of project completion) with future estimates of current revenues.

This analytical method has two significant advantages. First, evaluate the project in terms of annual profitability, and not in absolute values ​​of the increase in value. The absolute values ​​of cost estimates are difficult to perceive by non-professionals, and it is quite problematic to control them during the implementation of the project. Profitability in percent per annum as a characteristic of a project is more understandable for comparisons and assessment of benefits. Secondly, making a decision on the project does not require justification for a fixed discount rate (as provided in the NPV method). It is enough to understand the range of alternative bets on the market. If the resulting profitability of the project is obviously higher than multi-risk market rates (calculated estimates for projects at the level of 80, 120, 350% per annum are often observed), then the project is considered effective.

Many companies, when developing regulations on the adoption of an investment program and investment budget, fix the value of the barrier investment rate. Thus, for core projects of the Lukoil company (oil production, oil refining), the rate is fixed at 15% per annum (meaning projects assessed based on ruble cash flows). The development company Sistema-Hals does not consider projects with a yield below 21%.

The IRR indicator reflects the maximum allowable level of costs for capital attracted for the project. For example, if the project could be financed entirely by a bank loan (a completely unrealistic assumption), then the IRR value would indicate the upper limit of the acceptable interest rate. A higher rate would lead to the project “eating up” the company’s value. Examples of project financing using exclusively borrowed funds are often given, but it should be borne in mind that these funds are secured by both the assets of the project and the company implementing it. In this regard, when fully borrowing a project, it is incorrect not to take into account the risk of the owners of the company’s equity capital and their required level of profitability.

The meaning of such a calculation of profitability is well demonstrated by the situation with finding a rate that makes the investor’s position neutral to two possible investment decisions. The first solution is to save the investment amount, refuse to invest in the project and reinvest it annually at a certain percentage. The second solution is to receive, in exchange for the investment amount, periodic cash flows that are nominally equal to the flows on the investment project and can also be reinvested at a certain rate. In circulation, IRR is finding the interest rate that equalizes these two investor decisions.

The described method for determining the profitability of a project has criticism both from the formal calculation plan (for example, it is possible that the equation does not have a root, the presence of several roots) and from the economic and semantic ones. The first (main) remark concerns the low feasibility of the assumption about the possibility of reinvesting project funds at the estimated rate. Only a very small number of projects can fall under such an assumption (“walking excavator”, when the investment project is duplicated annually). The second note is the different risk of investment outflows and operating cash flows for the project.

It is more correct to calculate the rate of return of a project using the modified internal rate of return (MIRR) formula, when two rates are introduced that reflect the real market environment of the project: financial, characterizing the risk of investment outflows and often taken at the level of the risk-free or lending rate (average cost of borrowed capital); refinancing rate available to the company. The reinvestment rate-adjusted internal rate of return, or MIRR, also known as the modified internal rate of return, is actually much easier to calculate manually than the IRR. And this happens precisely because of the assumption made about reinvestment.

The procedure for calculating the modified internal rate of return MIRR:

1. Calculate the total discounted value of all cash outflows and the total accrued value of all cash inflows.

Discounting is carried out at the price of the project’s financing source (cost of capital raised, financing rate or required rate of return on investment, Capital Cost, CC or WACC), i.e. at the barrier rate. The increase is carried out at an interest rate equal to the level of reinvestment. The accumulated value of inflows is called net terminal value (Net Terminal Value, NTV).

2. Set a discount factor that takes into account the total present value of outflows and the terminal value of inflows. The discount rate that balances the present value of an investment (PV) with its terminal value is called MIRR.

The formula for calculating the modified internal rate of return (MIRR):

Where is the cash inflow in period t = 1, 2, ...n;

Cash outflow in period t = 0, 1, 2, ... n (in absolute value);

r - barrier rate (discount rate), fractions of a unit;

d - level of reinvestment, unit fraction (interest rate based on possible income from reinvestment of received positive cash flows or rate of return on reinvestment);

n - number of periods.

On the left side of the formula is the amount of investment (capital investment) discounted at the price of capital, and on the right side is the accrued value of cash receipts from the investment at a rate equal to the level of reinvestment.

Note that the MIRR formula makes sense if the terminal value of inflows exceeds the sum of discounted cash outflows (money inflows are greater than cash outflows).

The MIRR criterion always has a single meaning and can be used instead of the IRR indicator to evaluate projects with extraordinary cash flows. The project is acceptable to the initiator if the MIRR is greater than the barrier rate (the price of the funding source).

Example No. 1. Investment size - $115,000. Investment income in the first year: $32,000; in the second year: $41,000; in the third year: $43,750; in the fourth year: $38,250. The size of the reinvestment level is 6.6%

(1 + MIRR) 4 = (32000 · (1 + 0.066) 3 + 41000 · (1 + 0.066) 2 + 43750 · (1 + 0.066) + 38250) / (115000 / 1) = 170241.48 / 115000 = 1 ,48036

Answer: the modified internal rate of return is 10.304%, which is greater than the reinvestment rate (6.6%), which means that the project can be implemented.

The internal rate of return (IRR) for such an investment is 30.53%. Thus, it is assumed that 2,240 thousand rubles received at the end of the first year are invested in some project or account, which subsequently brings 30.53%.

MIRR is the internal rate of return adjusted for the reinvestment rate.

From a practical standpoint, the most significant shortcoming of the internal rate of return is the assumption made in determining all discounted cash flows generated by an investment that compound interest is calculated at the same interest rate. For projects that provide rates of return close to the firm's hurdle rate, reinvestment problems do not arise, since it is quite reasonable to assume that there are many investment options that provide a rate of return close to the cost of capital. However, for investments that provide very high or very low rates of return, suggesting that new cash flows must be reinvested may distort the true return on the project. The concept of reinvestment-adjusted internal rate of return was proposed to counter this distortion inherent in traditional IRR.

Despite its cumbersome name, the reinvestment rate-adjusted internal rate of return, or MIRR, also known as the modified internal rate of return, is actually much easier to calculate manually than IRR. And this happens precisely because of the assumption made about reinvestment.

The procedure for calculating the modified internal rate of return MIRR:

1. Calculate the total discounted value of all cash outflows and the total accrued value of all cash inflows.

Discounting is carried out at the price of the project’s financing source (cost of capital raised, financing rate or required rate of return on investment, Capital Cost, CC or WACC), i.e. at the barrier rate. The increase is carried out at an interest rate equal to the level of reinvestment.

The accumulated value of inflows is called net terminal value (Net Terminal Value, NTV).

2. Set a discount factor that takes into account the total present value of outflows and the terminal value of inflows. The discount rate that balances the present value of an investment (PV) with its terminal value is called MIRR.

The formula for calculating the modified internal rate of return (MIRR):

CFt - cash inflow in period t = 1, 2, ...n;
It is the outflow of funds in the period t = 0, 1, 2, ... n (in absolute value);
r - barrier rate (discount rate), fractions of a unit;
d - level of reinvestment, unit fraction (interest rate based on possible income from reinvestment of received positive cash flows or rate of return on reinvestment);
n - number of periods.

On the left side of the formula is the amount of investment (capital investment) discounted at the price of capital, and on the right side is the accrued value of cash receipts from the investment at a rate equal to the level of reinvestment.

Note that the MIRR formula makes sense if the terminal value of inflows exceeds the sum of discounted cash outflows (money inflows are greater than cash outflows).

The MIRR criterion always has a single meaning and can be used instead of the IRR indicator to evaluate projects with extraordinary cash flows. The project is acceptable to the initiator if the MIRR is greater than the barrier rate (the price of the funding source).

Example No. 1. The investment amount is $115,000.
Investment income in the first year: $32,000;
in the second year: $41,000;
in the third year: $43,750;
in the fourth year: $38,250.
Reinvestment rate - 6.6%

(1 + MIRR) 4 = (32000 * (1 + 0.066) 3 + 41000 * (1 + 0.066) 2 + 43750 * (1 + 0.066) + 38250) /
/ (115000 / 1)= 170241,48 / 115000 = 1,48036

Answer: the modified internal rate of return is 10.304%, which is greater than the reinvestment rate (6.6%), which means that the project can be implemented.

The internal rate of return (IRR) for such an investment is 30.53%. Thus, it is assumed that 2,240 thousand rubles received at the end of the first year are invested in some project or account, which subsequently brings 30.53% of income until the seventh year. Likewise, 3,050 thousand rubles related to the second year will bring 30.53% of income starting from the third year. And so on.

But let's assume that this is an exceptional investment and that the average rate of return the company can expect on its regular investment is 14% rather than 30.53%. Can you guess how much IRR overestimates the return on the project in this case?

When using MIRR, compound interest on annual cash flows is calculated at a more appropriate interest rate, in this case 14%. Each cash flow and its interest are carried forward to the end of the investment (here the seventh year). The resulting future values ​​are then added up and the result is compared with the original investment. Instead of determining the internal rate of return by considering seven cash receipts, we calculate the IRR for one expense and one cash receipt:

Clean monetary flow									Future price FVt

Sum FVt = 30579.7537;
(1+MIRR) 7 = 30579.7537 / 7800 = 3.9205;
MIRR = 0.215522 or 21.5522%.

MIRR for the project under consideration, allowing for reinvestment of cash proceeds at a rate of 14%, is equal to 21.55%, which is noticeably less than IRR equal to 30.53%.

Using MIRR instead of IRR always mutes the investment effect. Less profitable investments that have rates of return below the hurdle rate or reinvestment rate will always look better using MIRR than IRR, since the former will generate higher cash flows than the latter. On the other hand, particularly profitable investments (as shown above) for which the rate of return is higher than the hurdle rate will have a lower MIRR for the same reason.

The MIRR method does not have the problem of multiple definitions of the internal rate of return as does the IRR method.

In practice, the MIRR indicator is rarely used, which cannot be considered justified.

Home => Methods of financial analysis => MIRR (modified internal rate of return)
Copyright © 2008-2009 Directory "Financial Analysis"

A significant problem in applying the method IRR– a significant difference in the risks of operating and investment flows, which are spread over the years. Finding the average rate does not allow the analyst to make the correct decision on the project. To remove the problem of risk differences, a modified internal rate of return method is proposed.

Modified internal rate of return method (MIRR) provides the calculation of an annual rate that equates the given values ​​of investment outflows (with a discount rate at the level of risk-free return or profitability of borrowing for the project) with a future assessment of operating benefits (the accrual rate is the cost of capital for the company and the project).

MIRR method rule: if according to the project the calculated value MIRR exceeds the specified cut-off rate (the opportunity cost of money for the project), then the project can be accepted.

If we denote the future estimate of operating cash flows at the end of the year T through FV(CF), and the given estimate of investment costs – through PV(Inv), then the formula for calculating MIRR will take the form

Example 11

For a project with investment costs of 1000 den. units and with operating cash flows of 100, 300, 400 and 500 den. units by year we will show the application of the modified rate of return method with a projected reinvestment rate of 10%.

Solution.

Calculation scheme MIRR for this project the following.

1. The duration of the project is four years (T = 4). For a cost of capital equal to 10%, the future estimate of cash flows generated by the project is calculated (Fig. 29.4):

FV(CF) = 100 + 300 -1,11 + 400 1,12 + 500 1,13 = 1579,5.

2. MIRR is the discount rate at which the current valuation F.V. equal to the current estimate of investment costs:

PV(Inv) = 1000 = 1579,5/(1 + MIRR)" = 1000MIRR = 12,1%.

Rice. 29.4. Calculation of future estimates of project benefits in the methodMIRR

For calculation MIRR The analyst needs to specify two rates of return for investment and operating cash flows: the financing rate and the reinvestment rate. In financial functions Excel built-in algorithm for calculating the modified internal rate of return (MIRR). The problem of the implemented algorithm in a computer program is the reflection of all negative flows as investment flows, and positive flows as operating flows. An example implementation of the algorithm is given below.

Example 12

A six-year project is being considered with the cash flows shown in Table. 29.7.

Table 29.7

Investment and operating flows for the project

The IRR function for cash flows (-100, -10, 0, 180, 250, -80) gives a value of 0.466 (46.6% per annum). To assert that the project can be accepted at a cost of money less than 46.6% per annum would be reckless, since the analyzed flow is non-standard (changes sign twice: from “minus” to “plus” and from “plus” to “minus”) and we can assume the existence in the equation under consideration NPV= 0 two roots.

The MVSD function (Fig. 29.5) for the same flow will give a lower value: 0.3236 (32.36% per annum). The calculation is based on setting the financial rate at 10% and the reinvestment rate at 14%. If the financial rate increases to 14%, then the value MIRR will become 33%. When the financing rate is reduced to 7%, the value MIRR will also decrease to 32%. The higher the specified reinvestment rate, the higher the resulting value will be MIRR. So, with a financial rate equal to 10% and a reinvestment rate of 20%, the value MIRR will be 34%.

Rice. 29.5. Setting project parameters for calculationMIRR for the financial function of the Ministry of Internal AffairsExcel

Please note that the calculation MIRR does not give rise to the problem of multiple roots (values ​​of the required rate) or the lack of a solution in case of non-standard cash flows of the project. This is another advantage of the method compared to calculation IRR.

After reading the article, the reader will be able to find out:

• what is NPV and irr of an investment project;
• how to calculate NPV;
• how to calculate the irr of an investment project;
• how to calculate mirr;
• how the performance indicators of investment projects are calculated in practice.

All investors are faced with the problem of evaluating proposed investment projects. At the same time, it is often difficult to assess the profitability of a project when investments in it are extended over time.

In this case, the main assessment indicators are:

• investment project - irr - internal rate of return;
• NPV-net present value;
• mirr is the modified internal rate of return.

The irr indicator when analyzing the effectiveness of investment projects is most often used together with the NPV indicator.

In general, all indicators that allow making a decision on the advisability of investing in projects are divided into two groups:

• dynamic, based on discounting;
• static, not involving the use of discounting.

Static methods involve the use of well-known formulas for assessing economic efficiency, so we will dwell in more detail on dynamic indicators. The economic efficiency of an investment project npv and irr, as well as mirr are important indicators that allow investors to make the right decisions.

When analyzing investment projects, it is very important to use both groups of indicators, since they complement each other. It is in this case that the investor will be able to get an objective picture that allows him to make the right decision.

Advice! There are many financial calculators or programs, including those in the EXCEL spreadsheet editor, that allow you to calculate project performance indicators. Their use will significantly reduce the time for calculations and will allow for a more thorough analysis of the feasibility of investing.

NPV calculation

Calculation of the net present value indicator - NPV is the difference between the amounts of investments and payments on loan obligations, or, if the loan is not used, payments for the current financing of the project. The calculation is carried out on the basis of a fixed discount rate without taking into account the time factor and allows you to immediately assess the prospects of the project.

Where:

• D- discount rate,
• CF k is the cash inflow in period k,
• n- number of periods,
• INVt is the volume of investment in period t.

The interpretation of the calculations performed is based on the following logical conclusions:

• if NPV is greater than zero, then the project will be profitable;
• if the NPV value is zero, an increase in production volume will not lead to a decrease in profits;
• if NPV is less than zero, the project will most likely be unprofitable.

This indicator is very important when evaluating investment projects and is used together with other dynamic indicators.

Calculation of irr

Calculating the irr indicator of the effectiveness of an investment project has an important economic meaning. The calculation of this coefficient consists of estimating the maximum allowable amount of investment that an investor can spend on the analyzed project. The disadvantage of using irr is the complexity of calculations for an investor who does not have an economic education.

Advice! Despite the well-known indicators for calculating project efficiency, it is necessary to remember that they do not always take into account the specifics of the analyzed projects and therefore it is necessary to additionally use other analysis tools.

Where:

• D 1 – discount rate corresponding to NPV 1 (positive value of net income); ;
• D 2 - discount rate corresponding to NPV 2 (negative net income).

Advice! IRR is a relative measure of the rate of return at which the net present value is zero. The accuracy of the indicator is higher, the smaller the interval between D 1 - D 2, the criterion for choosing an investment project is the following ratio: IRR > D. In the case when several projects are considered, it is necessary to give preference to the one where the IRR is greater.

An important advantage of this indicator is that it allows you to assess the prospects of a project in an inflationary environment. So, for example, if the IRR is less than the official inflation value, then you should be more thoughtful about such a project, since perhaps, ultimately, the investment will not bring profit.

Advice! After calculating irr, be sure to compare it with the inflation rate! If the value of the indicator is lower, then it is necessary to make additional calculations and analyze the prospects for the overall development of the economy.

Analyzing npv irr investment metrics helps highlight the differences and similarities between them.

The calculation of NPV and IRR is based on discounting the cash flows generated by the project:

• NPV allows you to calculate the present value of the project, taking into account the fact that the interest rate is known;
• IRR shows the maximum loan rate at which the project will definitely not be unprofitable.

The difference between these indicators is also due to the fact that NPV shows the result in monetary terms, and IRR - in percentage terms, which is often more understandable to the investor.

Modified internal rate of return MIRR

Mirr investment project is also used quite often. The modified internal rate of return MIRR is a rate in the discount factor that takes into account and balances the inflows and outflows of funds for the project. The use of this coefficient allows us to obtain a more objective assessment of the reinvestment rate (see).

Where:

• A t – cash expenses incurred by the investor during the development of the project for period t;
• S – cash receipts from project development for period t;
• k – cost of capital of the enterprise;
• n – project duration.

The use of performance indicators in the real assessment of investment performance

Investments npv irr: examples of problem solving. Let's look at an example of calculating NPV and IRR indicators. To do this, we will presumably make a decision on the effectiveness of investing in the renovation of two apartments for the purpose of further renting them out.

The initial investment is the same for each project, but the profitability for each apartment will be different. At first glance, investing in apartment 1 is more profitable, since in three years the profit from the investment will be 1,800 thousand rubles, which is 200 thousand rubles more than the income from the second apartment.

Table 1 - Data for calculation:

Years	Apartment 1, thousand rubles.	Apartment 2, thousand rubles
0	1500	1500
1	600	700
2	600	700
3	600	200
Rental income	1800	1600

Which project will be more profitable?

Let us accept the following simplifications:

• discount rate is 10%;
• the investor receives income at the same time at the end of the year;
• Investments are made at the beginning of the year.

Of course, in real projects you will have to take into account all the nuances and carry out calculations based on actual data, since otherwise you can get distorted data that will not allow you to make the right decision.

Let's calculate NPV for the first apartment:

Let's calculate NPV for the second apartment:

Conclusion: Both projects will be profitable, but the first project will bring higher profits. But, as already noted, the benefits resulting from the calculation are ambiguous. If projects are carried out during a period of high inflation, then the profitability of the first project is not at all obvious, since the value of money will depreciate. From this point of view, the second project will be more profitable.

We will calculate the investment irr in the Excel spreadsheet editor. As a result, we get: for 1 apartment IRR = 9.7%, and for the second IRR = 3.9%. Therefore, investing in the renovation of your first apartment is more profitable. As you can see, calculating the efficiency indicators npv irr of investment projects helps to make the right choice.

The modified rate of return indicator is used when it is necessary to reduce the impact of investments in calculations. Continuing the calculation according to our example using a table editor, the following values ​​were obtained: for the first apartment MIRR=9.8%, for the second apartment MIRR=6.5%.

Consequently, this coefficient confirms that investments in the renovation of the first apartment will yield greater profits. But, as you have already noticed, when using the modified coefficient the values ​​turned out to be higher.