Do a Sensitivity / What-If Analysis in Excel

This tutorial demonstrates how to do a sensitivity analysis in Excel.

A sensitivity analysis involves entering a variety of financial scenarios to see which one is the most economically viable. In Excel, you can use single- or double-input data tables for your analysis.

Single-Input Data Tables

For a single-input data table, you change one variable in a table to see the results, known as a What-If Analysis. For example, take a loan where the amount is $2,000, the interest rate is 6%, and the payments are spread out over 12 months. The monthly payment amount is calculated in cell C8 using the PMT Function:

=PMT(C5/12, C6, -C4)

You can calculate hypothetical payment amounts – based on a constant rate and variable terms (6, 9, 18, and 24 months) – to see which scenario is best for you.

1. To run a What-If Analysis, highlight the partially blank data table –with rows for each hypothetical term (in months).

2. In the Ribbon, go to Data > What-If Analysis > Data Table…

3. You want to fill the table in, replacing the original term with the “what-if” term and show corresponding payments. So, for the Column input cell, set C6 (the original 12-month term).

4. This changes the formula in cell C8 to pick up each value in Column B (i.e., 6, 9, 18, and 24) in place of the value 12 in C6. Click OK.

5. The table is filled in with the new scenarios to show you how much the monthly payment would be should the term of the loan be adjusted.

Double-Input Data Tables

A double-input data table lets you adjust two variables. So, as well as changing the term in months, you can change the interest rate applied to the loan. You can therefore have both row and column input cells instead of just one or the other.

Consider the worksheet below.

In this scenario, both the term and the interest rate are going to be variables. In the PMT formula held in C8, the value in C6 is replaced by the row values in Rows 9 to 12; and the value in C4 is replaced by the column values in Columns C to L.

1. First, highlight the data table.
2. Then in the Ribbon, go to Data > What-If Analysis > Data Table…

3. For the Row input cell, select C5 (the interest rate), and in the Column input cell, select C6 (the term).

4. Click OK. Excel fills in the scenario table, as pictured below.