Assignment 2 : Financial Computing
** Please note that no mark will be given if the answer does not follow the question requirements.
Question 1 - Requirements (80%)
The objective of this question is to test your understanding of
● financial modeling of yield curve construction process from different rate sources
● to derive the discount factors of individual tenors from available financial instrument market rate, bootstrapping process and zero coupon yields computation
You are provided with the liquid market instruments of HKD market: Cash Rates, FRA Rates and Swap Rates in below table.
|
Instrument
|
Tenor
|
Mid Rate
|
|
CASH
|
1Wk
|
2.500%
|
|
CASH
|
1M
|
2.900%
|
|
CASH
|
2M
|
3.000%
|
|
CASH
|
3M
|
3.100%
|
|
FRA
|
1x4
|
3.000%
|
|
FRA
|
2x5
|
2.900%
|
|
FRA
|
3x6
|
2.700%
|
|
FRA
|
6x9
|
2.600%
|
|
SWAP
|
1Y
|
2.500%
|
|
SWAP
|
2Y
|
2.200%
|
|
SWAP
|
3Y
|
2.100%
|
|
SWAP
|
4Y
|
2.300%
|
Please Use Excel spreadsheet to build the HKD yield curve financial model based on the methodology introduced in the lecture. The model assumptions are listed out below.
● Base date is 1 Sep 2025
● Day to Spot is 2
● Day/Year convention is ACT / 365
● Linear interpolation
● Holiday effect is ignored
● Zero coupon rate is assumed to be compounded on yearly basis
The basic Excel Operators (e.g. + - * / ^ etc) can be used. No Excel macro or programming is allowed in the spreadsheet model. No Excel function is allowed except SUM(), EDATE() and vlookup(). No rounding to figures outcome. Format of data presentation, all discount factors display to 6 decimal points, zero coupon rates display to 3 decimal points in terms of percentage.
You are required to show clearly the following sections in your Excel financial model:
1) The rate source table (3.5%)
2) The Cash rate discount factor for tenors per Appendix 1 (14%)
3) The The FRA discount factor for tenors per Appendix 1 (14%)
4) The Swap discount factor for tenors per Appendix 1 (31.5%)
5) The discount factor and zero coupon rate per Appendix 2 (7%)
6) Appropriate use of vlookup function for bootstrapping of FRA and IRS discount factors (10%)
APPENDIX 1 - Tenor of respective Financial Instrument
|
Cash Rate
|
FRA
|
Swap
|
|
From
|
To
|
From
|
To
|
From
|
To
|
From
|
To
|
|
03-09-25
|
03-09-25
|
03-10-25
|
03-01-26
|
03-09-25
|
03-12-25
|
03-09-27
|
03-12-27
|
|
03-09-25
|
10-09-25
|
03-11-25
|
03-02-26
|
03-12-25
|
03-03-26
|
03-12-27
|
03-03-28
|
|
03-09-25
|
03-10-25
|
03-12-25
|
03-03-26
|
03-03-26
|
03-06-26
|
03-03-28
|
03-06-28
|
|
03-09-25
|
03-11-25
|
03-03-26
|
03-06-26
|
03-06-26
|
03-09-26
|
03-06-28
|
03-09-28
|
|
03-09-25
|
03-12-25
|
|
|
03-09-26
|
03-12-26
|
03-09-28
|
03-12-28
|
|
|
|
|
|
03-12-26
|
03-03-27
|
03-12-28
|
03-03-29
|
|
|
|
|
|
03-03-27
|
03-06-27
|
03-03-29
|
03-06-29
|
|
|
|
|
|
03-06-27
|
03-09-27
|
03-06-29
|
03-09-29
|
APPENDIX 2 - Yield Curve result per below date
|
Date
|
Date
|
|
03-09-25
|
03-09-26
|
|
10-09-25
|
03-12-26
|
|
03-10-25
|
03-03-27
|
|
03-11-25
|
03-06-27
|
|
03-12-25
|
03-09-27
|
|
03-01-26
|
03-12-27
|
|
03-02-26
|
03-03-28
|
|
03-03-26
|
03-06-28
|
|
03-06-26
|
03-09-28
|
|
|
03-12-28
|
|
|
03-03-29
|
|
|
03-06-29
|
|
|
03-09-29
|
Question 2 - Requirements (20%)
The objective of this question is to test your understanding of interest rate swap (IRS) pricing model.
You are provided with a HKD IRS with the following terms:
Swap of Receive fixed, Pay floating
Notional amount = HKD 1 million
Trade date of swap = 1 Sep 2025
Day to Spot = 2
Maturity date of swap = 3 Sep 2028
Fixed leg payment frequency = HKD, quarterly, ACT/365
Floating leg rate and payment frequency = HKD, 3 month HIBOR, quarterly, 30/365
Based on the HKD yield curve result from Question 1, please compute the IRS rate (i.e. the fixed leg rate) as of 1 Sep 2025.
You are required to provide only the IRS pricing financial model using Excel based on the methodology introduced in the lecture.
The financial model is based on the assumptions of linear interpolation, holiday effect is ignored. No rounding to figures outcome. Format of data presentation, all figures display to 6 decimal points, interest rate display to 3 decimal points in terms of percentage.