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

### 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`

`=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`

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF