How to Perform Interest Rate Swap Calculation in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Set Up Format to Perform Interest Rate Swap Calculation in Excel


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

Calculate Relevant Values to Perform Interest Rate Swap Calculation in Excel

  • 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.

Use Solver to Find Equilibrium Fixed Rate to Perform Interest Rate Swap Calculation in Excel

  • 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.

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo