How to calculate mirr using financial calculator

Capital Budgeting – Clarification Example
When people hear the term capital budgeting, they usually focus on the budgeting part of the term rather than the capital portion. Actually, capital is the more important aspect because it shows you that you are evaluating a larger expenditure that will be capitalized—in other words, depreciated over time. Remember, a capital expenditure can be many things—a large copying machine, an automated assembly line, a building, or the ultimate in capital budgeting—the acquisition of another entity. What is important about capital budgeting is it allows you to analyze one or more projects so you can intelligently and strategically decide on which project you wish to acquire or which piece of equipment you should procure.

There are at least six capital budgeting tools you can use in analyzing a capital expenditure: net present value (NPV), internal rate of return (IRR), profitability index (PI), payback period (PB), discounted payback period (DPB), and modified internal rate of return (MIRR), although the textbook mainly focuses on net NPV and IRR. In a prior finance course, you might have learned how to calculate four of the six tools—NPV, IRR, PI, and PB. If not, then this will be new material for you.

Crunching the numbers might seem by some to be the more crucial part—and it is indeed important. However, interpreting and analyzing the answers are just as important. See if you can do this with the six capital budgeting tool answers that you will be computing in the following example.

Example
Suppose you are thinking of acquiring either the ABC or the XYZ Company. Both have a purchase price of $500K so you cannot readily see which choice would be in your best interest. You also have a capital restraint of approximately $500K so you cannot purchase both entities. Thus, you provide your accountants and analysts with the historical financial details of both companies. They spend a few days forecasting 5 years of detailed financial statements based on how your company would operate these two corporations. The following are the results that ended with the projected 5-year net cash flow figures. Year 0 shows the initial cost outlay (or purchase price), and years 1 through 5 show the projected cash inflow if you make the purchase.

0

1

2

3

4

5

ABC

-500

100

200

575

325

100

XYZ

-500

275

250

75

250

450

It is an interesting coincidence to note that if you total both rows for each company, they are the same. However, you know that this does not matter in that comparing totals ignores the time value of money and is not a valid capital budgeting tool in making strategic decisions for your firm.

Let’s first look at two of the more popular capital budgeting tools, NPV and IRR. As you are doing, you always look at the projected cash flows for each project—not net income. To compare the projects on equal terms, you bring back the future cash flows to the present, which is the present value concept. Then, you subtract the cost of that project from its present value—thus, NPV.

Before you start this process, you need a discount rate—the interest rate used in the NPV formula. Sometimes it is called the hurdle rate or required rate of return. It is usually the cost of capital—sometimes with a risk factor added, if it is a risky project. The cost of capital is used because you want this project to at least make more than what capital is now costing you to run your business—otherwise, the project will lose the firm cash. In this example, you are using a discount rate of 10%. Thus, if your NPV is positive—it is a good project. If it is negative, it is considered a poor project. In comparing projects, you want to pick the one with the highest NPV.

Net Present Value
ABC

$472.28

XYZ

$463.13

As you can see (you may check these figures with your spreadsheet program or financial calculator), ABC has a slightly higher NPV. Therefore, ABC would be your choice based on this one capital budgeting tool. You should note that NPV is considered the most superior capital budgeting tool.

Now, how would you interpret or define your NPV answer? A textbook might define NPV as the present value of future cash returns, discounted at the appropriate interest rate, less the cost of the investment. If you explain it that way to most people, they might give you a blank stare. Yes, you should pick ABC because its NPV is higher than XYZ. However, here is the key interpretation that all will understand—ABC will be giving you, over 5 years, a current value cash return of approximately $472.3K above your 10% required rate of return. In other words, this project will not only meet your 10% required return, but it will give you an additional $472.3K.

The next question is, what total percentage return does the dollar amount represent? This is exactly what IRR tells you. The IRR calculations are as follows:

Internal Rate of Return
ABC

38.58%

XYZ

40.01%

Therefore, your total current valued percentage return on your investment for ABC = 38.58%. IRR is a percentage that will go with NPV most of the time.

NPV told you to choose ABC Company. Your IRR computations are giving you conflicting directions telling you to choose XYZ Company. Consider that IRR can have two problem areas—more than one negative in the cash flow, or if there are large fluctuations in cash flows from year to year. Either of these two problems can result in non-accurate IRR answers. If you noticed, there were some large fluctuations in your cash flow. If you get an IRR decision that conflicts with NPV, you always choose the higher NPV.

Here is an exercise to give you an idea of how NPV is related to IRR. Suppose you are using a discount rate of 10%. If you have an NPV of exactly $0, your IRR will be 10%. If your NPV is above $0, your IRR will be above 10%. If your NPV is below $0, your IRR will be below 10%. You can prove this by taking ABC’s IRR of 35.58% and using it as the discount rate in the NPV formula. Recalculate NPV using the 35.58% discount rate and you will get an NPV that is very close to $0.

You know that IRR can have its problems—this is why they came up with MIRR. MIRR solves the problems that may occur in IRR computations. MIRR can be calculated automatically with some financial calculators and it is also an fx function in Microsoft® Excel®. Here are the results of your example:

Modified Internal Rate of Return
ABC

25.65%

XYZ

25.41%

As you can see, MIRR’s capital budgeting decision matches that of the calculated NPV. Company ABC is slightly higher than company XYZ. You can also calculate MIRR using just the basic time value of money functions of a financial calculator. There is an example of this at the end of this document.

PI is another tool. It goes together with NPV and never conflicts with NPV’s findings. The easiest way to calculate PI is as follows:

PI = PV/ICO, that is, present value divided by the initial cost outlay. Most textbooks use this formula; however, some textbooks use the formula PI = NPV/ICO, PI = net present value divided by the initial cost outlay.

The simplest way to get PV is after you have calculated NPV. PV = NPV + ICO. In your example using ABC company, it would be PV = 472.28 + 500 = 972.28. Now you can calculate PI = 972.28 / 500 = 1.94.

Profitabilty Index
ABC

1.94

XYZ

1.93

Remember, PI is just an index number—anything 1.0 and higher is affirmation for the project you are evaluating. So the next question: If PI goes hand in hand with NPV, then why do you even need to calculate PI?

PI is a type of ratio that gives the highest NPV per dollar of investment. PI comes more into play when you are comparing many projects. One textbook describes it as:

The PI is sometimes used to rank projects even when there is no soft or hard capital rationing. In this case the unwary user may be led to favor small projects over larger projects with higher NPV’s. The PI was designed to select the projects with the most bang per buck—the greatest NPV per dollar spent. That is the right objective when bucks are limited. When they are not, a bigger bang is always better than a smaller one, even when more bucks are spent (Brealey, 1988, p. 201).

PB method is the most inferior of all basic capital budgeting tools because it does not consider the time value of money—for example, if a project will cost $500 to start and is projecting $100 per year cash inflow for the first 10 years, then the payback period is 5.0 years—the amount of time it takes the cash inflow to pay for the original cash outflow or cost of the project. Of course, this ignores the time value of money treating a dollar in any future year the same in value as a dollar today.

The decision-making criteria for the payback method is going with the project that pays off the initial cost outlay in less time. As you can see by the following calculations, you have another conflict. Your NPV indicated that company ABC is the correct choice; however, PB states that company XYZ is the choice. You can see that not only does the PB method ignore the time value of money, but it also does not consider all years of the cash flow projection. For example, XYZ showed that its payback was in less than 2 years ignoring the last 3 years as being a factor. Note. There will be an example of an easy way to calculate PB at the end of this document.

Payback Period
ABC

2.35

yrs

XYZ

1.90

yrs

The last tool to introduce is discounted payback period (DPB). While this method also ignores some of the future cash flow projections much like the PB method, it does consider the time value of money. The DPB is computed in a similar fashion as the PB method—the only difference is that the DPB method uses the discounted cash flow. As you can see in the following, factoring in the time value of money makes the decision in line with the NPV’s outcome—ABC is the choice!

Discount Payback Period
ABC

2.56

yrs

XYZ

2.77

yrs

The following is a quick glance summary of the capital budgeting tool answers—note that the preferred choice based on each capital budgeting tool’s criteria is highlighted:

ABC

XYZ

NPV

$472.28

$463.13

IRR

38.58%

40.01%

MIRR

25.65%

25.41%

PI

1.94

1.93

PB

2.35

yrs

1.9

DPB

2.56

yrs

2.77

Remember that if there is any question as to the validity of the calculations, NPV should always be used as the final decision factor.

Example MIRR Calculation Example and Hints
Discount rate = 10% Projected Cash flow years 0 1 2 3 4 5 Project A (500) 45 55 65 175 185

First, you must take all of years (except year 0) out to year 5 using the future value function of your financial calculator.

MIRR takes everything out to the future first before discounting it back to the present. Take year 1 for example—to get this year’s value out to year 5, you must use the future value (FV) function: PV = 45 i = 10 n = 4 because it is 4 years to get to year 5 Solving for FV = 65.68 FV - YR5 = 185.00 FV - YR4 = 192.50 FV - YR3 = 78.65 FV - YR2 = 73.21 FV - YR1 = 65.88 Terminal value (total of years 1 through 5) = 595.24 Once this process has been done for each year, you add them to get what is called the terminal value. Now, you have all of the numbers to calculate MIRR: FV = 595.24 PV = (500.00) n = 5 Solving for i = 3.55% MIRR is 3.55%, which will almost always be more conservative than IRR. Of course, because MIRR is below your required rate of return, it is a thumbs-down on this project. Make sure that you calculate the above answer to see if you can come up with the same solution. Remember, always make sure your PV is entered as a negative, or you will get an error when solving for i.

Payback Calculation Example and Hints
Suppose you had 5 years of cash flow as follows (year 0 being the initial cost outlay):

YR0 = -500 YR1 = 120 YR2 = 100 YR3 = 110 YR4 = 100 YR5 = 280

You would always start with the initial cost outlay and then subtract every year until you are at the last year that would put your total below zero:

500 - 120 - 100 - 110 - 100 = 70

The reason to not subtract year 5 is because that would put you below 0.

Now you know that the payback is 4 full years plus a fraction. How do you find the fractional part of the 5th year? You take the leftover 70 and divide by the 280 in year 5—this gives you a fraction of .25.

Therefore, the payback = 4.25 years.

Note that discounted payback is calculated in the same manner, but the discounted cash flow would be used.

Reference
Brealey, R. A.,Myers, S. C., & Marcus, (2007). Fundamentals of corporate finance (5th edition).New York, NY:McGraw-Hill International.