Financial Risk Management
Auckland University of Technology
The answers for Exercises I, II, III and IV should be provided in an Excel file. In the Excel file, there should be one empty
sheet with your name and ID number.
You should upload only one Excel file and its name must be: StudentIdNumberProjectII.xlsx/xlsm.
5 marks will be given for the quality of the presentation, the argumentation, the presentation of the results in Excel (figures
should have a title, legends on the axis etc...). The numbers should be delimited with borders. Colors should be used to
make the Excel sheets easy and pleasant to read.
You will find in StudentAssetNameProjectI.xlsx (previous project) the stock assigned to each student.
(Total: 2 marks). The fixed income market.
1. (2 marks) For the given date 26/08/2020, download all the fixed income products in USD such as bond or floating
rate bond that are alive and available for the company you were assigned in project I. For each of them, download the
characteristics of the product (maturity, coupon rate, coupon frequency, notional of the bond etc....) as well as the price,
yield and (modified) duration (if available) for that product on the 26/08/2020. �
(Total: 10 marks). LETF, some basic facts.
To trade the index you can trade an exchange traded fund (ETF). For the S&P500, the following ETFs are available.
Fund Name Ticker Name Leverage Ratio
Proshares UltraPro Short S&P 500 ETF SPXU -3
|Proshares UltraShort S&P 500 ETF|
Proshares Short S&P 500 ETF
SPDR S&P 500 ETF
Proshares Ultra S&P 500 ETF
Proshares UltraPro S&P 500 ETF
Note: Equity LETFs tracking the S&P 500 Index, complete names as well as the ticker.
see Proshares and SPDR for a description of these products.
If (st)t≥0 is an index and (sm t )t≥0 is a LETF with multiple (or leverage ratio) m then
t - s(1)
rt = mrtm , (2)
the LETF return is a multiple of the index return. If m = 1, it is a simple index tracker. In practice it is the daily return so
rt stand for the daily index return while rtm is the LETF daily return.
1. (2.5 marks) For each LETF, download the longest (price) time series you can get as well as the index. For each m,
and sm t
on a same graph. s0 is the index value at time 0 and sm 0 is the LETF with leverage ratio m value at time
0 with 0 the first available date for that LETF. �
2. (2.5 marks) For each LETF, download the longest time series you can get for the Market Value and Turnover by Volume.
Plot ina same graph, the evolution of the Market Value as well as the USD amount associated with the Turnover by Volume
(we suppose that the volume traded on a given day is trade at the close price). Amounts will be expressed in USD. On
another graph, plot the dollar amount associated with the Turnover by Volume in percentage of the Market Value. �
3. (2.5 marks) Plot the evolutions of the Market Value of the SPY and S&P500 Index from 01/01/2000 to today as well
as the ratio Market Value of the SPY and Market Value of S&P500 Index (that you can multiply by 100 to express it in
4. (2.5 marks) For each LETF estimate (using Excel for example but any other statistical software will do) the linear model
rt = a0 + a1rtm + �t , (3)
and check that a1 = m (or is close). �
(Total: 8 marks). Hedging an option using the Black&Scholes model.
We suppose the two stock evolutions given in the Excel sheet Stock evolutions in the file FINA865-2020V2ProjectII.xlsx.
On that stock we suppose that there are two European options, a call with maturity T = 1 year and strike X = 100 and
one put with maturity T = 1 year and strike X = 100. We recall the basic formulas
C(s0, X, T, r, σ) = N(d1)s0 - Xe-rT N(d2) , (4)
P(s0, X, T, r, σ) = Xe-rT N(-d2) - N(-d1)s0 , (5)
|d2 = d1 - σ√T ,|
X + r + σ2/2� T
with σ the volatility, T the maturity and r the risk free rate (continuously compounded). The values for these variables are
provided in the Excel file. Let us remind the reader that the hedging ratio of the call (i.e., the delta) is N(d1) while for the
put it is -N(-d1).
1. (2 marks for each option/path) For each path and each option, build the hedging portfolio and perform the dynamic
hedging of the option up to the maturity. Compute the hedging error (i.e., the difference between the payoff you have to
pay and the value of your hedging portfolio). �
(Total: 2 marks). Implied volatility.
The European call option with maturity T = 1 year, strike X = 105 when the stock is s0 = 100
|C(s0, X, T, r, σ) = N(d1)s0 - Xe-rT N(d2) ,||(8)|
X + r + σ2/2� T
|d2 = d1 - σ√T ,|
with σ the volatility, T the maturity and r = 0.05 the risk free rate (continuously compounded). We suppose that the
option market price is C(s0, X, T, r, σ) = 9.40853937.
1. (1 mark) Plot the function σ → C(s0, X, T, r, σ) for T = 1 year, X = 105, s0 = 100 and r = 0.05 with σ ∈ [0.01 , 0.9]
(i.e., 1% to 90%). �
2. (1 mark) What is the volatility value σ∗ that is such that C(s0, X, T, r, σ∗) = 9.40853937? First explain why the graph
of the previous question allows you to confirm that such σ exists? Then, explain how you can obtain σ∗ using the Excel
solver. Such volatility is called the implied volatility. �