Adjusted MIRR for time and scale difference

The post is continuation of earlier article NPV, IRR and their variants. For capital budgeting  Internal Rate of Return (IRR) is popular measure, however,  Net Present Value (NPV) method is preferred by academics since the rankings of mutually exclusive projects by IRR may not always select the project which will maximize the value of the firm, due to an implied reinvestment rate assumption by IRR. This weakness was removed by the Modified Internal Rate of Return (MIRR), the results are usually consistent with NPV.

Sometime back, I was solving capital budgeting problem and MIRR results were not consistent with NPV. After some research I found MIRR may also lead to erroneous rankings when projects have different initial outflows to start the project i.e. the scale problem, or the projects have different lives i.e. the time span problem. So here is the solution.

Scale Difference :

Imagine two projects with cashflow as shown.

2018-04-27_1505_1

As you can see the initial outlays are very wide and hence the MIRR results are inconsistent. The logic behind this is the acceptance of the smaller project also implies the acceptance of a shadow investment, equal to the difference in size between the smaller and larger projects, which earns the cost of capital. Therefore we make the shadow project with initial outlay of difference between big project and small project, with NPV of 0. Use Goal seek function of excel to make shadow project NPV 0.

2018-04-27_1507_2

After we add the shadow project and small project cashflow, and MIRR the results are consistent with NPV.

Timeline Difference:

For the project with the different timelines, use the life of the longest project.

2018-04-27_1544_3

Scale and Timeline Difference:

Imagine two projects with varying initial outflow and different timeline. The adjusted MIRR can be calculated by using the largest initial outflow and the largest number of periods.

2018-04-27_1603_4

This will give rankings that are consistent with NPV for mutually exclusive projects, even if the projects are of different sizes and in some cases, different lives.

Advertisements

NPV, IRR and their variants

Net Present Value (NPV), as the name suggest it is the sum of present value of outflow  and inflow cash discounted at a rate. Lets assume some Cashflow (CF) and find out the NPV using discount rate formula

2017-05-11_1210

At a discount rate of 8%, Present Value (PV) factor is calculated and multiplied with CF. The the discounted CF gives the NPV. The same can be achieved by using NPV formula as seen in the screen shot.

Internal Rate of Return (IRR) is the rate at which NPV is zero. If we separately find out the sum of NPV of outflow and inflow using a rate, we will get a value. Using Goal seek function of excel, we set the target value of sum to zero by changing the rate cell, we will get IRR in the rate cell. The same result can be achieved by using IRR function.

2017-05-11_1222

Now that we have learned the basics lets move forward. The NPV and IRR function assume the CF to happen annually, which may not be the case. For that we have functions like XNPV and XIRR at our disposal. Lets assume some random dates and find out the XNPV and XIRR.

2017-05-11_1232

We can observe the difference between two types of function.

People working in Project finance related assignment know that IRR is an optimistic number because the intermediate CF are reinvested at IRR, which may not represent the realty and hence making IRR a highly optimistic or inflated rate of return. To counter this we use Modified IRR (MIRR). MIRR considers both the cost of the investment and the interest received on reinvestment of cash.

Finance rate is the rate at which cash outflow is funded, usually cost of capital. Reinvestment rate is the rate at which intermediate CF are assumed invested, usually a mutually agreed conservative rate. Therefore MIRR represents a relatively fairer rate of return on the investment. Lets again assume some numbers and verify this.

2017-05-11_1258

We can see that MIRR is lower than IRR, when a conservative reinvestment rate is used. MIRR equals IRR when FR & RR equals IRR, which cannot be true in most cases.

But wait, again MIRR assumes annual cashflow, what if CF are not annual. And there is no direct function for it!

This requires a deeper understanding of how MIRR is calculated. Lets recalculate the MIRR in above example.

2017-05-11_1353

As you can see in above screen shot that outflow and inflow are separated. For outflow the NPV is found using Finance rate. For Inflow the future value is found using formula at time period 5. The MIRR is calculated using IRR function with NPV of outflow and total future value of inflow.

We have to use above logic to calculate MIRR with non periodic CF, lets call it Non Perodic MIRR (NP MIRR).

We calculate the present value of outflow using XNPV and future value of inflow using yearfrac function and formula. After getting the value we simply calculate the XIRR of CF, which gives us the NP MIRR. We can see that MIRR and NP MIRR are different.

Link to the file :

https://drive.google.com/file/d/0B2FLZ9YjqpJTMnJ3RGJlT2VSUFU/view?usp=sharing

Thanks!

Project Management (Time)

In Project Management, we essentially manage Time, control Cost without compromising Quality of the work. In today’s blog entry I present a worksheet which I made recently to manage time and cost of a Project.

Below you see the snapshot of Summary of the Report. The first two columns tells you about the number and name of ongoing Projects. Next 5 columns tells you about the financial status of the Project. Like Budget Amount vs Budget Consumed etc. The remaining columns tells you the status the different activities whether they are completed, ongoing or not yet started.

ProjStat1

However the activity status columns do not give us the detailed picture, the management may need. For that we need to refer next Project worksheets.

Below you see the snapshot of Project worksheet. For different activities Planned vs Actual dates are given. Based on dates the excel Gantt chart gets mapped, blue for planned and orange for actual. The dates are on weekly basis, can be put on daily basis. Filters are provided to view only planned or actual activities. % Done column is provided for the Manager to fill the % activity completed as on date.

PROJSTAT2

I have used conditional formatting for making the excel Gantt chart. The dates and values assumed are approx. User need to correct it as per his/her need. Hope you find it useful. Link to file:-

https://drive.google.com/file/d/0B2FLZ9YjqpJTdEhNdnNwcEVDYVk/view?usp=sharing

Other related entry :-

Gantt Chart