When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the NPV errors, in order of riskiness, when auditing an Excel Spreadsheet. Note that these errors are less of an issue with XNPV, which is why we try and use XNPV rather than NPV.
NPV treats the first period as Period 1 even if it is Period 0
Less a spreadsheet error and more misunderstanding how Excel is using a function, the NPV function assumes that all the values you give it happen at the end of the period, so even the first one is assumed to be the end. If you have annual cash flows, on a calculator you have the option of entering P0 (period 0) and the calculator treats it as the starting cash flow happening immediately. Excel’s NPV function treats the first cash flow as happening at the end of the period which generates an incorrect result especially if using annual cash flows.
As shown below, we can get all the NPV formulas in a spreadsheet listed in a central report using the Excel Auditing Tool.
If you look at the NPV calculation as shown below, cell B10 is wrong as it includes the first value in the NPV. The correct way would be to only include C6 to E6 in the NPV and then add the amount in B6. As shown there is a 10% difference in the result.
Matching the discount rate period to the periods modelled
The other common error with NPV is forgetting to divide the discount rate to match the cash flow periods. If you have annual cash flows, then you don’t need to divide the discount rate. But if you have monthly cash flows, you need to divide the discount rate by 12, and quarterly would be divided by 4.