Step 1 – Set Up the Format
- We’ll use the following information:
-
- 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.
- The Treasury interest rates with a six-month gap are included in the next section of this format.
- We will create a table to calculate the present value of the bond based on the fixed interest rate.
- The fourth table will return the present value of the bond based on the floating interest rate.
- The last section finds which party is gaining from this interest rate swap.
Step 2 – Calculate the Relevant Values
- Input all the relevant values: $15 million for the notional amount and 4.3% for the yearly fixed rate.
- We will assume the floating interest rate is equal to the six-month treasury rate. Link those cells using the formula below.
=C9
- Use the following formula in the cell range C15:C17.
=($C$6/2)*$C$5
- Use this formula to find the value of the cash flow at maturity.
=($C$6/2)*$C$5+C5
- Insert this formula and use the Fill Handle to fill the three cells below.
=C15/(1+C9)^B15
- Use this formula to find the total amount.
=SUM(D15:D18)
We will use the following formulas to calculate the present value of the floating rate bond.
- Use this formula in cell C22.
=(C7/2)*C5+C5
- Use this formula in cell D22.
=C22/(1+C9)^B22
- Insert this formula in cell D26.
=SUM(D22:D25)
- Insert this formula to find the amount that will be received by the float side.
=D26-D19
- Insert this formula to get the amount that will be paid by the float side.
=D19-D26
Read More: How to Calculate Effective Interest Rate On Bonds Using Excel
Step 3 – Use the Solver to Find the Equilibrium Fixed Rate
- Enable the Solver Add-in in Excel if needed.
- Select Solver from the Data tab.
- Use the following conditions.
-
- Set Objective: C29.
- Value Of: 0.
- By Changing Variable Cells: C6.
- Press Solve.
- This will find the yearly fixed rate and put the swap positions as equal for both sides.
- Here is the snapshot of the complete interest rate swap calculation in Excel.
Download the Practice Workbook
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
Get FREE Advanced Excel Exercises with Solutions!
