Sensitivity Analysis for NPV in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to perform sensitivity analysis in excel. Sensitivity Analysis is a tool to analyze the impact of a change in an independent variable associated with a product. This is a method used in financial modeling to study the uncertainties in the output of a product associated with the uncertain changes within the inputs related to that product. This article will guide you through easy steps to perform sensitivity analysis for net present value or NPV in excel.


What Is Sensitivity Analysis?

Sensitivity Analysis is done to determine the impact of a change in an independent variable associated with a product. This is a method used in financial modeling to study the uncertainties in the output of a product associated with the uncertain changes within the inputs related to that product.


Sensitivity Analysis for NPV in Excel: with Easy Steps

Assume you have a three-year project running under your supervision. The revenue from the project mainly depends on 3 variables: selling price, variable cost, and annual sales. You need to know how changing these variables will affect the Net Present Value or NPV of the project.

Follow the steps below to be able to do that.


📌 Steps 1: Calculate Profit Margin

  • First, enter the following formula in cell C13 to get the margin between cost and sales.
=D9-D10


📌 Steps 2: Estimate Annual Cash Flow

  • Then, apply the following formula in cell C14 to estimate the annual cash flow.
=C13*D11

Read More: How to Use What If Analysis in Excel


📌 Steps 3: Create NPV Table

  • Next, create a table showing yearly cash flows as follows. The numbers in brackets are negative.

  • After that, enter the following formula in cell C21 to get the NPV based on the expected cash flow.
=NPV($C$5,C18:C20)+C17

sensitivity analysis for npv in excel

  • Suppose you need to forecast how the NPV will change if the selling price falls to a certain minimum. Then change the formula in cell C13 to the following one.
=C9-D10

Note:

You can change only one variable at a time and keep the others as expected.

  • After that, extend the NPV table to get the NPV at the minimum selling price.

  • Now, change the formula in cell C14 to know what will happen if annual sales drop to a minimum. But, you should calculate the margin from the expected values.
=C13*C11

  • After that, change the formulas for a different variable one by one to complete the following table.

sensitivity analysis for npv in excel

Read More: What-If Analysis in Excel with Example


📌 Steps 4: Perform Sensitivity Analysis for NPV

  • Next, create another table to compare the change in NPV with the change in a variable. Then, apply the following formula in cell F24 to get the change in NPV resulting from the change in the variables.
=E24-C24

sensitivity analysis for npv in excel

Remarks:

You can see from the above result that the selling price is the most sensitive variable for the NPV. You can also calculate that a 20% increase in selling price may increase the NPV by more than 700%.

Read More: How to Build a Sensitivity Analysis Table in Excel


Things to Remember

  • You should change only one variable at a time and keep others as the base value.
  • Don’t forget that the numbers in brackets are negative.

Download Sample Workbook

You can download the free template from the download button below.


Conclusion

Now you know how to perform sensitivity analysis in excel. Hope this article helps you with this. Do you have any further queries or suggestions? Please let us know using the comment section below. Stay with us and keep learning.


Related Articles


<< Go Back to What-If Analysis in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo