EXCEL: Sensitivity Analysis

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 while performing sensitivity analysis.

One-Variable Data Table (1-dimensional) to calculate the NPV value

For evaluating 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 the discount rate.  

For example:

Step 1: Calculate the NPV of a project in cell #D10 using the XNPV function assuming a discount rate in cell #D4

Figure 1.

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 list of discount rates that the analyst wants to evaluate in the input cells #D16:D20
• 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

Figure 2

Interpretation of one-variable sensitivity analysis:

• The NPV calculation drastically changes with the change in the discount rate. Consider the data table showing the sensitivity of the net present value with a change in the discount rate.
• 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 the 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 the 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.

Step 1: Calculate the total cash flows available to equity holders in row #22. Calulate the IRR in cell #C24 based on the cash flows available to equity holders.

Figure 3

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 the exit year.

• Enter the list of exit years that the analyst wants to evaluate in row #D30:G30 and the exit multiples in column #C31:C35

• 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 update in row #D31:G35

Figure 4

Interpretation of two-variable sensitivity analysis:

• The calculation of the IRR is quite sensitive to the exit multiple and the exit year. Consider the data table showing the sensitivity of the IRR with changes in the exit multiple and the exit year.
• An investor seeking to exit investments in Dec 2018 at an exit multiple of 6.5x can expect an IRR of 20.1% 
• As shown in the above analysis, the returns from the investment increases by ~5%,  with an increase of 0.5x in the exit multiple for an investor with an exit in Dec 2018