How to Perform Interest Rate Swap Calculation in Excel

With the aid of a financial derivative called an interest rate swap, two parties can exchange the cash flows from a fixed-rate loan for the cash flows from a floating-rate loan, or vice versa. In this article, you will learn about three easy steps for the interest rate swap calculation in Excel. We will be using Microsoft 365 to do this; however, you can use any version of Microsoft Excel and follow this tutorial.

Steps to Perform Interest Rate Swap Calculation in Excel

There will be three steps for the interest rate swap calculation. Firstly, we will set up the format for this. Following that, we will calculate the values using some formulas. Lastly, we will use the Excel Solver feature to find the fixed rate where both parties will get the same value from the interest rate swap.

1^st Step: Set Up Format

In this first step, we will set up the format for the interest rate swap calculation in Excel. There will be five parts to this template. We will discuss the elements of the parts below.

• Firstly, the first part contains the assumed data.

• Secondly, the Treasury interest rates with a six-month gap are included in the next section of this format.
• Thirdly, we will create a table to calculate the present value of the bond based on the fixed interest rate.
• Then, the fourth table will return the present value of the bond based on the floating interest rate.
• Finally, the last section is to find which party is gaining from this interest rate swap.

2^nd Step: Calculate Relevant Values

In this step, we will calculate all the values for the fields that we created in the last step. We will use the SUM function and some basic formulas to do this.

• To begin with, type all the relevant values. $15 million for the notional amount and 4.3% for the yearly fixed rate.
• Then, we will assume the floating interest rate is equal to the six-month treasury rate. So, link those cells using the formula below.

• After that, use the following formula in the cell range C15:C17.

• Afterward, use this formula to find the value of the cash flow at maturity.

• Then, insert this formula and use the Fill Handle to fill the three cells below.

• Afterward, use this formula to find the total amount.

• Similarly, we will use the following formulas to calculate the present value of the floating rate bond. Firstly, type this formula in cell C22.

• Secondly, use this formula in cell D22.

• Thirdly, insert this formula in cell D26.

• Then, type this formula. We will find the amount that will be received by the float side.

• Lastly, use this formula. We will find the amount that will be paid by the float side.

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

3^rd Step: Use Solver to Find Equilibrium Fixed Rate

In this last step, we will find the yearly fixed rate for which the position will be equal for both parties. Before doing this, you will need to enable the Solver Add-in in Excel.

• Firstly, after enabling the add-in, select Solver from the Data tab.

• Secondly, use the following conditions.

• Thirdly, press Solve.

• Then, it will find the yearly fixed rate and we will see the swap position is equal for both sides.

• Finally, here is the snapshot of the complete interest rate swap calculation in Excel.

Download Practice Workbook

You can download the Excel file from the link below.

Conclusion

We have shown you three steps for the interest rate swap calculation in Excel. Moreover, there is a practice section in the Excel file. You can use that to follow along with this article. Please leave a comment below if you have any questions or concerns about these techniques. However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible.

Thanks for reading, keep excelling!

Related Articles

• Create Flat and Reducing Rate of Interest Calculator in Excel
• How to Perform Interest Rate Sensitivity Analysis in Excel

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