Build a HKD Yield Curve in Excel: Bootstrapping Cash, FRA, and Swap Rates

Introduction to Yield Curve Construction for HKD Markets

In financial computing, building a yield curve from market instruments is a core skill for analysts, traders, and risk managers. This tutorial walks you through the process of constructing a HKD yield curve using Excel, based on typical assignment requirements. You'll learn how to derive discount factors and zero-coupon yields from cash rates, forward rate agreements (FRAs), and interest rate swaps (IRS). The example uses a base date of September 1, 2025, spot lag of 2 days, ACT/365 day count, and linear interpolation—common assumptions in real-world financial modeling.

Understanding the Input Data: Cash, FRA, and Swap Rates

The yield curve is built from three types of instruments:

Cash Rates: Short-term rates (1W, 1M, 2M, 3M) reflecting borrowing costs for immediate delivery.
FRA Rates: Forward rate agreements (e.g., 1x4, 2x5) that lock in a future interest rate for a specified period.
Swap Rates: Fixed rates for interest rate swaps (1Y to 4Y) where the fixed leg is exchanged against floating 3M HIBOR.

These rates are provided as mid rates (e.g., 2.500% for 1W cash). The goal is to compute discount factors for all required tenors and then derive zero-coupon yields.

Step 1: Setting Up the Rate Source Table

Create a table in Excel with columns: Instrument Type, Tenor, From Date, To Date, Mid Rate (%). For cash, the from date is the spot date (3 Sep 2025) and to date is the maturity. For FRAs, the from date is the start of the forward period, and to date is the end. For swaps, the from date is the spot date, and to date is the maturity. Use EDATE() to compute dates. Example: for 1W cash, from = 3 Sep 2025, to = 10 Sep 2025 (7 days later).

Step 2: Bootstrapping Cash Rate Discount Factors

For cash rates, the discount factor (DF) is computed directly from the rate and time period. Use the formula: DF = 1 / (1 + rate * (days/365)). For example, 1W cash: days = 7, rate = 2.5% => DF = 1/(1+0.025*7/365) = 0.999521. Compute for all cash tenors: 1W, 1M, 2M, 3M. Display DF to 6 decimal places.

Step 3: Bootstrapping FRA Discount Factors

FRAs provide forward rates. To derive discount factors, use the relationship: DF_end = DF_start * (1 / (1 + FRA_rate * (days/365))). The DF_start is already known from the cash curve or previous step. For example, FRA 1x4 starts at 1M (from cash) and ends at 4M. Use VLOOKUP to fetch the DF at the start date. Then compute DF_end. Repeat for all FRAs: 1x4, 2x5, 3x6, 6x9. Ensure you use linear interpolation if the start date is not exactly a cash tenor.

Step 4: Bootstrapping Swap Discount Factors

Swaps are more complex. For a swap with maturity T, the fixed leg pays quarterly. The swap rate is the fixed rate that makes the present value of fixed and floating legs equal. The bootstrap process solves for the discount factor at the swap maturity using known DFs for earlier dates. For a 1Y swap with quarterly payments, you have 4 payment dates. Use the formula: sum(DF_i * fixed_rate * days_i/365) + DF_T * notional = notional. Rearranged: DF_T = (1 - sum(DF_i * fixed_rate * days_i/365)) / (1 + fixed_rate * days_T/365). The DF_i for earlier dates are obtained from cash and FRA steps, using interpolation if needed. Apply this for 1Y, 2Y, 3Y, 4Y swaps.

Step 5: Linear Interpolation for Intermediate Tenors

Not all required tenors (Appendix 2) have direct market rates. Use linear interpolation on discount factors or zero-coupon rates. For a target date between two known dates, interpolate the DF or rate linearly. For example, to get DF for 3 Oct 2025 (between 3 Sep and 10 Sep), compute: DF = DF1 + (DF2-DF1)*(days_diff/total_days). Display all DFs to 6 decimal places.

Step 6: Computing Zero-Coupon Yields

Once discount factors are known for all dates in Appendix 2, compute zero-coupon yields (ZC rates) using: ZC = (1/DF)^(365/days) - 1. Display as percentage to 3 decimal places. For example, if DF = 0.99 for 30 days, ZC = (1/0.99)^(365/30)-1 = 0.1268 = 12.680%.

Step 7: Using VLOOKUP for Bootstrapping

In the FRA and swap bootstrap, you need to retrieve previously computed DFs for specific dates. Create a table of date-DF pairs and use VLOOKUP with approximate match to find the DF for a given date. For example, to get DF at start of FRA 1x4 (3 Oct 2025), VLOOKUP(3-Oct-2025, date_DF_table, 2, TRUE). This automates the bootstrap process.

Practical Tips and Common Pitfalls

Always use ACT/365 day count. Compute days between dates using simple subtraction in Excel.
Ignore holidays as per assumptions. Use actual calendar days.
No rounding until final display. Keep full precision in calculations.
Use only SUM(), EDATE(), and VLOOKUP as allowed functions.
Double-check date alignment: spot date is 3 Sep 2025 (2 days after trade date 1 Sep).

Trend Connection: Yield Curves in the Age of AI and Fintech

Yield curve construction is not just a classroom exercise. In 2026, fintech firms and AI-driven trading platforms use similar bootstrapping algorithms to price derivatives in real time. For example, robo-advisors and decentralized finance (DeFi) protocols rely on accurate discount factors to value bonds and swaps. Understanding the manual Excel process gives you a foundation for building automated models in Python or R, which are in high demand for quantitative finance roles. Just as AI models like GPT-4o are used to analyze market sentiment, the yield curve remains a fundamental tool for interest rate risk management.

Conclusion

By following this tutorial, you have built a complete HKD yield curve from cash, FRA, and swap rates using bootstrapping and linear interpolation in Excel. This skill is essential for financial computing assignments and real-world applications in banking, asset management, and fintech. Practice with different datasets to master the process.