Financial Risk Management

FINA865-2020V2

Project II

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.

Exercise I

(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. �

Exercise II

(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 | SDS SH SPY SSO UPRO | -2 -1 +1 +2 +3 |

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

st - st-1 = m s | m t-1 sm t-1 , |

m

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,

plot st

s0

and sm t

sm

0

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. �

1

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

percent). �

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). �

Exercise III

(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)

with

d1 = | σ√T | , | (6) (7) |

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). �

Exercise IV

(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) | ||

with | |||

d1 = | σ√T | , | (9) (10) |

s0 X + r + σ2/2� T | |||

d2 = d1 - σ√T , |

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. �