Formulas discussed: NPV, XNPV
The workbook used throughout this article is available here.
In a previous post we looked at how Excel calculates NPVs and IRRs and some of their weaknesses. In this post we’ll show you how you can manually calculate an NPV so that you have greater flexibility and control, and know exactly what kind of answer you’re getting.
The first part of the workbook is a refresher on how NPV and XNPV produce different results, based on their different assumptions of the start date. XNPV is useful if you are not dealing with constant, annual periods, and means you don’t have to separately calculate a discount rate for the particular period length if you start with an annual rate.