NPV Sensitivity Analysis: A Dynamic Excel Approach

Abstract

Financial analysts generally create static formulas for the computation of NPV.  When they do so, however, it is not readily apparent how sensitive the value of NPV is to changes in multiple interdependent and interrelated variables. 

It is the aim of this paper to analyze this variability by employing a dynamic, visually graphic presentation using Excel.  Our approach illustrates how these variables, when increased or decreased to reflect the potential range of values in a business case, change the value of NPV, and hence affect the decision about whether to proceed with the project or to reject it. 

Furthermore, since sales revenue is one of the least certain elements in the business case, the presentation includes a probability estimate of whether NPV will be positive or negative, assuming that sales revenue is normally distributed with a known mean and standard deviation. 

The business case we have chosen for illustrative purposes is a global energy project. Nevertheless, financial analysts in any industry should be able to apply our dynamic spreadsheet approach to their projects as well.