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 sixmonth Treasury rate is equal to this rate.
 The Treasury interest rates with a sixmonth 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 sixmonth 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.
=D26D19
 Insert this formula to get the amount that will be paid by the float side.
=D19D26
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 Addin 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!