Session 3a

1
Session 3a
2
Overview

• Finance Simulation Models
• Forecasting
• Retirement Planning
• Butterfly Strategy
• Risk Management
• Introduction to VaR
• Currency Risk
• VaR with Live Data
• Securities Pricing
• Black-Scholes
• Electricity Option
• Miscellaneous
• Intro to Retailer
• Dynamic vs. Static
• Monte Carlo vs. Latin Hypercube
• Review of Binomial

3
Example 1 Retirement Planning
Amanda has 30 years to save for her retirement.
At the beginning of each year, she puts 5000
into her retirement account. At any point in
time, all of Amanda's retirement funds are tied
up in the stock market. Suppose the annual return
on stocks follows a normal distribution with mean
12 and standard deviation 25. What is the
probability that at the end of 30 years, Amanda
will have reached her goal of having 1,000,000
for retirement? Assume that if Amanda reaches her
goal before 30 years, she will stop investing.
4
(No Transcript)
5
The annual investment activities (columns A-D,
beginning in row 5) actually extend down to row
35, to include 30 years of simulated returns.
The range C6C35 will be random numbers,
generated by Crystal Ball. We could track
Amandas simulated investment performance either
with cell F5 (simply D35, the final amount in
Amandas retirement account), or with F4 (the
maximum amount over 30 years). Using F4 allows us
to assume that she would stop investing if she
ever reached 1,000,000 at any time during the 30
years, which is the assumption given in the
problem statement. Cell H1 is either 1 (she made
it to 1 million) or 0 (she didnt). Over many
trials, the average of this cell will be out
estimate of the probability that Amanda does
accumulate 1 million. This will be a Crystal
Ball forecast cell.
6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
(No Transcript)
12
(No Transcript)
13
(No Transcript)
14
It looks like Amanda has about a 52 chance of
meeting her goal of 1 million in 30 years.
15
Example 2 Butterfly
The SJ index is a measure of overall equity
value in the software publishing industry.
Shares of a tracking mutual fund (a fund that
tracks this index) are available from Avant Garde
Investments, Inc. Shares in the mutual fund are
currently available at a price of 605.
16
(No Transcript)
17
We are considering investing 100,000 in the SJ
index over the next month, based on our
estimation that the SJs level one month from
now is a log-normally distributed random variable
with a mean of 605 and a one month standard
deviation of 30. An analyst proposes that in
addition to investing the 100,000 in the SJ
index, we take some positions in call options. He
suggests selling 200 options contracts (1 option
contract is an option to purchase 100 shares) at
the 605 strike price, and buying 100 option
contracts each of the 600 and 610 strike
prices. What do you think of this scheme? Does
it have any advantage over simply investing all
the money in the index? Assume that there are no
transaction costs.
18
(No Transcript)
19
Put in quantities bought and sold, according to
the analysts proposal
20
Figure out how much cash is going out, in D10D17
21
Cell A5 will be an assumption the ending price
of the option in one month. Put cell references
to A5 into H10H17.
22
In I10I17 enter a formula to calculate the
payoff for options bought, as a function of the
random ending price of the index.
23
Similarly, in J10J17 enter a formula to
calculate the payoff for options sold, as a
function of the random ending price of the index.
24
In B19B20, calculate how many shares of the
index are being purchased.
25
In E10E17, calculate the amount of cash coming
back in at the end of the month.
26
In D2F2, calculate the P/L from the index.
27
In D3F3, calculate the P/L from the options.
28
In D4F4, calculate the total P/L.
29
In F6 calculate the difference between the two
strategies (with and without the options).
30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
A new Excel trick DataTable
36
Select A24B55, then Data - Table
37
(No Transcript)
38
(No Transcript)
39
3. Evaluation of Hedging Strategies
It is July 1, 2002, and international
entrepreneurs Clifford Kearns (CK) are
concerned about volatility in the exchange rates
between U.S. dollars and certain European
currencies. CK have incurred costs in dollars
to develop, produce, and distribute merchandise
to Norway, Switzerland, and Great Britain, for
which they expect to realize revenues in 12
months.
40
Specifically, they expect to earn 1 million units
each of British pounds, Swiss francs, and
Norwegian kroner. Based on current exchange
rates, this should result in 2,337,700 in
revenue (see current rates below).
41
Unfortunately, it is possible that one or more of
these currencies could devalue against the dollar
in that one year, causing CK to realize a
smaller total revenue (in dollars) than expected.
CK has turned to their investment bank, Nuccio,
Noto, and Rizzi (NNR) for advice. NNR has
recommended buying 1.3 million 1-year Euro put
options with a strike price of 0.98, for 0.0432
each. NNR claims that this hedging strategy will
substantially decrease the risk of a large loss
due to exchange rate fluctuations.
42
(a) Create a simulation model to study the
unhedged distribution of revenue for CK, using
the historical exchange rate data in Exhibit 2.
Make a histogram and report summary statistics.
What is the 5 value at risk (VAR) for CKs
revenue from these three countries over the next
12 months? What is the probability that CKs
revenue will be less than 2,087,700 (i.e., a
250,000 loss or worse)? (b) Create a simulation
model to study the hedged distribution of
revenue for CK. Make a histogram and report
summary statistics with the policy recommended by
NNR. What is the 5 VAR for CKs revenue from
these three countries over the next 12 months?
What is the probability that CKs revenue will
be less than 2,087,700?
43
(No Transcript)
44
(No Transcript)
45
(No Transcript)
46
Here are summary statistics for each of the
currencies returns against the dollar, including
a t-test to see if the means are significantly
different from zero (they are not)
47
(No Transcript)
48
(No Transcript)
49
(No Transcript)
50
(No Transcript)
51
(No Transcript)
52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
It turns out that all four of our variables can
be modeled reasonably well by normal
distributions normal is always either the best
fit or the second best fit. Well use normal
distributions with means of zero and standard
deviations estimated from our sample data.
56
(No Transcript)
57
(No Transcript)
58
(No Transcript)
59
(No Transcript)
60
(No Transcript)
61
(No Transcript)
62
(No Transcript)
63
(No Transcript)
64
(No Transcript)
65
(No Transcript)
66
(No Transcript)
67
(No Transcript)
68
(No Transcript)
69
(No Transcript)
70
(No Transcript)
71
(No Transcript)
72
(No Transcript)
73
(No Transcript)
74
(No Transcript)
75
(No Transcript)
76
(No Transcript)
77
(No Transcript)
78
(No Transcript)
79
(No Transcript)
80
Example Daily VaR for a 2-stock Portfolio

• Consider a portfolio of 1,000,000 consisting of
  two of your favorite stocks. For example, lets
  assume that today you invest 400,000 in Intel
  (INTC) and 600,000 in Yahoo (YHOO).
• What is the risk as measured by daily VaR (at 95
  confidence level) associated with such a
  portfolio?
• To simulate the value of your portfolio tomorrow,
  you need to estimate the distribution of daily
  returns for each of these stocks. This can be
  done using the historical return data obtained,
  for example, from Yahoo Finance.

81

• Yahoo Finance provides downloadable historical
  prices

Once the prices are displayed, select Download
to spreadsheet
82

• For example, here is the spreadsheet containing
  daily price information for Intel stock for a
  3-month period ending on June 9, 2005

83

• We edit this file to retain only the date and
  the adjusted close columns and to compute the
  daily returns

Note that we lose one row of data we dont
have the daily return for row 65.
84

• We did the same for Yahoo stock and merged both
  data sets into a single Excel spreadsheet (which
  we called Model in the file intc-yhoo-0.xls)

85

• Steps
• Create 2 assumption cells for tomorrows returns
  in G2G3.
• Fit distributions to the historical data based on
  the Chi-square test, and add correlation between
  them.
• Calculate the daily profit/loss from the
  portfolio as a function of these two random
  variables.
• Simulate 10,000 random days, and estimate the 5
  VaR.

86

• In the dialog window, enter the range of historic
  returns for the INTC stock on your spreadsheet
  (in our case, C2C64) and click OK

87

• CB presents you with the best fit distribution to
  the historical returns data. In the case of INTC
  stock, CB chose a Minimum Extreme Distribution.
• Click Accept and then OK CB now treats cell
  G2 as a random variable distributed as described
  above.
• Repeat the same procedure for the Yahoo stock
  returns CB chooses Maximum Extreme distribution
  to best describe the data.

88
(No Transcript)
89

• Now we can create a cell for the portfolios PL
  tomorrow (G5).
• G5 will be our forecast cell.

90

• After a simulation run with n10,000 trials, the
  5 level Value at Risk for our portfolio is
  18,731.
• With the current portfolio composition, we will
  have a daily loss of 18,731 or more with
  probability 5.