Yield Curve Construction and IRS Pricing in Excel

Introduction: Why Yield Curves Matter in 2026

In the world of finance, the yield curve is the backbone of pricing everything from bonds to swaps. As of June 2026, with central banks adjusting rates amid a shifting global economy, understanding how to construct a yield curve from market data is more relevant than ever. This tutorial walks you through the process of building a HKD yield curve using Excel, following the methodology typical in financial computing assignments like the one described. We'll cover bootstrapping discount factors from cash rates, FRAs, and swaps, then use the curve to price an interest rate swap (IRS). No macros or complex functions—just pure Excel logic and linear interpolation.

Understanding the Input Data

Your starting point is a set of liquid market instruments for the HKD market: Cash rates for short tenors (1W, 1M, 2M, 3M), FRA rates (1x4, 2x5, etc.), and swap rates (1Y to 4Y). The base date is 1 Sep 2025, with a spot date two days later (3 Sep 2025). Day count convention is ACT/365. All rates are mid-rates, and we ignore holidays. The goal is to derive discount factors and zero-coupon yields for a set of specified dates (Appendix 2).

Step 1: Set Up the Rate Source Table

Create a clean table with columns: Instrument Type, Tenor, From Date, To Date, Mid Rate. Use the provided data. For cash, the from date is the spot date (3 Sep 2025) and to date is the tenor end. For FRAs, the start date is the forward start (e.g., 1x4 means 1 month from spot to start, 4 months total). For swaps, the start is spot, end is maturity. Calculate the actual dates using EDATE() and simple date addition. This table is your reference for bootstrapping.

Step 2: Bootstrap Cash Rate Discount Factors

Cash rates are simple interest for tenors up to 3M. The formula for discount factor (DF) from a cash rate R is: DF = 1 / (1 + R * (days/365)). Compute days between spot and maturity using actual day count. For example, 1W cash: from 3 Sep 2025 to 10 Sep 2025 = 7 days. DF = 1/(1+0.025*7/365). Display DFs to 6 decimal places. Build a table for each cash tenor from Appendix 1 (e.g., 3 Sep-3 Oct, etc.) using linear interpolation if needed between known points.

Step 3: Bootstrap FRA Discount Factors

FRAs are forward-starting. The FRA rate is the forward rate. To get the DF for the end date, use: DF_end = DF_start / (1 + FRA_rate * (days/365)). You already have DF_start from cash rates (or interpolated). For example, FRA 1x4: start 1M from spot (3 Oct 2025), end 4M from spot (3 Jan 2026). DF_start is the 1M cash DF. Then DF_end = DF_start / (1+0.03*92/365). Use VLOOKUP to fetch the DF_start from your cash DF table. Repeat for all FRAs, interpolating where needed.

Step 4: Bootstrap Swap Discount Factors

Swaps are more complex. For a 1Y swap with quarterly fixed payments, the present value of fixed leg equals floating leg (par swap). The formula: 1 = DF_spot * (sum of fixed payments * DF_i) + DF_maturity. Rearranged: DF_maturity = (1 - swap_rate * sum of DF_i * days_i/365) / (1 + swap_rate * days_maturity/365). You need DFs for all payment dates before maturity. Bootstrap sequentially: start with 1Y swap, using cash/FRAs for intermediate DFs. Use linear interpolation to get DFs at payment dates. For 2Y, 3Y, 4Y swaps, use previously bootstrapped DFs. This is the most calculation-heavy part; build a helper table for payment dates and interpolation.

Step 5: Build the Full Yield Curve (Appendix 2)

Now you have DFs for all instrument maturities. For the dates in Appendix 2 (e.g., 3 Sep 2025 to 3 Sep 2029), interpolate DFs linearly between known points. Then compute zero-coupon rates: ZCR = (1/DF)^(365/days) - 1 (compounded yearly). Display ZCR to 3 decimal places in percentage. This curve can now be used for pricing any fixed-income instrument.

Step 6: Price the Interest Rate Swap (Question 2)

You are given a swap: receive fixed, pay floating (3M HIBOR), notional HKD 1M, trade date 1 Sep 2025, maturity 3 Sep 2028. Fixed leg quarterly ACT/365, floating leg quarterly 30/365 (but we'll use ACT/365 for simplicity as per lecture). The IRS rate is the fixed rate that makes the swap value zero. Compute: Fixed Rate = (1 - DF_maturity) / (sum of DF_i * days_i/365). Use your bootstrapped yield curve to get DFs at each fixed payment date (quarterly from 3 Dec 2025 to 3 Sep 2028). Interpolate as needed. Display the IRS rate to 3 decimal places.

Practical Tips for Excel

Use EDATE for month offsets, but adjust for exact days manually.
Use VLOOKUP to fetch DFs from your tables—critical for bootstrapping FRAs and swaps.
Linear interpolation formula: y = y1 + (x - x1) * (y2 - y1) / (x2 - x1). Use dates as serial numbers.
Keep all calculations in separate columns for clarity. No rounding intermediate steps.
Double-check day counts: ACT/365 means actual days divided by 365.

Real-World Relevance: Yield Curves in 2026

With the rise of AI-driven trading and real-time risk management, understanding yield curve construction is a skill in demand. Just as gamers track in-game economies or sports analysts use data to predict outcomes, financial analysts rely on curves to price derivatives. This tutorial gives you the foundation to tackle assignments and real-world projects alike.

Conclusion

By following these steps, you'll have a fully functional yield curve model in Excel, ready for assignment submission or further analysis. Remember to display all discount factors to 6 decimal places and zero coupon rates to 3 decimal places. Good luck!