How to Perform an Interest Rate Sensitivity Analysis in Excel – 4 Steps

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.

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.

Read More: How to Calculate Effective Interest Rate On Bonds Using Excel

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.

Download Practice Workbook

Download the workbook.

Related Articles

• Create Flat and Reducing Rate of Interest Calculator in Excel
• How to Perform Interest Rate Swap Calculation in Excel

<< Go Back to Interest Rate Calculator | Finance Template | Excel Templates