IRR and XIRR not giving required answer

[HarryFröhlich]

Hi everybody!

I have something that has me completely stumped and am hoping that somebody can help me find an answer.

The scenario is the following:

Coal reserves in a coal mine are up for grabs;
Investors have to pay a certain amount of money to get a certain share in the company that will mine the coal;
The rarred spreadsheet (14.2 KB) can be downloaded from here: http://www.gpforum.co.za/example.html (I cannot see how to turn this into a link - sorry!);
The investor will be making the kind of profits indicated in the spreadsheet;
I used both IRr and later XIRR to calculate the return on the investor's money

BUT still do not reach the same answer when I check the returned return with other calculations.

For example, the XIRR calculations show that the investor achieves an annual return of 180% for a specific grade of coal. The project runs for 2 years.

When I take the original investment amount ($X) and use the XIR-determined return rate against it by doing the following ($X * (1+180%))^2), it gives a completely different answer to the actual return.

The rate that XIRR function does not seem to be the right one to use. In this scenario, the initial investment "returns" a series of future cash flows as follows:

The initial investment (out) beginning month 1
An outflow of $y beginning month 2 (expenses to set the operation up - no income earned)
Another outflow ($y) beginning month 2 and month 3
Then, in month 4, the outflows still happen, but the inflows begin.
In month 24, the mining ceases (coal deposits depleted), but money continues to come in until month 27.

The original investment should actually be shown as an "income" in month 27 (because it is to be paid back to the investor as a loan account), but I have not yet done it yet.

Could someone please see what the heck I am not doing right, please?

I gather that XIRR is simply not the right function to use ... Should I use Goal Seek?

Thanks!

Harry

 

[Andrew Fergus]

Hi Harry

I haven't downloaded your example, but you can check your IRR is correct by using the formula:
=NPV(B1, A1:A10)
where B1 contains the rate determined by the IRR function and the range A1:A10 contains the undiscounted cash flows.

If the NPV = 0 then the IRR is correct.

The formula Investment*((1+i)^n) is a compound interest calculation which is a completely different thing. Unfortunately you are comparing an apple to an orange.

The IRR is the periodic interest rate necessary to discount a series of future cash flows such that it has a net present value of zero. In other words, if I could yield 7.8% per period elsewhere then I would be indifferent to an investment that offered me $10/period for 20 periods for an initial investment of $100. If I could only yield 5% per period elsewhere then 7.8% per period is a great invesment - conversely if I could yield 10% per period elsewhere then 7.8% is not such a good investment.

If you go through the process of applying the discount factor to each periodic cash flow then you can prove the NPV, and hence prove the IRR. The formula to apply to each periodic cash flow is : =CashFlow/((1+i)^n), including the negative value for the initial invesment in period 0. The sum of the discounted cash flows will equal the NPV based on the IRR - hence it proves itself.

Cheers
Andrew

 

[Andrew Fergus]

I forgot to mention that an IRR is not an interest rate, it is a discount rate. Try applying a discount rate of the IRR, then greater than the IRR and then less than the IRR, to an investment that yields x% (say 5%). Where the discount rate exceeds the interest rate, it is a bad investment and vice versa. Where the discount rate equals the IRR then one would be indifferent to the investment. This is crux of the apple vs orange comment......

Cheers
Andrew