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.
1st 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.
-
- Notional Amount – This is the original swap amount for both parties.
- Yearly Fixed Rate – In this field, we will input the annual fixed interest rate.
- Floating Interest Rate – We will need to assume this rate, and we will assume the six-month Treasury rate is equal to this rate.
- 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.
2nd 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.
=C9
- After that, use the following formula in the cell range C15:C17.
=($C$6/2)*$C$5
- Afterward, use this formula to find the value of the cash flow at maturity.
=($C$6/2)*$C$5+C5
- Then, insert this formula and use the Fill Handle to fill the three cells below.
=C15/(1+C9)^B15
- Afterward, use this formula to find the total amount.
=SUM(D15:D18)
- Similarly, we will use the following formulas to calculate the present value of the floating rate bond. Firstly, type this formula in cell C22.
=(C7/2)*C5+C5
- Secondly, use this formula in cell D22.
=C22/(1+C9)^B22
- Thirdly, insert this formula in cell D26.
=SUM(D22:D25)
- Then, type this formula. We will find the amount that will be received by the float side.
=D26-D19
- Lastly, use this formula. We will find the amount that will be paid by the float side.
=D19-D26
Read More: How to Calculate Effective Interest Rate On Bonds Using Excel
3rd 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.
-
- Set Objective: C29.
- Value Of: 0.
- By Changing Variable Cells: C6.
- 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!
