IRR and NPV workbook

IRR (internal rate of return) and NPV (net present value) are discounted cash flow approaches and are widely used for investment appraisal. Whilst they are based on the same ingredients, the results differ, so you’ll need a methodology to be able to compare the two.

Internal rate of return (irr)

IRR is the return, expressed as a percentage, of a future series of cash flows. It’s calculated using a formula which you can obtain in MS Excel. To calculate the IRR of an investment, you need to know the cost of the initial investment and the net returns from the investment over a defined time period. An investment with an IRR greater than the cost of capital to the business would add value. Use sheet 1 of our IRR and NPV Workbook to see if your project passes or fails this test.

Wherever possible, you should use the weighted average cost of capital (WACC) to compare to the IRR as this ensures you cover the loan interest and dividend returns to the shareholders. The most common alternative, however, is to use the rate paid by the company to borrow the funds to be invested.

Warning! Investments with the highest IRR may be adopted even though the returns are less than the cost of capital. IRR should not be used to compare investments against each other because this could result in selecting a project with a higher IRR but a lower NPV.

Net present value (npv)

NPV is the present value, expressed in money, of a future series of cash flows. The NPV is also calculated using a formula which you can obtain in MS Excel. To calculate the NPV of an investment you need to know the cost of the initial investment, the net cash returns from the investment over a defined time period and the discount rate (use the weighted average cost of capital).

An investment with an NPV greater than zero should add value to the business and be suitable for progressing whereas an investment with an NPV of less than zero would lose value for the business and be unsuitable to progress. Use sheet 2 of our IRR and NPV workbook to see if your project passes or fails this test.

Unlike IRR, NPV can be used to compare alternative investments, and you can recommend those investments with the highest positive NPV.

Warning! NPV does not take account of underlying risks, for example that the cost of the investment may be greater than estimated or that the returns may be lower than estimated. The simplest way of dealing with risk is to apply sensitivity analysis to the investment cost, the net cash returns or the time period used, e.g. an investment may return a positive NPV over a three-year period but a negative return over a five-year period.