M
ESTRADO
F
INANCE
T
RABALHO
F
INAL DE
M
ESTRADO
T
RABALHO DE
P
ROJECTO
A
C
ASE
S
TUDY ON
S
ECURITIES
E
VALUATION
F
LÁVIO
J
OSÉ
R
IJO
P
INTO
M
ESTRADO EM
F
INANCE
T
RABALHO
F
INAL DE
M
ESTRADO
T
RABALHO DE
P
ROJECTO
A
C
ASE STUDY ON SECURITIES EVALUATION
F
LÁVIO
J
OSÉ
R
IJO
P
INTO
O
RIENTAÇÃO:
P
ROFESSORT
IAGOG
ONÇALVESi Acknowledgements
I would like to thank my supervisor, Professor Tiago Gonçalves for the all the guidance he
provided during this project. I am also grateful to everyone in Millennium BCP’s Financial Markets Department, especially to the Derivatives Team for all the patience and support given
ii Abstract
This project intends to contribute with a real life scenario that tries to capture the vital issues
regarding the issuance and commercialization of Structured Products.
It will target a Path Dependent Structured Product issued by Millennium BCP Investment
Bank where I am, at the time of the realization of this paper, an intern working with the Equity
Derivatives team.
The case is designed to be useful at Masters Level Courses, in the scope of Financial
Engineering, Financial Options or Derivatives Courses. It is accompanied by all the necessary
data and intends to equip instructors with a valuable resource to meet both Financial and
Quantitative objectives.
Key Words: Case Study; Structured Products; Derivatives; Cholesky Decomposition; Basket
iii Resumo
A intenção deste projecto é contribuir com um caso prático, que tenta capturar os assuntos
vitais envolvidos na emissão e comercialização de Produtos Estruturados numa situação real.
Para tal, o caso vai usar como base um Produto Estruturado, emitido e comercializado pelo
Millennium BCP Investment Bank, onde me encontro como estagiário na equipa de produtos
derivados no momento de realização deste documento.
O Caso esta desenhado para ser usado ao nível de Mestrado em cadeiras de Engenharia
Financeira, Opcções Financeiras e Derivados. Vem acompanhado por todos os dados
necessários e pode ser assim ser usado para completer objectivos de aprendizagem quer no
iv Table of Figures
(Case) Table 1: Composition of the Underlying Basket ... 5
(Case) Table 2: Payoff Possibilities ... 6
(TN) Table 1 : Millennium Valor Composition ... 15
v Table of Contents
Acknowledgements ... i
Abstract ... ii
1. Introduction ... 1
2. Literature Review ... 2
3. Case Study ... 4
3.1 Overview ... 4
3.2 Status Report ... 7
3.3 Suggested Problems ... 8
4. Teaching Note ... 9
4.2 Learning objectives ... 10
4.3 Appropriate Uses ... 11
4.4 Supplementary Materials... 12
4.5 Discussion Outline ... 13
4.4 Case Analysis and Proposed Solution ... 14
4.4.1 Question 1 ... 14
4.4.2 Question 2. ... 15
4.4.3 Question 3. ... 18
4.4.4 Question 4. ... 19
4.4.5 Question 5 ... 22
4.4.6 Question 6 ... 24
5. Conclusion ... 25
6. References ... 26
7. Attachments ... 28
7.1 Teaching Note (TN) Excel File ... 28
7.2 Case Study (Case) Excel File ... 37
8. Appendix ... Error! Bookmark not defined.
vi List of Abbreviations
Dy - Divdidend Yield
L - Lower Triangular Matrix
Q - Option Value
Rf - Risk Free Rate
Si - Price of Underlying Asset i
T - Time
Z - Value of Zero Coupon Bond
- Variation
- Random Shock
1 1. Introduction
This project was developed during an internship in Millennium BCP Investment Bank in the
Derivatives Team.
It intends to give a snapshot of this experience in the form of a Case Study designed to be useful
in Masters Levels classes, providing a real life standpoint to give students a deeper
understanding of structured products and the everyday questions regarding Structured
Products.
The main objective of the case is to set forth an opportunity to step beyond the theoretical
concepts learned in class and to deal with the problems that emerge from a real life situation.
Using Millennium Valor, a Structured Product issued by Millennium BCP Investment Bank as
a tool, the case will introduce students to the endless number of possibilities to build structured
products as well as the questions regarding the pricing, risk assessment and analysis of said
products. It will also require the use of critical thinking, in order to make an enlightened
assessment about the attractiveness of the product considering the present financial
environment.
This document will be structured in three main sections: the first section, reviews extant
literature on the construction and use of cases; the second section given to students, will be the
case per se, containing all the questions and guidelines needed to solve the case. This section
will be accompanied by an excel file containing all the information needed to solve the case.
The third, will be the teaching note, it intends to provide instructors with guidance and
information to make the most of the case and set forth a solution suggestion, providing
2 2. Literature Review
Case studies are widely accepted as educational tools (Merrian, 1998; Stake, 1995) to provide
students with an action oriented opportunity to develop the concepts learned in class. They
make use of detailed problem focused narratives from educational valuable situations
addressing a central concept.
Lundberg; Rainsford; Shay and Young (2001) quote a definition of case study as offered by
Maufette-Leenders et al (1997), they say the following:
“… A description of an actual situation, commonly involving a decision, a challenge, an
opportunity, a problem, or an issue faced by a person (or persons) in an organization. “(p.2).
Cases are meant to be used as foundations to apply and learn the material, using their narrative
to provide real world context. Taking the standpoint of a situation faced in a real organization,
a case becomes the vehicle for discussion and investigation, transferring the learning
responsibility from the instructor to the students.
The case hereby presented will try to meet this objectives. To do so it utilizes the insights found
in the literature and follow the recommendations provided by several authors on how to build
a proper Case Study.
Austin, J. E. (1993) suggests that a teaching note increases the learning effects of the utilization
of a Case Study. Given the importance of the teaching note suggested by the literature, special
attention was given to the construction of a teaching note in the case presented.
The document tries to address the fundamental objectives utilizing the suggestions made by
Austin, J. E. (1993) according to whom a teaching note increases teaching effectiveness by
3
Special effort was made to provide clear learning objectives and material for discussion in the
teaching note following Austin, J.E. (1993) suggestion that teaching notes should be “crafted
as discussion vehicles”(p.1) helping instructors to create class discussion and increase
probability of classroom success by providing a teaching plan that defines the learning
objectives and suggests a teaching strategy.
Regarding the construction of the Case structure, Baxter and Jack (2008) suggest that it is
essential to start by defining the boundaries of the case to avoid building a subjected with too
many objectives with scope that is overly broad. And to select the type of case that best suits
the desired objectives of the case.
Methods to define such boundaries are suggested by several authors, such as time and place
(Creswell, 2003), time and activity (Stake, 1995), and by definition and context (Miles &
Huberman, 1994). Different types of possible case studies are presented by Baxter and Jack
(2008) according to Yin (2003) and Stake (1995) distinguished by the objective that each
possible type is trying to achieve.
The Structure of the presented case follows these recommendations. Given that the case is used
to help solidify theoretical concepts and help understand their application in a real life setting,
the case is built as an instrumental Case Study defined by Stake (1995) and presented by Baxter
and Jack (2008) as being a case that “Provides insight into an issue or helps refine a theory.
The case is of secondary interest, it plays a supportive role, facilitating our understanding of
something else”. (Table 2).
Given that the case is a reproduction of a real life situation and given the nature of the suggested
problems of the case, it seems suitable to follow the suggestions made by Creswell (2003) and
4
3. Case Study 3.1 Overview
Millennium BCP is at the moment, the largest privately owned Portuguese bank and a player
in several markets. Despite having commercial banking has its main activity, the investment
bank is responsible for functions of high importance within the bank. It is composed by several
teams, such as Corporate Finance; Trading and Sales; Market Research and Derivatives.
The Derivatives team, where you just started working, is responsible for the creation, issuance
and commercialization of all the bank’s Structured Products.
At the current time, given the low interest rate environment, this products may become
appealing to investors since they offer capital guarantee with a small payment, but also provide
the opportunity to receive an higher payment at maturity making them more attractive in the
market.
It is now 12/06/2015 and the team leader made you responsible for the analysis of a Structured
Product - Millennium Valor issued and commercialized by the bank.
You just had meeting with the team where you discussed the product and were able to get some
time to read the term sheet of the product (provided in Appendix.)
You were able to gather the following information:
3.1.1 Description
Millennium Valor is an Indexed Deposit issued in Euros; with a maturity of 2 Years starting at
12 June 2015 with maturity at 14 June 2017. The minimum investment required is 1.000€ and
5
The product targets clients with no need for liquidity during the maturity of the deposit.
Possible investors, should not only to privilege the capital guarantee but also have a positive
expectation regarding the underlying assets for a possible higher payoff.
The product is composed by an equally weighted basket of stocks from 5 European companies
as described in Table 1.
(Case) Table 1
3.1.2 Payoff
The product has a guaranteed payment at maturity, returning the value invested with an
additional 0.3% return on the deposit with no possibility of early redemption. Additionally, it
provides the possibility for an additional payment of 5% dependent on the simultaneous
appreciation of all underlying assets during the 2 year maturity period.
Fixed Return:
At maturity, the product guarantees the value invested, the client will receive the value invested
and a minimum of 0.30% on the deposit.
Company Name Exchange Bloomberg Ticker Country Currency Website
GDF Suez Euronext Paris GSZ FP EQUITY France EUR www.gdfsuez.com
Allianz Frankfurt Stock Exchange ALV GY EQUITY Germany EUR www.allianz.com
Iberdrola Bolsa de Madrid IBE SE EQUITY Spain EUR www.iberdrola.es
Orange Euronext Paris ORA FP EQUITY France EUR www.orange.pa
6
Variable Return:
The product offers the possibility for Variable Return at maturity with the following condition:
On the observation date, the closing prices of all underlying assets are simultaneously higher
or equal to the issue date closing prices, the client will receive a payoff of 5% over the notional
amount.
Formally, the Variable Return component of the product is as follows:
(Case) Table 2
3.1.3 Underlying Assets
GDF Suez is a French Gas and Energy Company, it provides services of Energy Management
and Environmental Engineering and is involved in the production transportation and
commercialization of Natural Gas.
Orange is also French, it offers Telecommunications services, Internet and Cable Television.
Moreover it rents and sells telecommunications equipment.
Allianz is a German Insurance Company, offering services through its subsidiaries, it offers
several types of insurance and fund management services.
Iberdrola is also in the Energy Sector, the Spanish Company is specialized in renewable
energies and commercializes Electricity in the EUA, UK, Spain, Portugal and South America.
Eni is an Energy Italian Company, it is involved in the production and commercialization of
Electricity and Gas. Additionally it operates Gas Stations and Refines Oil.
Condition Payoff
(Stocki) "i < 0% 0.30%
7 3.2 Status Report
Since you started working in the bank In January you have been following news and data about
Global Economy and Financial Markets. You do not remember everything that happened but
you can recall the following information:
In January, European Central Bank announced an expansionary policy in the form of a
Quantitative Easing Program. European Central Bank is set to create inflation and boost
economic growth by purchasing bonds with monthly injections of 65 Billion Euros.
Because of this, by June, European Yields were at an historical low. In Equity Markets it was
possible to witness a positive macroeconomic expectation, with signals of economic growth
and a positive performance by major European Indexes.
Additionally, Greece, unhappy with the harsh austerity terms imposed due to the bailout, is for
some time evolved in unsuccessful negotiations with their lenders creating instability in the
markets and uncertainty in the continuity of the country in the European Union.
In the United States, investors are looking at strong indicators and closely watching the FED’s
decision on rising Interest Rates.
In conclusion, while the market is holding for further developments about the Greek situation,
the overall view point is one of confidence in economic growth and positive performance in
8 3.3 Suggested Problems
It is now 12/06/2015, as an employee at Millennium Investment Bank you are tasked with the
analysis of the new issued Structured Product, Millennium Valor. Since you still lack
experience dealing with non-vanilla Financial Products, you decide to ease your way in by
clarifying the following issues:
1. Structured Products are usually combinations of simpler Financial Products. Divide
this product into its components.
2. Consider a product identical to Millennium Valor depending on a single underlying
asset, GDF Suez stocks. Build a pricing model for this product and assess the
probability of each possible Payoff at maturity.
3. In reality, the Payoff is dependent on a basket of 5 underlying assets. Does this raise
any difficulties? Why? How does that affect your calculations?
Now that you are fully aware of the issues surrounding this situation. Your job as an employee
in the Derivatives Team is to:
4. Build a pricing model for Millennium Valor, calculate its value and estimate the
probability of each possible payoff at the product’s maturity.
5. Perform a risk assessment using Greeks, and define a strategy to hedge against the
main risks created by issuing the product.
6. Considering all the information gathered about Millennium Valor and market
conditions at this time, do you think this is an attractive product for an investor?
9
4. Teaching Note 4.1 Case Synopsis
By letting students be part of a team in Millennium BCP, the largest privately owned
Portuguese Bank Derivatives Team, instructors will be able to guide students through the
analysis of a new product Millennium Valor, being issued and a commercialized by Millennium
BCP Investment Bank.
As members of this team, students will be asked to assess the probability of different payoffs
at maturity, make a risk assessment of the product in order to define a hedging strategy and
analyze the attractiveness of the product in the current market environment.
The case presented will help to introduce and raise discussion about the concept of Structured
Products, Option Strategies, Hedging Strategies and different models that can be utilized within
10 4.2 Learning objectives
The case intends to introduce and develop the concepts regarding Structured Products and
Quantitative Methods. It intends to be used as a vehicle for the development of a critical way
of thinking about Structured Products and the issues concerning the issuance and
commercialization of this type of Financial Products in a real life setting.
Subject to the circumstances of the course, the case can be useful in the exemplification of
introductory concepts such as Financial Products with a discontinuous payoff and the
Monte-Carlo Approach, and concepts of higher complexity such as methods of working with an
underlying composed of inter-correlated assets.
The main objective of this document, is to give instructors the materials to incite discussion,
research and provide a real life example of the following subjects:
Main Concepts regarding Structured Products and their benefit for both the issuer and
investors.
Construction of Structured Products through the identification of its underlying
components.
Quantitative Methods required for pricing and assessing the probability of payoff of
non-vanilla Structured Products.
The use of Cholesky Decomposition as a method of implementing the Monte-Carlo
approach with inter-correlated underlying assets.
Risk analysis of Structured Products and possible hedging strategies utilized by the
11 4.3 Appropriate Uses
This case is meant to be used at Masters Level classes. It gives instructors the means to meet
both finance and quantitative objectives by bringing inside the classroom practical world
insight about analysis of Structured Products.
The issues addressed in the case, can be considered relevant in the scope of a Financial
Engineering, Financial Options, or Financial Derivatives Class.
Given the nature of the case, by changing or making a carefully selection of Suggested
Problems, it has the flexibility to be useful in a variety of subjects.
Suggested Problems are divided in two parts, initial questions can be used to provide a gentle
introduction to the case and to introduce the basic concepts of Structured Products and the
convenience of Monte Carlo Methods when dealing with Financial Products with
discontinuous payoff.
Succeeding questions will yield the possibility to drive discussion about higher complexity of
pricing and risk assessment models and hedging strategies that can be utilized in these types of
12 4.4 Supplementary Materials
The case is self-contained, providing students with all the information needed for the
resolution.
An excel spreadsheet ((Case Study) Excel) with information from Bloomberg is provided,
containing the historical information of the underlying assets, dividends from the companions
that compose the underlying as well as their implied volatilities. Financial information that
intends to provide a snapshot of the situation in financial markets at the valuation date will also
be provided: historical data from the main Indexes as well as historical yields from the relevant
countries.
Additionally, the term sheet of the product containing all is provided, containing detailed
information about the product.
Together with these documents, Instructors be provided with the teaching note, and an excel
file ((TN) Excel) containing all the information given in the excel file provided to students and
a proposed solution for the case questions.
A guide for both excel companion files is provided as an Attachment, containing a thorough
13 4.5 Discussion Outline
Given the complexity of the product addressed in the case, the Suggested Problems will be
structured into two groups.
Initial questions, are meant to create a simplified platform that can be used by instructors to
clarify or introduce the theoretical concepts of Structured Products. This should create a
stepping stone, giving instructors the opportunity to introduce the subjects necessary for the
case, by encouraging students to discuss the main issues that would have to be addressed by a
members of the derivatives team, preparing them for the analysis of Millennium Valor.
The following questions aim to inspire discussion about how to address the complexity risen
by having a product with multiple inter correlated underlying assets and the theoretical models
that can be applied for pricing, making judgments and predictions and creating an hedging
strategy.
Appropriate guidance regarding the particularities of Basket Options is recommended.
Particularly, how to take into account the basket’s internal correlations in the model, granted
the complexity and diversity of the existing theoretical models.
In the case where students are familiar with non-vanilla products and the necessary
methodology to immediately work with Millennium Valor, instructors are advised to skip
Question 2 and 3.
14 4.4 Case Analysis and Proposed Solution
The case is meant to provide students with a step by step progression, using the questions to
guide the students through the main problems that it means to address. It is also self-contained,
meaning that all the data needed for a solution is provided in the form of the product’s term sheet and a companion excel file.
This section is meant to advice instructors on the learning objectives of each proposed question
and propose a solution for the suggested problems.
4.4.1 Question 1
Initially, students will be asked to, using the product term sheet, break down the product into
simpler components. The objective is the basic premise of Structured Products, namely the
notion of how they are constructed from simpler financial components, that can be put together
to create a desired payoff.
Additionally, the analysis of the option component can be used to encourage discussion about
different types of option strategies, and research about different non-vanilla financial options,
yielding the chance of discovery some of the diversity of existing Exotic Options.
The information provided by De Weert (2011) and Hull (2011), suggests that Millennium Valor
can be constructed by putting together a Zero Coupon Bond and a Binary Option that has stocks
of 5 European Companies as its underlying assets as seen in Table 1.
Hull defines a Binary Option, specifically a cash or nothing binary option as an option that
“pays off nothing if the asset price ends up below the strike price at time T and pays a fixed
amount Q, if it ends up above the strike price”. In the case of Millennium Valor the Binary Option Payoff, Q is responsible for the variable return and the bond component will provide
15 (TN) Table 1
The recognition of the product composition will not only provide a deeper understanding about
how to construct this structured Product, but is also a necessary step for the analysis of
Millennium Valor.
4.4.2 Question 2.
The question requires students to think about a simplified version of Millennium Valor, where
the option component is depend on a single underlying assets: GDF Suez stocks.
This is meant to ease in the analysis of the original product, providing an opportunity to discuss
the available methodologies without the complexity that arises from dealing with a basket of
inter-correlated assets.
Instructors can utilize this Question to spur discussion and research about the different types
of approaches that can be applied to Financial Options and which ones are best suited for each
type of Product.
As explained in the discussion outline, if the Instructor intends that students should address the
real product immediately, Question 2 and 3 should be ignored and the case should continue
directly at Question 4.
Value Z Q
1 GDF Suez S1
2 Allianz S2
3 Iberdrola S3
4 Orange S4
5 Oni S5
Millennium Valor Components
U
n
d
e
rly
in
g
A
ss
e
ts
Security 2Y Zero Coupon Bond
16
For one possible solution, we should utilize the product term sheet and the data provided in the
case where we have access to Millennium Valor particular payoff conditions.
The product will guarantee the initial investment with an additional 0.30% fixed return.
Additionally, the product offers a Variable Return with the following condition
If at maturity date, the prices of all the underlying assets, are simultaneously equal or above
their price at initial date, the product will return the initial investment with a 5% variable return
over the notional amount.
Formally, the two possible payoff scenarios are presented in Table 2.
(TN) Table 2
The literature suggests several possibilities to address this type of product, such as the Binomial
Model suggested by Cox, Ross and Rubinstein (1979).
Given the introductory objective of the proposed question of serving as a stepping stone for the
methodologies that will be used to evaluate the main product Millennium Valor, it is
recommended to use a Monte Carlo approach is as the model to address this problem.
Thus, the proposed solution will follow the suggestions presented by Hull (2011) for the model
construction.
Condition Payoff
(Stocki) "i < 0% 0.30%
17
To build the simulation, a large number of possible paths in a risk neutral framework will be
generated, 10.0001 paths in the proposed solution, for each underlying asset, which in the case
of this simplified consists only of GDF Suez stocks
An equation to calculate the price movement for each time step with length ∆𝑡 can be found in
Hull (2011) and will be utilized to simulate a the possible paths.
𝑑𝑆 = 𝑆 𝑑𝑇 + 𝑆 𝑑𝑍 (1)
Where dZ is a Wiener Process, µ is the expected return in a risk neutral world and is the
volatility.
This methodology requires that the life time of the asset is divided into N smaller time periods
of length ∆𝑡, and works with the logarithm of S for greater accuracy.
It is important to clarify the assumptions in the methodology, particularly, the assumption that
expected risk neutral return µ and the asset’s volatility 𝜎𝑖2 remain constant.
By following this approach, we will utilize the following equation:
𝑆𝑖(𝑡 + ∆𝑡) = 𝑆𝑖(𝑡)exp [(𝑟𝑓 − 𝑑𝑦𝑖−𝜎𝑖 2
2 ) ∆𝑡 + 𝜀𝑖∗ 𝜎𝑖2∗ ∆𝑡]
(2)
Where 𝜀𝑖 is composed of values generated from a standard normal distribution, 𝑑𝑦𝑖 will be the
annualized dividend yield for GDF Suez and 𝑟𝑓 is the annualized risk free rate for the products
maturity, which in the proposed solution is assumed to be the 2 Year Interest Rate Swap at
current date, and 𝜎𝑖2 is the asset Implied Volatility inferred using GDF Suez quoted options.
1. It is important to note that given the computation time needed for such a large number of simulations instructors may require a smaller
18
After performing the simulation, a value for the option component can be found by calculating
the discounted average of the resulting payoffs.
Additionally, by comparing the number of simulations that predict a payoff of 5% or 0.30%
given the conditions of the products payoff. We are able to make predictions about the
probability of each payoff at maturity by dividing the frequency of each occurrence by the total
number of simulations.
4.4.3 Question 3.
This question requires students to, in an isolated manner, assess the impact of having a basket
of inter-correlated assets as underlying in their previous approach.
The intention is to underline and create discussion about the complexity risen from the basket
component of Millennium Valor, and the types of strategies that can be used to address it.
Instructors may take advantage of this question to introduce the Cholesky Decomposition
Method that is utilized in the proposed solution for Question 4.
The question does not require a practical solution, but it should become clear that correlation
will have to be taken into account when generating the possible paths for the each of the assets.
Specifically, correlation will have to be considered when applying the random shock
component of the simulation .
Since the basket is composed of inter-correlated assets, random shocks for each step of the
possible paths will have to take each other into consideration, otherwise the simulation would
19
4.4.4 Question 4.
Until this point, questions were used to provide an opportunity to introduce or develop a further
understanding of the tools and concepts needed to analyse the product, Millennium Valor.
By solving the case so far, students should be familiar with the concepts regarding Structured
Products, the Monte Carlo approach and how to utilize it to address multi-asset dependent
products.
This question requires students to perform the analysis of Millennium Valor. By doing so, it
pretends to make students take the place of an employee of Millennium Investment Bank at the
Derivatives desk and have them tackle a real life situation.
Given the multi-asset component of the product. This question provides the opportunity to
introduce and create discussion about more advanced quantitative methodologies, such as the
proposed Cholesky Decomposition created by André-Louis Cholesky (1910) and later utilized
for simulating systems with multiple correlated variables.
In this Question, it is critical to clarify the importance of the model construction for the issuer.
The model will allow the issuer to price the product at any given time, assess the probability
of each payoff at maturity and the calculation of sensitivity measures essential to hedge the risk
created by the various uncertainties surrounding the product.
To build the model for Millennium Valor, we can apply a similar methodology to that used in
Question 2 taking into account the additional complexity given by having a basket of correlated
assets as the underlying asset.
To calculate the value of the Option Component of the product a possible solution is to follow
the suggestions in Hull (2011). It will make use of 10.000 simulations for possible paths for
20
For each time step we will use equation 3 to calculate the price from the price of the previous
∆𝑡 interval equivalent to one day in the proposed solution.
𝑆𝑖(𝑡 + ∆𝑡) = 𝑆𝑖(𝑡)exp [(𝑟𝑓 − 𝑑𝑦𝑖 −𝜎𝑖 2
2 ) ∆𝑡 + 𝜀𝑖]
(3)
The random shocks 𝜀𝑖 for each 𝑠𝑡𝑜𝑐𝑘𝑖 are generated from n independent standard normal
distributions where n is the number of the composing assets of the basket.
To account for the correlation between the assets, as suggested by Haugh (2004) the generated
shocks 𝜀𝑖 will be subjected to a Cholesky Decomposition2 which will impart the correlations
of the n assets in 𝜀𝑖.
The decomposition is done utilizing the implied covariance matrix using the equation:
= 𝐿 × 𝐿𝑇 (4)
Where 𝐿 is a lower triangular matrix and 𝐿𝑇denotes the conjugate transpose of 𝐿.
By multiplying the matrix 𝐿𝑇 by the vector of 𝜀𝑖 uncorrelated samples produces a vector with
the assets covariance proprieties modelled.
We can now use equation 3 to simulate 10.000 possible paths for each of the assets.
Using the results of the simulation, the value of the Option Component is given by average of
the resulting payoffs obtained to value the Option Component of the Product, this is, the
expected payoff of the Option.
2Instructors should guide students in the research about theoretical models for addressing the correlation problem, Question 3 provides a
21
Furthermore, by comparing the number of simulations that predict a payoff of 5% and 0.30%
according to the product’s payoff conditions, we are able to make predictions about the
probability of each payoff at maturity by dividing the number of simulations where each payoff
occurs by the total number of simulations performed. Formally:
𝑃𝑟𝑜𝑏(𝑃𝑎𝑦𝑜𝑓𝑓) = (𝑂𝑏𝑠𝑒𝑟𝑣𝑎𝑡𝑖𝑜𝑛𝑠 𝑃𝑎𝑦𝑜𝑓𝑓 )
𝑁𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑆𝑖𝑚𝑢𝑙𝑎𝑡𝑖𝑜𝑛𝑠
(5)
To value of the Zero Coupon Bond component can be calculated by discounting a nominal of
100% at the annualized Cost of Funding for Millennium BCP.
The value of the Bond Z will be given by:
𝑍 = (1 + 𝐶𝑜𝑠𝑡 𝑜𝑓 𝐹𝑢𝑛𝑑𝑖𝑛𝑔)100 2 (6)
Millennium Valor value can now be calculated by the sum of the values of its components, the
Expected Payoff of the Option, Q and the value of the Zero Coupon Bond Z.
22
4.4.5 Question 5
This question intends to underline the importance that understanding the risks from issuing
Structured Products represents to the issuer. Additionally, it should be used to encourage
discussion about possible strategies can issuers can utilize to hedge against these risks.
Given the complexity of the product, instructors will also have the opportunity to explore the
utilization of the Greeks when a closed form calculation is not possible. This will provide
students with a deep intuition of what this measures represent and learn how to calculate3 their
values utilizing a simulation approach.
When dealing with these type of products, to reduce the risk of their position, issues can either
perform a back to back transaction or take action to reduce the risk of its position during the
maturity of the product.
To do this, the issuer has to consider the various sources of uncertainty, Greeks can be used to
measure the sensitivity of the position to each one of them.
The primary measure to be taken into account, is the exposure to the price of the underlying
assets given which is given by Delta, the value of the partial derivative of the Product Payoff
with respect to the underlying prices.
Since we are dealing with a product with a discontinuous payoff, to take the derivative, the
proposed solution will use the previous 10.000 simulations to calculate the Delta Value.
According to Taleb (1996), for variations in the underlying asset prices, the increase of this
prices may not have the same effect has a decrease of the same dimension.
3Given the complexity of the calculation of the Greeks in this setting and most models that try to address it, instructors should give special
23
Following this suggestion the Equation 8 can be used to calculate Delta:
𝐷𝑒𝑙𝑡𝑎𝐴 =𝑃𝑎𝑦𝑜𝑓𝑓( 𝑆𝐴+ ∆𝑆𝐴2∆𝑆) − 𝑃𝑎𝑦𝑜𝑓𝑓(𝑆𝐴− ∆𝑆𝐴) 𝐴
(8)
Where 𝑆𝐴 the initial is price for each asset A in the basket, and ∆𝑆𝐴 is the increase on the price
of the asset to measure its impact on the Payoff.
A common hedging strategy employed by issuers, is to make use of Dynamic Hedging, running
the Delta calculation periodically to adjust their position accordingly.
Gamma sensitivity can be used to give further insight. By measuring the sensitivity of the Delta
value to changes in the underlying prices, Gamma provides information about the rate of
change in the underlying prices over time; it is given by the second partial derivative of the
Product Payoff with respect to the underlying prices.
Given the path dependency, equation 9 can be utilized to calculate the value of Gamma.
By looking at its value, issuers can anticipate periods of higher sensitivity, where the delta
value should be watched more carefully and calculations should be performed with higher
frequency.
Given that the discontinuous payoff of the product and its dependency on 5 underlying assets,
the calculation of Gamma will only have be meaningful using a high number of simulations
where the small movements will produce noticeable changes to Delta.
Given the computational requirements instructors are advised to not require this calculation.
𝐺𝑎𝑚𝑚𝑎𝐴 =𝐷𝑒𝑙𝑡𝑎𝐴( 𝑆𝐴 + ∆𝑆𝐴) − 𝐷𝑒𝑙𝑡𝑎2 𝐴(𝑆𝐴− ∆𝑆𝐴)
24
4.4.6 Question 6
This question is meant to encourage critical thinking about Millennium Valor, and provide
space to debate opinions about the product at hand and the results reached during the case.
Instructors can make use of the financial and economic data provided to spur discussion and
encourage students to make a critical analysis of their results and use them to reach fundamental
conclusions about the product and its attractiveness in the economic environment during the
case timeframe.
More than in previous Questions, there is a large number of reasonable solutions depending on
the results obtained during the analysis of the product, since conclusions should be supported
by such results.
To assess the attractiveness of the product, several factors should be taken into account.
It is important to analyse alternatives to this product, such as the equity and fixed income
markets and compare them to the payoff of Millennium Valor.
Instructors should encourage critical analysis of the values reached in the calculations and
comprehension of the impact that the inputs for the calculation will have in the results.
Specifically, it is important to note by looking at equation 3 that the low yield environment and
relatively high dividend yield for all the assets will contribute to a lower probability that the
Option will pay at maturity, as will the low correlation between the assets.
Expected Payoff value is expected to have higher difference between different solutions,
leading to different comparisons between the calculated value of the product and the
25 5. Conclusion
Case Studies are of high value for education. By imparting real life experience in a document
and importing this experience to the classroom, they allow students to learn from this
experiences.
It was this objective that this case study tried to achieve, by using a real life learning experience
in the form of an internship at an Investment Bank, it does its best to bring to the Case the
valuable lessons acquired by the author during this period, to provide students with an
opportunity to also learn from this experience, and provide instructors with a tool to help
communicate their knowledge.
26 6. References
Austin, J. E. (1993). Teaching Notes: Communicating the Teacher's Wisdom. Harvard
Business School Pub.
Baxter, P., & Jack, S. (2008). Qualitative case study methodology: Study design and
implementation for novice researchers. The qualitative report, 13(4), 544-559.
Boyle, Phelim, Mark Broadie, and Paul Glasserman. (1997). Monte Carlo methods for
security pricing. Journal of economic dynamics and control 21.8: 1267-1321.7
Clawson, J. G., & Weatherford, L. Teaching Notes.
Cox, J. C., Ross, S. A., & Rubinstein, M. (1979). Option pricing: A simplified
approach. Journal of financial Economics, 7(3), 229-263.
De Weert, F. (2011). Exotic options trading (Vol. 564). John Wiley & Sons.
Lundberg, C. C., Rainsford, P., Shay, J. P., & Young, C. A. (2001). Case writing
reconsidered. Journal of Management Education, 25(4), 450-463.
Glasserman, P. (2003). Monte Carlo methods in financial engineering (Vol. 53). Springer
Science & Business Media.
George, A. L., & Bennett, A. (2005). Case studies and theory development in the social
sciences. MIT Press.
Gerring, John. Case Study Research: Principles and Practices. Cambridge University Press
27
Haugh, M. (2004). The Monte Carlo framework, examples from finance and generating
correlated random variables. Course Notes.
Krekel, M., de Kock, J., Korn, R., & Man, T. K. (2006). An analysis of pricing methods for
basket options. 70+ DVD’s FOR SALE & EXCHANGE, 181
Lundberg, C. C., Rainsford, P., Shay, J. P., & Young, C. A. (2001). Case writing
reconsidered. Journal of Management Education, 25(4), 450-463.Wiley
Petroni, N. C., & Sabino, P. (2013). Pricing and hedging Asian basket options with
Quasi-Monte Carlo simulations. Methodology and Computing in Applied Probability, 15(1), 147-163.
Taleb, Nassim. (1996). Dynamic Hedging: Managing Vanilla and Exotic Options. New York:
28 7. Attachments
Guide for Excel Companion Files
For easier comprehension, it is here provided a guide for the excel files that accompany the
case, explaining the relevant contents and locations within the files.
All the information provided in the files is taken from Bloomberg.
7.1 Teaching Note (TN) Excel File
For simplicity, sheets provided in (TN) Excel file are colour coded in the following manner:
8. Blue - Sheet that enables instructors to change the inputs and see the results of calculations.
9. Grey – Sheet used by VBA Subroutines to perform calculations and are provided for detailed consultation of the simulation steps.
10. Non-Coloured – Sheet containing financial or economic data or values to be used as input in the Blue Sheets.
(TN) Excel Sheets 1 – Index
1 Index
2 Simple Product Simulation
3 Millennium Valor Simulation
4 Cholesky Decomposition
5 Simple Product Random Walk
6 Random Shocks
7 Random Walk
8 Stocks
9 Correlations
10 Euribor and Swaps
11 Implied Volatilities
12 Dividends
13 Equity Indexes
14 France Yields
15 Italy Yields
16 Germany Yields
Millennium Valor Case Study
Instructors Guide
29
Given the number of sheets, the instructors guide opens with an Index describing all the
contents that can be found in the companion file. For easier navigation, the index provides
hyperlinks to all the sheets in the file. Additionally, at any time instructors may use the
command ctrl + I to return to the Index Page.
(TN) Excel Sheets 2 – Simulation for Simple Product
Simulation for Simple Product contains the results concerning Question 2. Instructors may
change the inputs regarding the underlying and the number of simulations and press simulation
to run the desired number of simulations.
Results of the simulation will be given in the same page, under outputs.
(TN) Excel Sheets 3 – Millennium Valor Simulation
Millennium Valor Simulation will contain all the information regarding Question 4. Instructors
may select the inputs regarding all the underlying assets contained in the basket, as well as the
number of simulations to perform. The output section will return all the calculations resulting
from the simulation.
Price Volatility Risk Free Dividend Yield Initial Price Final price
GDF SUEZ 100 15.00% 0.20% 2.00% 100 95.35155411
Number of Simulations 100
INPUTS
OUTPUTS
Simulation for Simple Product
Simulation
Volatility Risk-Free Div Yield Initial Price Average Final Price Number of Simulations 100 21.30% 0.16% 5.80% 100 1.0001 S Impact for Delta Calculation 1
17.76% 0.16% 5.10% 100 1.0001
18.20% 0.16% 5.61% 100 1.0007
25.87% 0.16% 5.26% 100 0.9992
20.81% 0.16% 6.52% 100 0.9990
Millennium Valor Simulation
INPUTS Securities
GDF SUEZ ALLIANZ IBERDROLA
ORANGE ONI
30 (TN) Excel Sheet 4 – Cholesky Decomposition
Excel Sheet 4 contains all the calculations regarding the Cholesky decomposition required as
part of the model built for Question 4.
(TN) Excel Sheet 5 – Simple Product Random Walk
Simple Product Random Walk will be used by the simulation for the Simple Product to perform
the random walks for the simulations.
This sheet contains all the steps for all the simulations performed and can be consulted for a
detailed view of such steps.
Covariance Matrix
Security Security GSZ ALV IBE ORA ENI Security GSZ ALV IBE ORA ENI
GDF SUEZ 1.664% GSZ 1.000 0.319 0.572 0.486 0.347 GSZ 0.028% 0.009% 0.016% 0.013% 0.009% ALLIANZ 1.676% ALV 0.319 1.000 0.523 0.299 0.539 ALV 0.009% 0.028% 0.015% 0.008% 0.014% IBERDROLA 1.693% IBE 0.572 0.523 1.000 0.375 0.439 IBE 0.016% 0.015% 0.029% 0.010% 0.012% ORANGE 1.646% ORA 0.486 0.299 0.375 1.000 0.320 ORA 0.013% 0.008% 0.010% 0.027% 0.008%
ONI 1.561% ENI 0.347 0.539 0.439 0.320 1.000 ENI 0.009% 0.014% 0.012% 0.008% 0.024%
1.66% 1.68% 1.69% 1.65% 1.56%
MATRIX A A TRANSPOSED COVARIANCE MATRIX
1.664% 0.000% 0.000% 0.000% 0.000% 1.664% 0.535% 0.968% 0.800% 0.542% 0.028% 0.009% 0.016% 0.013% 0.009% 0.535% 1.589% 0.000% 0.000% 0.000% 0.000% 1.589% 0.608% 0.250% 0.705% 0.009% 0.028% 0.015% 0.008% 0.014% 0.968% 0.608% 1.248% 0.000% 0.000% 0.000% 0.000% 1.248% 0.095% 0.165% 0.016% 0.015% 0.029% 0.010% 0.012% 0.800% 0.250% 0.095% 1.414% 0.000% 0.000% 0.000% 0.000% 1.414% 0.139% 0.013% 0.008% 0.010% 0.027% 0.008% 0.542% 0.705% 0.165% 0.139% 1.264% 0.000% 0.000% 0.000% 0.000% 1.264% 0.009% 0.014% 0.012% 0.008% 0.024%
Correlation Matrix
Cholesky Decomposition
100 100.3995 100.4548 100.6001 100.5513 100.3857 101.2835 99.91737 100.2539 101.0377 101.3068 101.0334 101.0538 101.4241 100.6375 100.5062 99.87288 99.95706 100.6248 100.0111 100.8969 99.06323 97.99579 97.67699 98.03788 98.4338 100 99.75281 100.3794 100.1004 101.5891 102.2079 102.2512 101.8212 101.5709 101.4342 102.0847 101.6826 100.6214 102.4985 101.7274 101.9898 102.7591 102.7842 102.3703 101.6192 102.6718 102.7172 100.9843 101.2815 99.07666 98.2289 100 99.85955 99.72972 100.1626 100.3109 101.4274 101.0321 101.3287 99.53068 98.08382 96.50745 97.67886 99.19524 100.1526 100.1998 101.0422 99.80999 100.8145 100.5077 100.8453 99.75809 100.114 99.85295 101.4027 102.749 105.0833 100 100.0467 99.77294 100.0867 99.17482 100.8194 98.9163 98.9536 98.70487 98.6007 98.213 99.14184 98.92535 98.87537 100.4288 100.6394 101.1932 101.9234 100.9363 101.2135 102.0169 101.9915 102.5661 101.5836 99.62108 99.05888 100 100.8233 101.9035 101.3149 102.0683 101.1077 101.8042 102.9982 103.7057 105.4852 105.5737 106.8365 105.2188 104.4784 103.3449 101.9165 101.6019 100.693 100.9313 101.4285 102.911 103.5995 102.6735 103.3224 103.92 104.152 100 97.55026 96.9762 96.55172 96.87415 95.57071 96.1818 96.7185 98.27545 97.36282 97.429 97.8639 98.67011 98.41038 99.49831 99.17863 99.04924 98.70902 97.1874 97.30998 97.44167 96.97796 98.01931 96.70809 96.60753 97.45088 100 100.2331 101.6484 101.989 102.2836 103.9199 105.3136 104.9313 105.457 105.6429 105.8721 106.4749 107.2433 106.9164 106.8371 106.4404 108.9524 108.9982 107.1006 107.6325 106.4975 104.5518 104.2628 103.9656 103.4488 105.6333 100 99.77332 99.41937 96.9208 98.40124 98.08981 99.19151 100.9087 100.6212 100.8046 101.0711 101.1851 100.7925 100.4923 100.3977 100.4145 100.9733 99.86255 100.7103 100.3904 100.2349 99.19544 99.37425 99.52193 99.48756 98.45152 100 99.76124 100.7873 100.5055 102.0164 102.3402 102.3988 102.0102 101.6222 100.5838 99.35611 97.05338 97.09331 96.37593 95.72502 95.75537 95.36812 96.34635 96.48922 96.69032 94.50886 95.11102 93.59644 93.80483 94.81118 94.14637 100 100.126 98.36054 100.1504 100.8662 98.68756 98.25022 98.86232 97.94013 98.14639 98.30229 97.79926 97.75591 98.35958 98.20593 98.44985 97.68925 98.6196 98.89628 97.3369 95.85448 95.3605 97.66946 97.9013 97.75977 97.72505 100 101.0894 100.1627 100.5115 100.5147 100.2325 100.0966 98.98453 99.55592 100.4283 99.44379 100.0697 99.44284 100.1291 99.1711 98.06951 97.19437 96.65057 96.29988 98.50564 99.65747 100.3607 100.0074 100.8058 100.2831 100.0316 100 99.85289 101.3729 101.8005 101.1848 100.6409 101.2392 101.1705 101.7944 102.335 101.7987 100.5594 99.06732 98.10978 99.98912 99.53191 99.44822 100.0222 100.8527 101.4093 102.9833 102.8919 102.3552 102.3689 103.1752 103.6994 100 99.77609 99.03503 98.0369 98.32505 97.93032 98.6132 97.6891 98.07539 97.48213 98.72505 99.54212 99.69265 102.0509 102.4732 102.8084 102.5642 101.2295 100.1978 100.5966 101.5079 101.5589 102.2221 102.8921 103.1129 101.9902 100 98.14983 97.46905 98.56996 98.64173 98.55498 99.60714 99.57981 100.127 100.7834 102.4612 102.5586 103.0987 102.5797 103.7464 103.6382 104.0438 103.4714 102.1601 100.5751 100.1766 99.2593 98.98455 98.63962 98.9333 97.50142 100 101.1876 100.5914 100.4109 100.3614 101.5541 101.0344 101.1428 101.9566 101.978 101.933 100.4565 100.4572 101.4657 102.2593 103.731 102.3838 102.0341 100.6672 100.6841 101.5274 100.7033 101.2371 101.6737 103.8445 104.2714 100 98.88288 97.16537 97.58552 97.1449 97.21402 97.47696 96.39379 96.06214 96.78588 97.92612 96.1669 96.89787 96.35343 96.81929 96.50372 96.09853 95.50004 95.46307 96.42619 96.22531 96.84469 96.90042 97.79427 98.85803 99.16129 100 98.58992 98.05236 99.11986 99.26558 99.8874 100.3347 101.1881 99.97075 99.18717 98.17698 98.42841 98.83967 99.34833 100.9419 100.8663 101.6638 101.5152 100.1678 99.65156 99.83236 99.41417 98.64881 97.98983 97.13147 96.52753 100 99.60695 100.0667 99.19158 100.0773 100.3573 100.8841 100.1934 99.50133 98.50832 98.35463 98.34463 97.47963 96.8489 98.47093 97.59269 98.17709 98.12003 97.57735 98.63507 99.33637 98.99814 99.40786 99.53456 98.97325 97.76361 100 98.9889 99.36096 101.3594 101.7746 101.2709 100.8033 100.8517 101.6755 101.5777 101.8621 102.9077 102.3099 102.9196 104.0405 104.9673 105.5265 104.9001 106.4182 105.9606 105.4777 104.9538 103.7499 104.4884 103.2564 103.4646 100 100.1368 99.92838 99.31965 99.21915 98.86755 99.25914 100.1265 99.35873 98.67872 100.4206 101.7056 100.328 99.46115 98.56541 99.15804 98.05833 98.80605 99.22774 101.0096 100.659 101.5908 102.011 100.6804 101.2758 100.0898 100 99.02205 100.3022 99.75475 101.0627 101.593 100.5218 100.5566 99.78824 99.29417 100.8838 100.1148 100.5935 100.0727 100.0429 99.48587 99.06923 100.8475 102.2005 102.28 101.9397 101.8025 99.56484 99.60848 98.7875 100.1699
31 (TN) Excel Sheet 6 – Random Shocks
Sheet 6 is used to apply the Cholesky Decomposition to the random shocks required for
Millennium Valor Monte Carlo Simulation, it contains the calculations to impart the correlation
between the assets composing the underlying to a set of random shocks.
Additionally, the sheet contains one random walk that will be used by the simulation to perform
the required number of trials for each asset.
(TN) Excel Sheet 7 – Random Walk
12/06/2015 15/06/2015 16/06/2015 17/06/2015 18/06/2015 19/06/2015 22/06/2015 23/06/2015 24/06/2015 25/06/2015 26/06/2015 29/06/2015 30/06/2015 01/07/2015 02/07/2015 03/07/2015 06/07/2015 GSZ 0.74895982 -0.17184925 0.32922501 1.31021834 -0.5365099 0.97384713 -0.2332286 0.28347238 -0.7085142 -0.0308806 -0.3644033 -1.6804749 0.44106854 0.13366094 0.08249561 -1.1773814 -0.8698747 ALV 0.39193889 0.984191923 -0.8817266 -0.3160254 0.21518401 0.9939404 -1.1922696 1.26006774 -0.5955751 -0.6742958 -0.3253018 0.85175786 -0.3219093 1.13028983 1.49979911 0.17347677 -0.9399959 IBE -0.2938132 0.343373807 0.35192143 -1.0379858 0.97479729 -2.199093 -0.2012518 -0.8124627 -0.3597705 -1.1662884 -0.098648 0.99419917 -0.0191188 0.70631289 -1.0981127 -0.8179553 -0.2220441 ORA -0.843154 -0.28757851 -0.5031823 1.07368378 -0.3073822 -1.4230396 -0.342564 -0.3569147 0.46896655 0.99658649 -1.0596831 0.34292249 -1.2830093 0.93836157 0.45512659 -0.1817631 -0.65887 ENI -0.2628311 -1.1047505 -0.6597064 0.57960784 -0.2635597 0.90666138 0.7139287 -0.212098 1.01437733 -0.0847809 -0.0295858 -1.0767541 -0.1398068 -0.7646683 0.05075885 -0.5342641 0.4308964
12/06/2015 15/06/2015 16/06/2015 17/06/2015 18/06/2015 19/06/2015 22/06/2015 23/06/2015 24/06/2015 25/06/2015 26/06/2015 29/06/2015 30/06/2015 01/07/2015 02/07/2015 03/07/2015 06/07/2015 GSZ 0.01246574 -0.00286027 0.00547965 0.02180737 -0.0089297 0.01620878 -0.0038819 0.00471814 -0.0117926 -0.000514 -0.0060652 -0.0279699 0.00734118 0.00222466 0.00137306 -0.0195964 -0.0144783 ALV 0.01023065 0.014715463 -0.0122463 0.00198527 0.00054968 0.02099613 -0.0201867 0.02153238 -0.0132492 -0.0108766 -0.0071159 0.00454538 -0.0027554 0.01866979 0.02426603 -0.0035395 -0.0195833 IBE 0.00596844 0.008608171 0.00221765 -0.0021919 0.00828147 -0.0119745 -0.012022 0.00026757 -0.0149735 -0.018958 -0.0067383 0.00131872 0.00207418 0.01698521 -0.0037858 -0.020555 -0.0169117 ORA -0.0052257 -0.00265408 -0.0063512 0.02388886 -0.0071767 -0.0119239 -0.0098814 -0.0003963 -0.0008691 0.01105106 -0.0188039 -0.0055258 -0.0154327 0.01783128 0.00980487 -0.0123311 -0.0188359 ENI 0.00183766 -0.00778806 -0.0128944 0.01197228 -0.0035355 0.01812768 -0.0014563 0.00590026 0.00484428 -0.006536 -0.0062804 -0.0145839 -0.0034673 0.00150337 0.01048395 -0.0135126 -0.0071777
12/06/2015 15/06/2015 16/06/2015 17/06/2015 18/06/2015 19/06/2015 22/06/2015 23/06/2015 24/06/2015 25/06/2015 26/06/2015 29/06/2015 30/06/2015 01/07/2015 02/07/2015 03/07/2015 06/07/2015 GSZ 100 0.9968075 1.00515555 1.02170218 0.99077576 1.01599807 0.99578968 1.00439041 0.98794337 0.99914905 0.99361796 0.97208961 1.00702843 1.00188911 1.00103626 0.98026361 0.98529362 ALV 100 1.014569934 0.98758081 1.00173613 1.00029907 1.02096216 0.97977004 1.0215098 0.98659082 0.98893444 0.99266047 1.00430396 0.9969985 1.01858982 1.02430607 0.99621706 0.98036147 IBE 100 1.008369621 1.00194616 0.99753774 1.00804024 0.98782685 0.98777987 0.99999419 0.98486876 0.98095234 0.99301281 1.00104589 1.00180242 1.01685225 0.995949 0.97938704 0.98296178 ORA 100 0.997025551 0.99334622 1.02384389 0.99252652 0.98782599 0.98984572 0.99927921 0.99880678 1.01078399 0.98105309 0.99416652 0.98436604 1.01766062 1.00952515 0.98742384 0.98102173 ENI 100 0.991916821 0.98686465 1.01171238 0.99614395 1.01795907 0.99821733 1.00558785 1.00452653 0.99315952 0.99341344 0.98519881 0.99621196 1.0011761 1.01020774 0.98625476 0.99252244
Random Shocks
Random Shocks with Correlation
Random Shocks
Random Walk
32
Random walk sheet will be used to perform the defined number of simulations for Millennium
Valor simulation, similarly to Simple Product Random Walk it is provided for an in depth
consultation of the steps evolved in each asset’s defined number of trials.
(TN) Excel Sheet 8 – Stock Prices
Stock Prices sheet contains historical price information and daily returns for all companies
contained in Millennium Valor underlying Basket.
(TN) Excel Sheet 9 - Correlations
Sheet 9 contains all information about the correlations of the assets composing the product’s basket of underlying’s.
Source: Bloomberg
GSZ FP Equity ALV GY Equity IBE SM Equity ORA FP Equity ENI IM Equity
Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST
05/01/2010 30.475 05/01/2010 88.81 05/01/2010 5.8373 05/01/2010 17.72 05/01/2010 18.23
06/01/2010 30.45 -0.08% 06/01/2010 89.5 0.77% 06/01/2010 5.8295 -0.13% 06/01/2010 17.665 -0.31% 06/01/2010 18.25 0.11%
07/01/2010 30.085 -1.21% 07/01/2010 88.47 -1.16% 07/01/2010 5.7923 -0.64% 07/01/2010 17.445 -1.25% 07/01/2010 18.36 0.60%
08/01/2010 30.375 0.96% 08/01/2010 87.99 -0.54% 08/01/2010 5.7862 -0.11% 08/01/2010 17.33 -0.66% 08/01/2010 18.37 0.05%
11/01/2010 30.375 0.00% 11/01/2010 87 -1.13% 11/01/2010 5.7871 0.02% 11/01/2010 17.295 -0.20% 11/01/2010 18.56 1.03%
12/01/2010 30.22 -0.51% 12/01/2010 86.63 -0.43% 12/01/2010 5.7827 -0.08% 12/01/2010 17.35 0.32% 12/01/2010 18.19 -2.01%
13/01/2010 29.82 -1.33% 13/01/2010 86.78 0.17% 13/01/2010 5.7836 0.02% 13/01/2010 17.405 0.32% 13/01/2010 18.23 0.22%
14/01/2010 29.065 -2.56% 14/01/2010 85.87 -1.05% 14/01/2010 5.7758 -0.13% 14/01/2010 17.32 -0.49% 14/01/2010 18.5 1.47%
15/01/2010 28.355 -2.47% 15/01/2010 84.77 -1.29% 15/01/2010 5.7004 -1.31% 15/01/2010 17.09 -1.34% 15/01/2010 18.35 -0.81%
18/01/2010 28.975 2.16% 18/01/2010 84.79 0.02% 18/01/2010 5.729 0.50% 18/01/2010 17.1 0.06% 18/01/2010 18.45 0.54%
19/01/2010 29.2 0.77% 19/01/2010 85.18 0.46% 19/01/2010 5.7637 0.60% 19/01/2010 17.295 1.13% 19/01/2010 18.35 -0.54%
20/01/2010 28.73 -1.62% 20/01/2010 83.06 -2.52% 20/01/2010 5.6311 -2.33% 20/01/2010 17.01 -1.66% 20/01/2010 17.98 -2.04%
21/01/2010 28.51 -0.77% 21/01/2010 81.9 -1.41% 21/01/2010 5.5549 -1.36% 21/01/2010 16.925 -0.50% 21/01/2010 17.74 -1.34%
22/01/2010 28.27 -0.85% 22/01/2010 81.39 -0.62% 22/01/2010 5.5029 -0.94% 22/01/2010 16.92 -0.03% 22/01/2010 17.5 -1.36%
ONI
Stock Prices
GDF SUEZ ALLIANZ IBERDROLA ORANGE
Source: Bloomberg
CORRELATION COVARIANCE
Security GSZ ALV IBE ORA ENI Security GSZ ALV IBE ORA ENI
GSZ 1 0.319 0.572 0.486 0.347 GSZ 42.67 14.002 28.026 21.287 12.678
ALV 0.319 1 0.523 0.299 0.539 ALV 14.002 45.208 26.397 13.495 20.301
IBE 0.572 0.523 1 0.375 0.439 IBE 28.026 26.397 56.326 18.871 18.455
ORA 0.486 0.299 0.375 1 0.32 ORA 21.287 13.495 18.871 44.934 12.01
ENI 0.347 0.539 0.439 0.32 1 ENI 12.678 20.301 18.455 12.01 31.37
33 (TN) Excel Sheet 10 – Euribor and Swaps
In the Euribor and Swaps sheet, instructors can find historical information for the assets
assumed as risk free in the proposed solution. Information for Euribor 12, 6, 3 and 1 Month are
provided as well as the 2 Year maturity interest rate Swaps.
All values in this sheet are presented in basis points.
(TN) Excel Sheet 11 – Implied Volatility
EUR012M Index EUR006M Index EUR003M Index EUR001M Index EUSA2 Curncy
Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST
02/01/2014 0.555 02/01/2014 0.387 02/01/2014 0.284 02/01/2014 0.214 01/01/2014 0.538 03/01/2014 0.551 03/01/2014 0.381 03/01/2014 0.28 03/01/2014 0.208 02/01/2014 0.5251
06/01/2014 0.55 06/01/2014 0.38 06/01/2014 0.28 06/01/2014 0.204 03/01/2014 0.515
07/01/2014 0.55 07/01/2014 0.381 07/01/2014 0.28 07/01/2014 0.202 06/01/2014 0.521
08/01/2014 0.552 08/01/2014 0.383 08/01/2014 0.281 08/01/2014 0.203 07/01/2014 0.5231 09/01/2014 0.557 09/01/2014 0.388 09/01/2014 0.282 09/01/2014 0.205 08/01/2014 0.553 10/01/2014 0.557 10/01/2014 0.39 10/01/2014 0.282 10/01/2014 0.208 09/01/2014 0.5291 13/01/2014 0.557 13/01/2014 0.389 13/01/2014 0.282 13/01/2014 0.207 10/01/2014 0.504
14/01/2014 0.557 14/01/2014 0.39 14/01/2014 0.282 14/01/2014 0.208 13/01/2014 0.495
15/01/2014 0.565 15/01/2014 0.397 15/01/2014 0.29 15/01/2014 0.216 14/01/2014 0.5251
16/01/2014 0.572 16/01/2014 0.405 16/01/2014 0.3 16/01/2014 0.234 15/01/2014 0.5371
17/01/2014 0.571 17/01/2014 0.408 17/01/2014 0.302 17/01/2014 0.243 16/01/2014 0.504
20/01/2014 0.57 20/01/2014 0.408 20/01/2014 0.302 20/01/2014 0.24 17/01/2014 0.4871
21/01/2014 0.571 21/01/2014 0.409 21/01/2014 0.302 21/01/2014 0.242 20/01/2014 0.4821 22/01/2014 0.57 22/01/2014 0.406 22/01/2014 0.301 22/01/2014 0.241 21/01/2014 0.4841
23/01/2014 0.57 23/01/2014 0.404 23/01/2014 0.3 23/01/2014 0.24 22/01/2014 0.4931
24/01/2014 0.567 24/01/2014 0.401 24/01/2014 0.3 24/01/2014 0.238 23/01/2014 0.4731
27/01/2014 0.569 27/01/2014 0.404 27/01/2014 0.3 27/01/2014 0.239 24/01/2014 0.4781
2Y SWAPS EURIBOR 12M
Euribor and Swaps
Source: Bloomberg Information in Basis Points
EURIBOR 6M EURIBOR 3M EURIBOR 1 M
% Moneyness1) IBE SM: LIVE 2 YR C (P% Moneyne1) ALV GY: % Moneyne1) ORA FP: LIVE 2 YR C (Price% Moneyne1) ENI IM: L
80% 19.854 80% 17.805 80% 26.868 80% 23.034 85% 19.039 85% 17.792 85% 26.57 85% 22.319 90% 18.538 90% 17.779 90% 26.321 90% 21.766 95% 18.048 95% 17.771 95% 26.095 95% 21.252 97.5% 17.733 97.5% 17.768 97.5% 25.983 97.5% 21.013 100% 18.195 100% 17.764 100% 25.87 100% 20.811 102.5% 18.791 102.5% 17.761 102.5% 25.771 102.5% 20.946 105% 18.783 105% 17.757 105% 25.683 105% 21.081 110% 18.561 110% 17.754 110% 25.507 110% 20.872 115% 18.251 115% 17.752 115% 25.33 115% 20.587 120% 18.09 120% 17.749 120% 25.219 120% 20.215
IBEDROLA ALLIANZ ORANGE ONI
IMPLIED VOLATILITY
Source: Bloomberg
34
In sheet 11, instructors can find the implied volatilities given by the quoted Options for all the
product’s underlying assets. These volatilities will be used as inputs in Sheet 3 for the
simulation of Millennium Valor underlying basket.
(TN) Excel Sheet 12 – Dividends
Sheet 12 contains the relevant dividends for all the underlying assets, dividends during the
products maturity will be used as inputs in Sheet 3.
(TN) Excel Sheet 13 – Equity Indexes
The Equity Indexes sheet contains historical price information for the main indexes. It means
to provide an overview of the financial environment in the timeframe surrounding the case.
Ticker ENGI FP Equity Ticker ALV GY Equity Ticker IBE SM Equity Ticker ORA FP Equity Ticker ENI IM Equity Est. Ex-Date BDVD Forecast Est. Ex-Date BDVD Forecast Est. Ex-Date BDVD Forecast Est. Ex-Date BDVD Forecast Est. Ex-Date BDVD Forecast
26/02/2015 0.5 02/07/2015 0.3 08/06/2015 0.4 21/09/2015 0.4
29/07/2015 0.5 10/05/2015 7.3 13/01/2016 0.125 07/12/2015 0.2 18/05/2015 0.56
02/05/2016 0.5 05/05/2016 7.4 04/07/2016 0.145 06/06/2016 0.4 23/05/2016 0.4
17/10/2016 0.5 11/05/2017 7.5 13/01/2017 0.125 06/12/2016 0.2 19/09/2016 0.4
02/05/2017 0.55 10/05/2018 7.6 03/07/2017 0.145 06/06/2017 0.4 22/05/2017 0.4
16/10/2017 0.55 09/05/2019 7.7 15/01/2018 0.135 06/12/2017 0.3 18/09/2017 0.45
30/04/2018 0.55 03/07/2018 0.165 06/06/2018 0.45 21/05/2018 0.45
15/10/2018 0.6 14/01/2019 0.15 05/12/2018 0.3 17/09/2018 0.5
29/04/2019 0.6 02/07/2019 0.18 05/06/2019 0.5 20/05/2019 0.5
DIVIDENDS
Source: Bloomberg Information in Basis Points
GDF SUEZ ALLIANZ IBERDROLA ORANGE ONI
Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST Date PX_LAST
05/01/2010 6031.86 05/01/2010 4012.91 05/01/2010 5522.5 05/01/2010 8677.69 05/01/2010 2462.3 05/01/2010 10681.83 05/01/2010 1136.52
06/01/2010 6034.33 06/01/2010 4017.67 06/01/2010 5530.04 06/01/2010 8680.3 06/01/2010 2472.83 06/01/2010 10731.45 06/01/2010 1137.14
07/01/2010 6019.36 07/01/2010 4024.8 07/01/2010 5526.72 07/01/2010 8763.93 07/01/2010 2436.4 07/01/2010 10681.66 07/01/2010 1141.69
08/01/2010 6037.61 08/01/2010 4045.14 08/01/2010 5534.24 08/01/2010 8839.75 08/01/2010 2440.42 08/01/2010 10798.32 08/01/2010 1144.98
11/01/2010 6040.5 11/01/2010 4043.09 11/01/2010 5538.07 11/01/2010 8821.6 11/01/2010 2473.5 12/01/2010 10879.14 11/01/2010 1146.98
12/01/2010 5943 12/01/2010 4000.05 12/01/2010 5498.71 12/01/2010 8733.64 12/01/2010 2448.71 13/01/2010 10735.03 12/01/2010 1136.22
13/01/2010 5963.14 13/01/2010 4000.86 13/01/2010 5473.48 13/01/2010 8739.85 13/01/2010 2458.05 14/01/2010 10907.68 13/01/2010 1145.68
14/01/2010 5988.88 14/01/2010 4015.77 14/01/2010 5498.2 14/01/2010 8697.31 14/01/2010 2451.98 15/01/2010 10982.1 14/01/2010 1148.46
15/01/2010 5875.97 15/01/2010 3954.38 15/01/2010 5455.37 15/01/2010 8494.85 15/01/2010 2442.38 18/01/2010 10855.08 15/01/2010 1136.03
18/01/2010 5918.55 18/01/2010 3977.46 18/01/2010 5494.39 18/01/2010 8529.28 18/01/2010 2465.3 19/01/2010 10764.9 19/01/2010 1150.23
19/01/2010 5976.48 19/01/2010 4009.67 19/01/2010 5513.14 19/01/2010 8482.86 19/01/2010 2489.42 20/01/2010 10737.52 20/01/2010 1138.04
20/01/2010 5851.53 20/01/2010 3928.95 20/01/2010 5420.8 20/01/2010 8337.91 20/01/2010 2489.45 21/01/2010 10868.41 21/01/2010 1116.48
21/01/2010 5746.97 21/01/2010 3862.16 21/01/2010 5335.1 21/01/2010 8241.21 21/01/2010 2470.51 22/01/2010 10590.55 22/01/2010 1091.76
22/01/2010 5695.32 22/01/2010 3820.78 22/01/2010 5302.99 22/01/2010 8118.39 22/01/2010 2407.78 25/01/2010 10512.69 25/01/2010 1096.78
25/01/2010 5631.37 25/01/2010 3781.85 25/01/2010 5260.31 25/01/2010 8105.07 25/01/2010 2417.87 26/01/2010 10325.28 26/01/2010 1092.17
26/01/2010 5668.93 26/01/2010 3807.04 26/01/2010 5276.85 26/01/2010 8100.74 26/01/2010 2388.64 27/01/2010 10252.08 27/01/2010 1097.5
27/01/2010 5643.2 27/01/2010 3759.8 27/01/2010 5217.47 27/01/2010 8040.16 27/01/2010 2373.41 28/01/2010 10414.29 28/01/2010 1084.53
28/01/2010 5540.33 28/01/2010 3688.79 28/01/2010 5145.74 28/01/2010 7930.24 28/01/2010 2362.93 29/01/2010 10198.04 29/01/2010 1073.87
29/01/2010 5608.79 29/01/2010 3739.46 29/01/2010 5188.52 29/01/2010 7927.31 29/01/2010 2382.64 01/02/2010 10205.02 01/02/2010 1089.19
01/02/2010 5654.48 01/02/2010 3762.01 01/02/2010 5247.41 01/02/2010 8011.77 01/02/2010 2363.18 02/02/2010 10371.09 02/02/2010 1103.32
02/02/2010 5709.66 02/02/2010 3812.13 02/02/2010 5283.31 02/02/2010 8061.59 02/02/2010 2374.35 03/02/2010 10404.33 03/02/2010 1097.28
EQUITY INDEXES
WIG20 Index NKY Index SPX Index
Source: Bloomberg