What is Sensitivity Analysis?
Sensitivity analysis is a frequently used analysis tool in financial modeling that enables an analyst to gauge the impact of any changes in critical variables on the output.
Sensitivity Analysis in Excel
Excel contains various inbuilt features, such as data table, scenario manager, and solver, which allow us to perform sensitivity analysis.
Data table is the most widely used tool to perform sensitivity analysis.
One-Variable Data Table (1-dimensional) to calculate the NPV value
To evaluate the NPV of any project, we are generally faced with multi-period cash flows and a single discount rate. To better analyze the NPV of a project under different discount rate scenarios, we can use data tables. This single-variable analysis will help us understand the delta on the NPV with marginal changes in discount rate.
Step 1: Calculate the NPV of a project in cell #D10 using the XNPV function assuming a discount rate in cell #D4
Input the list of discount rates that the analyst wants to evaluate in the input cells#D16:D20
Step 2: After calculating the NPV, let’s assume the analyst wants to see how the NPV gets impacted by changing the discount rate. To get the desired output, the analyst can select data tools from the ‘What if’ analysis and use the one-variable data table to perform sensitivity analysis.
- Enter the formula of the NPV in cell #E15
- Select the range of cells that contain the formula and the desired input values, i.e., select the range #D15:E20
- Insert the data table by inputting the column input cell as #D4; as a result, the NPV values at different discount rates would automatically appear in cells #E16:E20
Interpretation of one-variable sensitivity analysis:
- As shown in the above analysis, the net present value of the project cash flows at a discount rate of 10% is $654.1. As the discount rate increases, the NPV of project declines; however, the decline is not material in this case, and this adds credibility to the analyst’s projections. Thus, a 50-bps increase in discount rate decreases the NPV of a project by roughly 1%.
Two-Variable Data Table (2-dimensional) to calculate the impact on IRR
This type of sensitivity analysis is used when an analyst wants to analyze the impact of changes in two different variables simultaneously.
Let’s consider an example where we need to calculate the impact of changes in the exit year and the exit multiple on the IRR. To analyze the impact of the above two variables, the analyst would use a two-variable data table to perform sensitivity analysis.
Enter the list of exit years that the analyst wants to evaluate in row #D30:G30 and the exit multiples in column #C31:C35
Step 2: After calculating the IRR, let’s assume an analyst wants to see how the IRR gets impacted by changing the exit multiple and exit year
- Type the IRR formula exactly above the column variable and beside the row variable, i.e., in cell #C30 (note that the output cell is at the intersection of the variables in the table)
- Select the range of cells that contain the formulas and the desired input variables. Select the range #C30:G35
- Insert the data table by inputting the row input as #C4 and the column input as #C5. The result, i.e., the IRR, would automatically be updated in row #D31:G35
Interpretation of two-variable sensitivity analysis:
- The calculation of IRR is quite sensitive to the exit multiple and the exit year. Consider the data table showing the sensitivity of IRR, with changes in the exit multiple and exit year
- An investor seeking to exit investments in December 2018 at an exit multiple of 6.5x can expect an IRR of 20.1%
- As shown in the above analysis, the returns from investment increases ~5%, with an increase of 0.5x in the exit multiple for an investor with an exit in December 2018