What are Net Present Values (NPVs) and Internal Rates of Return (IRRs)? Rather than reaching for complicated definitions, I prefer to explain them in terms of a loan schedule or investment.

Invest £100 in a bank at an interest rate of 5% and you will receive a particular stream of cashflows, depending on when you take the money out. For example, let’s say the money is repaid at the end of year 4. Therefore, you would receive £5 for 3 years and then £105 in year 4.

If you calculate the NPV at 5% of this stream of cashflows (£5, £5, £5, £105) you will get a value of £100. Is that surprising? It shouldn’t be as £100 was the original investment. An NPV therefore tells you the amount you need to invest at a certain interest rate to achieve a particular stream of cashflows.

If you calculate the IRR of -£100, £5, £5, £5, £105 you will get 5%. Is that surprising? It shouldn’t be, as 5% was the interest rate of the investment.

Replace the above example with whatever repayment schedule you wish, and the answers will always give an NPV of £100 and an IRR of 5%. Of course, that is on the proviso that you calculate the NPVs and IRRs correctly: if you don’t then these relationships will not be true. And there are plenty of dubious calculations of NPVs and IRRs out there.

Remembering the three-way relationship between investments, NPVs and IRRs is key to good financial analysis. Looking at an IRR/NPV problem from a different angle can often help to reduce needless over-complication in financial models. And, as ever, there is no need here for the inclusion of circular references, iterative macros or goal seek.