COMP7802 Assignment 2
Assignment 2 : Financial Computing
Copyright By PowCoder代写 加微信 powcoder
** 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 1.180%
CASH 1M 1.330%
CASH 2M 1.650%
CASH 3M 2.200%
FRA 1×4 2.500%
FRA 2×5 2.550%
FRA 3×6 2.650%
FRA 6×9 2.780%
SWAP 1Y 2.930%
SWAP 2Y 2.960%
SWAP 3Y 2.930%
SWAP 4Y 2.860%
Please Use Excel spreadsheet to build the HKD yield curve financial model based on the
methodology introduced in the lecture. The model assumption listed out below.
⚫ Base date is 25 Jul 2022
⚫ 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, all discount factors display to 6 decimal points, zero coupon rates
display to 3 decimal points in 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
APPENDIX 1 – Tenor of respective Financial Instrument
Cash Rate FRA Swap
From To From To From To From To
Question 2 – Requirements (20%)
The objective of this question is to test your understanding of valuation of fixed rate financial
instrument.
You are provided with a HKD fixed rate financial instrument with 3 year maturity in the
following terms:
Notional amount = HKD 1 million
Trade date = 25 Jul 2022
Day to Spot = 2
Maturity date = 27 Jul 2025
Paid at fixed interest rate = 2.93% p.a
Fixed interest payment frequency = quarterly, ACT/365
The notional of the financial instrument will be redeemed at par at maturity date.
Based on the HKD yield curve result from Question 1, please compute the price (% of Par) of
the above financial instrument on trade date as of 25 Jul 2022.
You are required to provide only the financial instrument valuation model using Excel based on
the methodology introduced in the lecture. Please indicate clearly all the cash flow period
covered in your valuation model (marks will be counted for each correct cash flow period
indicated)
The financial model is based on the assumptions of linear interpolation, holiday effect is
ignored. No rounding is required in your calculation.
1) You need to calculate the present value of all future cash flow derived from the financial
instrument.
2) Price quote in % of Par
= (amount paid to buy the financial instrument / face notional of the financial instrument) x
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com