How to Perform an Interest Rate Sensitivity Analysis in Excel

What Is the Interest Rate Sensitivity Analysis in Excel?

The Sensitivity Analysis is used to estimate possible outcomes of real-life projects by changing input variables.

Step 1 – Select a Dataset to perform the Sensitivity Analysis

This is the sample dataset: John takes a loan from a bank: the Loan Amount, Interest Rate, and No. of Installments is showcased. 7 different Variable Interest Rates were used to perform the interest rate sensitivity analysis in Excel.

Step 2 – Calculate the Monthly Payment

Select C9 and enter the following formula to find the monthly installment amount.

=PMT(C5/12,C6,C4)

The PMT function determines the periodic loan payments for a fixed interest rate, time period, and present value of the loan.

Press Enter to calculate the Monthly Payment.

Step 3 – Calculate the Monthly Payment for Variable Interest Rates

Select B9:C16.
Select the Data tab and choose Forecast.
Select the Data Table in What-If Analysis.

In the Data Table, select C5 in Column input cell.
Click OK.

It ill show the monthly payment amount for different rates.

Step 4 – Final Output

You can see how the monthly interest rate influences the monthly payment.

Things to Remember

Changes in the inputs will result in changes in the final calculation.
You can’t edit or delete part of the output range. You’ll get a warning message.
You can also create a row-oriented one-way table.