6 considerations when calculating the NPV in Excel.

By Nick Crawley on June 1 2009

Last week I was asked, more times than usual in a week, “what is the best way to calculate net present value?”. My answer, as you might have predicted by now, is that it depends on the circumstances to be taken into account,

do you (and your models users) fully understand how the Excel function NPV or XNPV works?
do we have 1 minute or 3?
will this need to be audited
what level of understanding do the likely users have

The possible methods are

From first principles
Using NPV
Using (X)NPV
…and theres always somebody that claims to be able to do it in their head!

My preference for calculating NPV

If I had time my choice is to calculate from first principles, the reason for this is that to do this requires one line of Excel code more than using an Excel function and allows me to sense check every aspect of it. My philosophy is that if you cannot calculate a discount factor you probably shouldn’t be calculating NPV anyway, manual or using a function!

My second choice would be to use the XNPV() function which is introduced nicely in this tutorial from model audit specialists, Corality. NPV, without the X, is best avoided, if you are doing financial modelling the numbers are probably big enough to do it properly and take the days into account and generally take care.

Either way, when calculating the NPV of your project

ensure to show the units and the discount rate clearly
spend as much time checking and stressing the calculation (in fact no more !) as you do building it.
presenting it clearly, maybe at a range of discount rates
clearly show if it is pre or post tax, real or nominal
communicate the shape of the NPV(discount rate) profile, maybe there is more than one root to NPV(x)=0.
identify if it is a Project NPV or an Equity NPV (for geared projects)

A substantial benefit of using the XNPV function is that it can be copied and used to calculate the NPV at different discount rates, this is true but is not a good enough reason to use a blackbox function, which is easy to get wrong, to perform a very simple calculation. If this is important to you then a quick 1D datatable with discount rate as the vertical parameter will provide the same output and can be easily tested.

Coming soon ! a Navigator tutorial on calculating the NPV of a set of cashflows

We will post two tutorials on calculating the NPV and IRR shortly, so if you are interested, stay tuned or register your interest through our inquiry form.