**Difficulty: **Easy

**Prize:** Free (and early) access to a new online course that we have under development and an F1F9 mug.

__Introduction__

Thanks again to all who took part in Modelling Challenge #4 on NPV. I think it yielded some fascinating results.

This week we move on to the twin brother of NPV – The Internal Rate of Return or IRR.

Internal Rate of Return (IRR) is a key metric used by investors to calculate the attractiveness of a potential investment. Investopedia defines IRR as follows:

Internal rate of return (IRR) is a metric used in capital budgeting measuring the profitability of potential investments. Internal rate of return is a discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero.

You can estimate an IRR by using the NPV formula as a starting point.

where:

Ct = net cash inflow during the period t

Co= total initial investment costs

r = discount rate, and

t = number of time periods

To calculate IRR using the formula, set the NPV equal to zero and solve for the discount rate *r*, which is the IRR.

__The Challenge__

While investors routinely use IRR as a key metric in decision-making, I have a sneaking suspicion that it is a minority of investors who could accurately describe how to calculate the IRR of a project.

The challenge is simple – calculate the annual IRR of the future pre-tax, pre-financing cash flows of the project.

If you are feeling adventurous, prepare your calculation without using Excel’s inbuilt functions.

__Instructions __

- 1. Download the template model from
__here (or above)__ - 2. Based on the cash flows in the model, found on the Cashflow sheet, calculate the IRR using the guidelines above.
- 3. If you are brave enough, post your answer (the calculated IRR). Otherwise, you can e-mail me your answer and calculations danny.leitch@f1f9.com
- 4. We will select the winner using our complex Excel-based randomiser
- 5. We’ll also post the answer in a couple of weeks.

I got 11.24% using XIRR

I get 11.244% using XIRR and 11.245% by converting the cashflow from semi-annual to annual and using IRR.

Using MIRR with the discount rate of 8% as the finance and reinvestment rate gets me a return of 6.138%…

For some reason the following text is missing in my original post: “…two scenarios when NPV is positive (discount rate IRR). If we model…”

The IRR comes to 11.24%. In addition to the approaches offered (the usage of XIRR function and the goal seek instrument), IRR can be calculated by using two scenarios when NPV is positive (discount rate NPV). If we model the project’s cash flows using discount rate of, let’s say, 14% NPV would come to negative 41,667k. It’s worth mentioning that the original settings in the given model had the discount rate set at 8% which led to NPV of 90,958k. Thus, the IRR can be calculated as follows: (90,958k – 41,667k)/90,958k*(14%-8%)+8% = 11.25% which is very close to 11.24 calculated using the above mentioned function and the goal seek instrument.

My result differs. I got IRR 4.20% and when I compare with the above results I realized I did something wrong……I need more practice 🙂

Hi, I definitely did something wrong because my result is 4.20%. I followed the instruction of Mary Pat Campbell but the result is not 11.24%……I need more practice 🙂

I also have 11.24% as the result using two methods:

1) Using XIRR and adjusting the range to remove the first zero cell (not really a FAST approach but excel has its downsides occasionally!!). This resulted in 11.24%

2) Using the NPV calculation but changing the 8% into an input cell and using goal seek to change the 8% so that the NPV equals zero.

IRR would 11.24%.

IRR comes to 11.24%.

And the IRR I get is 11.24%

I did it two ways:

1. XIRR function — and you need to be careful, because XIRR doesn’t work when the first cashflow is 0. You have to start the function in the K column (31 Dec 2016) as a result. Not sure why XIRR is set up that way, but it is.

2. Using Goal Seek + XNPV — I copied over the XNPV block from the prior challenge, and used Goal Seek to change the discount rate until it got XNPV to zero. This one, I could use starting with the 30 June 2016 date, as XNPV can handle 0 cash flows just fine.

I calculated it using goal seek but did not use a built-in formula. Simply, 1/(1+r)^n for calculating discount factor for each half.

I used the formula mentioned in the challenge for calculating the discount factor and then used goal seek to arrive at IRR of 11.24%.

Yes, 11.24% using XIRR and Goal Seek.