How to Perform Interest Rate Sensitivity Analysis in Excel

Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can perform interest rate sensitivity analysis easily in Excel for better representation. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you a step-by-step procedure to perform interest rate sensitivity analysis in Excel. Hence, read through the article to learn more and save time.

What Is Interest Rate Sensitivity Analysis in Excel?

Sometimes, we estimate possible outcomes from real-life projects by changing input variables. Accordingly, an analysis of those probable scenarios is necessary. This is called Sensitivity Analysis. However, sensitivity analysis in Excel is very essential for any business model. Usually, it uses the What If command tab to show the desired result of any financial model. Additionally, it not only can predict the present situation of the business but only helps in taking crucial decisions for the business’s growth and it can be used for interest rates. Here, it shows how the installment payments are sensitive to variable interest rates.

Step-by-Step Procedure to Perform Interest Rate Sensitivity Analysis in Excel

Often, we need to perform an interest rate sensitivity analysis for certain business analytics, and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. Hence, go through the following steps in order to perform the operation using Excel.

📌 Step 1: Select Dataset for Excel Sensitivity Analysis

For the purpose of demonstration, I have used the following sample dataset. Here, John is taking a loan from a bank for his business. So, I have the Loan Amount, Interest Rate, and No. of Installments. For instance, I have taken 7 different Variable Interest Rates in order to perform the interest rate sensitivity analysis in Excel.

📌 Step 2: Calculate Monthly Payment to Perform Interest Rate Sensitivity Analysis

Initially, select cell C9 and write down the following formula in order to find the monthly installment amount.

=PMT(C5/12,C6,C4)

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

After that, hit the Enter key in order to calculate the Monthly Payment.

📌 Step 3: Calculate Monthly Payment for Variable Interest Rates

Firstly, select the cell range B9:C16.
Secondly, select the Data tab and move on to Forecast.
Thirdly, select the Data Table from the What-If Analysis drop-down menu.

Consequently, the Data Table will pop out.
Fourthly, select cell C5 as the Column input cell.
Finally, press OK.

As a result, it’ll spill the precise monthly payment amounts for various rates.

📌 Step 4: Final Output

Last but not least, the interest rate sensitivity analysis will appear as in the image below. Here, you can see how the monthly interest rate influence the change in monthly payment. Hence, you can easily compare how much you have to pay with this sensitivity analysis. However, you can choose the rates according to your personal preference.

💬 Things to Remember

First of all, any changes in the inputs will result in changes in the final calculation of the outputs. For instance, if you change the interest rate, the monthly payment amounts will get modified too. Therefore, special care is a must so that we don’t change any input data by mistake.
Moreover, you can’t edit or delete a certain portion of the spilled output range. You’ll get a warning message if you try to do so. However, this could also result in unnecessary complications.
Next, make sure you don’t mix up while choosing the row and column input cells. Or else, it’ll result in a big error.
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 the row input cell reference in the Data Table dialogue box.
Lastly, Excel enables 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.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Interest Rate Sensitivity Analysis.xlsx

Conclusion

These are all the steps you can follow to perform interest rate sensitivity analysis in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

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

Get FREE Advanced Excel Exercises with Solutions!