The article shows how to perform one and two variable **sensitivity analysis **in Excel. **Sensitivity analysis** studies how different sources of uncertainty can affect the final output in a mathematical model. Fortunately, we can do one and two variable **sensitivity analysis **in Excel by using some simple formulas. Please stay tuned for the rest of the article to get to the topic.

## Download Practice Workbook

## 2 Examples to Perform One and Two Variable Sensitivity Analysis in Excel

Recently one of my friends borrowed money from a bank to buy a house. And I was asked to determine how monthly payment will vary as the total amount borrowed or term length changes.

At that time, what-if analysis in Excel came to my mind as it allows me to quickly try out different values for formulas.

Here are the details about the mortgage. My friend planned to borrow **$200,000** and the interest rate is **3.40%**. The mortgage term is **30** years which means **360** months.

To start off, I only applied a one-way data table to see how the changes in mortgage amount will affect the mortgage payment. The Excel file applied just looks like below.

I have all the necessary inputs – mortgage amount, interest rate, and months – in section 1 and the data table in section 2.

Initially, I used the following formula into cell **C7**, and you can see that my friend needs to pay **$886.96** every month. Then a range of mortgage amount was put down column **B** from cell **B8** (shown below).

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

### 1. Example of One Way Variable Sensitivity Analysis in Excel

We need to rearrange the data first to do the one way variable **sensitivity analysis**. Let’s go through the following sections for further discussion.

**Steps:**

- First, I selected the table ranges (cells
**B9: C16**in this case) which begin one row above the first input value (row 9) and the last row is the row containing the last input value (row 16). - The first column in the range is the column containing inputs (column B) and the last column is the column including outputs (column C). Then clicked the
**Data**tab on the ribbon, chose**What-If Analysis,**and then clicked on**Data Table**(see below for details).

- A
**Data Table**dialog box (shown below) prompted after clicking on Data Table. Since our table is column-oriented, I just put the column input cell reference**$C$4**and leave the row input cell blank.

- By clicking
**OK**, I got the below results. When the mortgage amount decreases from**$200,000**to**$140,000**, the monthly payment will decrease from**$886.96**to**$620.87**. And for the other variable mortgages, we see the monthly payment also.

- I also made a one-way data table to see the impact of changes in terms of the
**months**. You can see from below that my friend needs to pay about**$1,419**every month if she plans to pay off in**15**years.

### 2. Example of Two Way Variable Sensitivity Analysis in Excel

We also would like to know how monthly payment varies as total mortgage varies from **$140,000** to **$260,000** (in **$20,000** increments) and term length varies from **5** years to **35** years (in **5** years increments).

**Steps:**

- First, we changed two inputs, and therefore we created below two-way data table.
- We put the mortgage amount values down the first column (Column B) of the table range and term length values in the 7
^{th}row.

There is one point that I have to remind you of. A two-way data table can have only one output cell, and you must place the formula for the output in the upper-left corner (B9 in this case) of the table range.

As with a one-way table, we selected the table range (cells B9: I16) and clicked the **Data **tab. In the **Data Tools** group, chose **What-If Analysis** and then select **Data Table**. Since there are two sources of uncertainty and therefore, I need to fill both rows input cell and column input cell. In our case, cell C4 (mortgage amount) is the column input cell and cell C6 (mortgage term length) is the row input cell.

- In the
**Data Table dialog box**, we put the cell reference “**$C$6**” in the**Row input cell**section and “**$C$4**” in the**Column input cell**section.

- After clicking
**OK**, you see the two-way data table shown as following. As an example, in cell C8, when the mortgage amount is**$140,000**and term length is**5**years or**60**months, the monthly payment equals to**$2,540.58**.

**Read More:** **How to Build a Sensitivity Analysis Table in Excel (With 2 Criteria)**

## Things to Remember

Finally, I have several notes for you:

- First, as you change input values in a worksheet, the values calculated by a data table change too. For example, if you increase the interest rate from
**3.40%**to**4.00%**, the monthly payment will be**$2578**(number in a blue square) given**5**years**$120,000**mortgage. When comparing with the above figure, you can see from the below screenshot that all values in this two-variable data table have been changed.

- Next, you cannot delete or edit a portion of a data table. If you select a cell in the data table range and edit accidentally, the Excel file will prompt a warning message and you can’t save or change or even close the file anymore. The only way that you can close it is by ending the task from task management. It causes time waste if you did not save the file before making that mistake.
- Thereafter, though the column-oriented one-way table (which is also applied in this post) is used popularly, you can also make your own row-oriented one-way table by yourself. Just put inputs in one row and only fill row input cell reference in Data Table dialogue box.
- After that, when making a two-way data table, do not mix up your row input cell and column input cell. This kind of mistake can result in a big error and nonsensical results.
- In addition, Excel enables the automatic calculation by default and that’s the reason why any change in the inputs can cause all data in the data table to recalculate. This is a fantastic feature. However, sometimes, we’d like to disable this feature especially when the data tables are large and automatic recalculation is extremely slow. In this situation, how can you disable automatic calculation? Just click the
**File**tab on the ribbon, choose**Options**, and then click the**Formulas**tab, select**Automatic Except For Data Tables**. Now you can recalculate all your data in the data table only when you press the F9 (recalculation) key.

## Conclusion

After reading thoroughly the description above, we can consider that you will learn how to analyze one and two variable **sensitivity analysis **in Excel. If you have any better suggestions or feedback, please leave them in the comment section.