Excel代写-MSIN0107
时间:2021-03-01
Assessment Information Coursework 2020-21
Module name: MSIN0107
Module code: Advanced Quantitative Finance
Module leader name: Ming Yang
Academic year: INSERT
Term 1, 2 or 3: INSERT
Type of assessment: Coursework assignment
Nature of assessment – group:
Content of this Assessment Brief
Section Content
A Core information
B Coursework Brief and Requirements
C Module learning outcomes covered in this
assessment
D Assessment criteria
E Groupwork instructions (if applicable)
F Additional information from module leader (if
applicable)

Page 2 of 9

Section A: Core information
This assessment is
marked out of:
100
% weighting of this
assessment within total
module mark
10%
Word count/number of
pages - maximum
1,000 words
There is no penalty for using fewer words pages.
Determining word count
impacted by Turnitin
• After submission to Turnitin, the Turnitin recorded word
count is usually higher than the word count in a Word
document.
• Where the assessment brief specifies a maximum word
count, on the front cover of your submission record the
number of words as recorded in your Word document.
• It is the Word document word count which will be taken
account of in marking, NOT the Turnitin word count.
Footnotes, appendices,
tables, figures, diagrams,
charts included
in/excluded from word
count/page length?
Any footnotes, appendices are not included in page limit.

Bibliographies, reference
lists included
in/excluded from word
count?
Title page, table of contents, any bibliography are excluded from the
page limit.
Penalty for exceeding
specified word
count/page length?
• Where there is a specified word count/page length and this is
exceeded, yes there is a penalty: 10 percentage points
deduction, capped at 40% for Levels 4,5, 6, and 50% for Level
7. Refer to Academic Manual Section 3: Module Assessment -
3.13 Word Counts.
• Where there is no specified word count/page length no
penalty applies.
Requirements for/use of
references
• Where you draw upon sources you must cite them
appropriately.
• As appropriate, you may draw upon a range of sources
including, illustratively, journal articles, other textbooks,
industry reports.
• As appropriate, you may draw upon course materials –
lecture slides, notes, handouts, readings, textbook(s) - you
engaged with in your studying of this module.
• Unless citing content specifically (e.g. a quote from a book)
you should not be copying word for word from lecture slides,
notes, handouts, readings, textbook(s).
• You should capture, articulate and communicate your views,
thoughts and learning in your own words.
• If you do provide quotes from any lecture slides, notes,
handouts, readings, textbook(s) you should cite them and
provide references in the usual way.
Page 3 of 9

• Be aware that a number of academic misconduct checks,
including the use of Turnitin, are available to your module
leader.
• If required/where appropriate UCL Academic Misconduct
penalties may be applied (see immediately below).
Academic misconduct
(including plagiarism)
• Academic integrity is paramount.
• It is expected that your submission and content will be your
own work with no academic misconduct.
• Academic Misconduct is defined as any action or attempted
action, including collusion with other students, that may
result in a student obtaining an unfair academic
advantage. There are severe penalties for Academic
Misconduct, including, where appropriate and required,
exclusion from UCL.
• Refer to Academic Manual Section 9: Student Academic
Misconduct Procedure - 9.2 Definitions.
Submission date 4th March 2021
Submission time 10am UK time
Penalty for late
submission?
Yes. Standard UCL penalties apply. Students should refer to
https://www.ucl.ac.uk/academic-manual/chapters/chapter-4-
assessment-framework-taught-programmes/section-3-module-
assessment#3.12
Submitting your
assignment
The assignment MUST be submitted to the module submission link
located within this module’s Moodle ‘Submissions’ tab by the
specified deadline.
Anonymity of identity.
Normally, all submissions
are anonymous unless
the nature of the
submission is such that
anonymity is not
appropriate, illustratively
as in presentations or
where minutes of group
meetings are required as
part of a group work
submission
Anonymity is required.
Your name should NOT appear anywhere on your submission.

Return and status of
marked assignments
• At the latest this will be within 4 weeks from the date
of submission as per UCL guidelines, but we will
endeavour to return it earlier than this.
• Assessments are subject to appropriate double
marking/scrutiny, and internal quality inspection by
a nominated School of Management internal
assessor. All results when first published are
provisional until confirmed by the relevant External
Examiner and the Examination Board.
Page 4 of 9

• No appeals regarding your published mark are
available until after confirmation by that
Examination Board. UCL regulations specify that
academic judgment applied within the marking
process cannot be challenged.

Uploading your submission
• Unless specifically instructed otherwise in the assessment document, please upload your work as a
single file via the submission link on Moodle.
o Wherever possible you should type/use Excel (as appropriate) for your answers and follow
instructions later in this assessment document.
o If you do have to include any elements that are not typed/computer generated (e.g. figures,
diagrams, equations etc.) as instructed by your module leader, please follow the advice for
submitting handwritten answers for any submission that requires scanning documents. (The
webpage refers to 24-hour timed exams but is applicable to this assessment).
o If you are unable to type your answers for medical reasons, please contact Student Support
and Wellbeing to arrange a SORA and notify your programme administrator.
o If for any reason you are not able to use the app recommended by ISD at the link above, you
can consult the following resources for advice about preparing your submission:
 Submitting handwritten assignments to Moodle using mobile or tablet Devices -
Device Camera
 Submitting handwritten assignments to Moodle using mobile or tablet devices - MS
One Drive App
• Please DOUBLE CHECK that the file you are uploading is the correct one and is complete (with all
pages visible). You should only submit your final version of the work to the submission box. Once the
original deadline has passed, any submission made to the official submission box will be taken as
the final submission. Work submitted after the original submission deadline will NOT be removed
and a new version cannot be submitted. If you wish to check your work before submitting your final
version, you can use the Turnitin Drafts Checker rather than your official submission inbox. Please be
sure that the version of the assignment you submit is the version you intend to be marked for that
module. If you are granted ECs in relation to a piece of work already submitted, the mitigation
applied will be a deferral and not an extension. If you are unable to submit on time, you may submit
an EC form to request an extension if you have legitimate mitigating circumstances.

Page 5 of 9

Technical Problems
If you encounter difficulties submitting your assessment via Moodle, then please immediately notify (by
email) your department (Programme Administrators ONLY), explaining the problem and including a copy of
the work you are trying to submit. ONLY use this approach if you can show that you have tried to download
from/upload to Moodle and encountered technical difficulties.
Advice and other support
• Student Support and Wellbeing
Page 6 of 9

Section B: Coursework Brief and Requirements
See document at the end of the brief.
Page 7 of 9

Section C: Module Learning Outcomes covered in this
Assessment

This assignment contributes towards the achievement of the module Learning
Outcomes as specified in the module description.


Page 8 of 9

Section D: Assessment criteria
Within each section of this coursework you may be assessed on the following aspects, as
applicable and appropriate to this particular assessment, and should thus consider these aspects
when fulfilling the requirements of each section:
• The accuracy of any calculations;
• The strengths and quality of your overall analysis and evaluation;
• Appropriate use of relevant theoretical models, concepts and frameworks;
• The rationale and evidence that you provide in support of your arguments;
• The credibility and viability of the evidenced conclusions/recommendations/plans of
action you put forward;
• Structure and coherence of your considerations and reports;
• As and where required, relevant and appropriate, any references should use either
the Harvard OR Vancouver referencing system (see References, Citations and
Avoiding Plagiarism)
• Academic judgement regarding the blend of scope, thrust and communication of
ideas, contentions, evidence, knowledge, arguments, conclusions.
• Each part has requirements with allocated marks, maximum word count limits/page
limits and where applicable, templates that are required to be used.

You are advised to refer to the UCL Assessment Criteria Guidelines, located at
https://www.ucl.ac.uk/teaching-learning/sites/teaching-learning/files/migrated-
files/UCL_Assessment_Criteria_Guide.pdf

Page 9 of 9

Section E: Groupwork Instructions
This is a team assignment. Each team should submit a pdf version report in Moodle. The
report should contain your answers/arguments and will be graded. Please also prepare
Powerpoint slides that summarize your findings. You don't have to submit the Powerpoint
slides in Moodle. In class, I may randomly select one or two teams to present their
Powerpoint slides.

This assignment is due at 10am on 4th March, 2021. See the assignment instructions here
and data here.

You'll need to use the Black-Scholes formula to calculate option prices and implied volatility.
To make your life easy, here I provide two useful Excel add-ins: optall3.xla and
optbasic3.xla. Please put the add-ins to "C:\Program Files\Microsoft Office\Office16\Library"
and then install the two add-ins. To install the add-ins, open any Excel spreadsheet --> file -
-> options --> add-ins --> choose "Excel add-ins" in the bottom box and click "go" -->
browse and choose the two add-ins at the path where you put them --> click "ok".

These add-ins are provided in a CD published with the "McDonald" textbook (Derivatives
Markets, Third Edition, Robert L. McDonald, Addison Wesley, Copyright 2013) and are thus
intended for educational, non-commercial use. Neither the author nor publisher represent
these spreadsheets as suitable for any purpose other than to help you learn the material in
the text.

Section F: Additional information from module leader(s)
N/A
MSIN0107: Advanced Quantitative Finance
UCL School of Management
Prof. Yang
m-yang@ucl.ac.uk
Group Course Work 2
I Introduction and Instructions
Please read the HBS case 9-201-071 Pine Street Capital carefully, and answer the
following questions.
A Black-Scholes Functions
You’ll need to use the Black-Scholes formula to calculate option prices and implied
volatility. To make your life easy, here I provide two useful Excel add-ins:1 op-
tall3.xla and optbasic3.xla. Please put the add-ins to "C:nProgram FilesnMicrosoft
O¢ cenO¢ ce16nLibrary" or "C:nProgram Files (x86)nMicrosoft O¢ cenrootnO¢ ce16nLibrary"
and then install the two add-ins. To install the add-ins, open any Excel spreadsheet
–> …le –> options –> add-ins –> choose "Excel add-ins" in the bottom box and click
"go" –> browse and choose the two add-ins at the path where you put them –> click
"ok".
Once the add-ins are installed, you should be able to use the Excel functions in the
table below. (You may just need to use a few of them, e.g., BSCallImpVol, BSPut,
BSPutDelta, etc. You may want to play with others so I include all the functions here
FYI.) The following symbol de…nitions are used in these functions: “s”= stock price,
“k”= strike price, “v”= volatility (annualized), “r”= interest rate (continuously-
compounded, annualized), “t”= time to expiration (years), and “d”= dividend yield
(continuously-compounded, annualized).
1These add-ins are provided in a CD published with the "McDonald" textbook (Derivatives Mar-
kets, Third Edition, Robert L. McDonald, Addison Wesley, Copyright 2013) and are thus intended
for educational, non-commercial use. Neither the author nor publisher represent these spreadsheets
as suitable for any purpose other than to help you learn the material in the text.
1
Function De…nition Function Description
BSCall(s, k, v, r, t, d) European call option price
BSPut(s, k, v, r, t, d) European put option price
BSCallDelta(s, k, v, r, t, d) European call delta
BSPutDelta(s, k, v, r, t, d) European put delta
BSCallGamma(s, k, v, r, t, d) European call gamma
BSPutGamma(s, k, v, r, t, d) European put gamma
BSCallVega(s, k, v, r, t, d) European call vega
BSPutVega(s, k, v, r, t, d) European put vega
BSCallRho(s, k, v, r, t, d) European call rho
BSPutRho(s, k, v, r, t, d) European put rho
BSCallTheta(s, k, v, r, t, d) European call theta
BSPutTheta(s, k, v, r, t, d) European put theta
BSCallPsi(s, k, v, r, t, d) European call psi
BSPutPsi(s, k, v, r, t, d) European put psi
BSCallElast(s, k, v, r, t, d) European call elasticity
BSPutElast(s, k, v, r, t, d) European put elasticity
BSCallImpVol(s, k, v, r, t, d, c) Implied volatility for European call2
BSPutImpVol(s, k, v, r, t, d, c) Implied volatility for European put3
BSCallImpS(s, k, v, r, t, d, c) Implied stock price for a given European call option price
BSPutImpS(s, k, v, r, t, d, c) Implied stock price for a given European put option price
II Case Summary
Pine Street Capital (PSC) is a technology hedge fund located in San Francisco, Cali-
fornia. PSC manages a fund that invests primarily in semiconductor companies that
produce integrated circuits for broadband communications including wide and local
area networks, optical components, and storage area networks. It is currently July,
2000 in the case, and the NASDAQ market (where most of the stocks that PSC invests
in are traded) index reached a peak of over 5,000 a few months ago but has dropped
2The option price is entered as “c”; the volatility entered does not matter.
3The option price is entered as “c”; the volatility entered does not matter.
2
considerably since then. In addition, the NASDAQ has experienced a historically un-
precedented amount of volatility over the last several months, as shown in Exhibit 7
of the case. Due to the market drop and volatility, PSC’s fund has experienced con-
siderable problems in the last few months. Even though the fund has had a policy
of hedging market risk, the fund has nevertheless experienced large losses on many
days (sometimes on several consecutive days) in the last few months. This has created
problems for the fund because of the leverage employed by the fund. Some of the
losses that the fund had experienced had been large enough that the fund had come
dangerously close to having its prime broker, a prominent Wall Street …rm, liquidate
the fund. Harold Yoon, a partner in the fund, now must evaluate the fund’s market
hedging program over the last few months to determine why the fund had experienced
such large losses on many days.
III Derivatives and Leverage
Consider the following binomial tree of the price movement of a non-dividend-paying
stock, assume the time period is one year (i.e., h = 1), and the e¤ective one-year
interest rate is 20% (i.e., er1 = 1 + 20%).
% Su = 140
S0 = 100
& Sd = 90
1. [2 points] Please calculate u and d, the gross returns on the stock for both states
of the world.
2. [5 points] Consider an at-the-money European call option with maturity of 1
year. Compute the and B of the call option. What is the gross return on a
long position in the call option (You need calculate the return on the call option
at both nodes, u and d)?
3. [3 points] Is the call option riskier than the stock? Why? Sometimes we say a
call option is like a leveraged position on the underlying stock, comment on this.
3
4. [3 points] Consider a futures contract on one share of the stock. What is the
no-arbitrage futures price (i.e., the forward price), F0;T?
5. [5 points] Assume an initial margin requirement of 8:33% of the futures price,
F0;T . What is the return on a long position in the futures contract? (Again, the
realized return will depend on the state of the world. Also, note that balances on
the margin account will earn risk-free return.) What is the expected return on
the futures contract under the risk-neutral probability? Is the futures contract
riskier than the stock?
6. [7 points] Can you invest in stocks and a risk-free bond to replicate the payo¤
of the futures contact, how? (You need to take into account that investing in
the futures contract involves an initial investment in the margins account.) Is
the futures contract in the above example a leveraged position in the underlying
stock?
IV Hedging with Put Options
1. [5 points] Use the historical data on NASDAQ 100 index in the Excel …le NAS-
DAQ Index_PSC.xlsx 4 to estimate the volatility of the NASDAQ 100 index
during the sample period.
Hint: You need to …rst get the data series for ln

St+1
St

, and estimate the stan-
dard deviation of the log return process (Recall that we assume the log return
process is i.i.d. normal). Since you are using monthly data, you need to trans-
form the monthly estimate into annual volatility. You can do so by using the
following formula:
Annual = Monthly
p
12
2. [5 points] Suppose on Sep 30, 2018, an at-the-money European call option on
the NASDAQ 100 index with maturity of 1 year is traded at $425.7661. Assume
the continuously compounded LIBOR is 3% per annum, and the continuously
4This can be downloaded from the course website.
4
compounded dividend yield is 0%. What is the implied volatility of the NASDAQ
100 index (from the Black-Scholes model)?
3. [5 points] Please …nd the data for the NASDAQ 100 index during Sep and
Oct 2017 in the spread sheet named "Oct 2017" in the Excel …le NASDAQ In-
dex_PSC.xlsx. Consider an at-the-money European put option on one NASDAQ
100 index on Sep 21, 2017. The maturity of the option is one year (the option
expires on Sep 21, 2018, i.e. the third Friday of September, 2018. Take 1 year as
252 trading days, and each trading day as 1/252 year). Calculate the theoretical
Black-Scholes prices of this put option for each day in the spreadsheet Oct 2017
and …ll in the column "BS Put Price". Please use the continuously compounded
annual interest of 3% per year. Please use the implied volatility you obtained in
part 2 when calculating the Black-Scholes prices.
4. [5 points] Suppose you invested 1 million US dollar in the NASDAQ 100 index 5 6
on Sep 21, 2017. Suppose you want to use the above put option on NASDAQ 100
index (That is, the at-the-money put option on the index with one year maturity
described in the last question) to hedge the risks of your investment. To construct
a neutral position, how many put option contracts do you need to purchase
on Sep 21, 2017? (Note that each put option contract on the index contains 100
put options on the index. Assume you can take fractions of an option contract.)
5. [5 points] Suppose you rebalance your position in the put option everyday to
5Of course, trading the NASDAQ 100 index directly can be very costly. In practice, you will
probably invest in an ETF that tracks the NASDAQ 100 index (the ticker symbol for the ETF that
tracks the NASDAQ 100 is QQQ).
6A Short Note on ETF: You can think of an exchange-traded fund as a mutual fund that trades
like a stock. Just like an index fund, an ETF represents a basket of stocks that re‡ect an index such
as the S&P 500. An ETF, however, isn’t a mutual fund; it trades just like any other company on
a stock exchange. Unlike a mutual fund that has its net-asset value (NAV) calculated at the end of
each trading day, an ETF’s price changes throughout the day, ‡uctuating with supply and demand.
It is important to remember that while ETFs attempt to replicate the return on indexes, there is no
guarantee that they will do so exactly. It is not uncommon to see a 1% or more di¤erence between
the actual index’s year-end return and that of an ETF.
By owning an ETF, you get the diversi…cation of an index fund plus the ‡exibility of a stock.
Another advantage is that the expense ratios of most ETFs are lower than that of the average mutual
fund. When buying and selling ETFs, you pay your broker the same commission that you’d pay on
any regular trade.
5
maintain a neutral position during Sep and Oct 2017. In the column "Num-
ber of Contracts", calculate the number of put option contracts you need to
maintain in your portfolio on each day in Sep and Oct 2017. What is the theo-
retical return of your hedged portfolio? (Again, please assume a continuously
compounded risk free rate of 3% per year, and a dividend yield of 0. Please use
the implied volatility you obtained in question 2 in your calculation. Assume also
all assumptions of the Black-Scholes model hold.)
V Beta and Delta
1. In CAPM model, re‡ects the covariance of the return of a stock/portfolio with
some benchmark return, for example, the market index. Once we know the of
a portfolio, we can calculate the of the portfolio with respect to the market
index. Here, is de…ned as "the increase in the value of the portfolio with
one unit increase in the market index" (An alternative way of interpreting the
concept of in this context is, if NASDAQ 100 increases by $1, by how much
PSC’s portfolio is expected to increase?). Here we derive a formula that expresses
of a portfolio (with respect to the market) as a function of its market .
Derivation: Let A0 denote the value of the portfolio at time 0, and AT denote
the value of the portfolio at time T . Then
AT = A0 R
where R is the return on the portfolio. Regressing the portfolio return on that of
the Nasdaq,
R = +RNasdaq + "
where RNasdaq is the return on the market index (in the case, it is the NASDAQ
100 index). Assuming zero dividend yield on the NASDAQ 100 index, we have
RNasdaq =
ST
S0
, where ST denote the NASDAQ 100 index at time T. Therefore we
6
have:
AT = A0

+
ST
S0
+ "

= A0 ( + ") + A0
S0
ST :
The above equation implies that if ST (The NASDAQ 100 index) increase by $1,
then AT (PSC’s portfolio) is expected to increase by A0S0 . Therefore PSC’s
is:
=
A0
S0
(1)
2. [10 points] Assume PSC’s portfolio with respect to NASDAQ 100 is 1:785.
Suppose today is July 26, 2000, PSC decides to short the NASDAQ 100 index to
hedge the market risk. What is the total (dollar) amount of short positions that
PSC should take in the NASDAQ 100 index? (You’ll need to check Exhibit 2 in
the case to …nd the value of PSC’s portfolio on July 26, 2000.)
VI PSC’Hedging Problem
1. [5 points] What risks does PSC face? How did PSC hedge these risks historically?
Should PSC continue to hedge these risks?
2. [5 points] In Exhibit 3, what is PSC’s (with respect to NASDAQ100) in the
"up" days of NASDAQ 100 (Use UP to denote PSC’s estimated using data
from "up" days of NASDAQ100)? What is PSC’s in "down" days of NASDAQ
100 (Use DOWN to denote PSC’s estimated using data from "down" days of
NASDAQ)? Are they equal?
3. [4 points] Suppose PSC decides to short the NASDAQ 100 index to hedge the
market risk. Suppose also it uses UP to calculate and chooses the hedging
strategy. Would this be a good hedge if the market goes up? What if the market
goes down? Is this a perfect hedge?
7
4. [4 points] Answer question 3 assuming PSC now uses DOWN to calculate the
number of short positions
5. [4 points] Answer question 3 assuming PSC uses the average of UP and DOWN
(that is, 1
2
(UP + DOWN)) to calculate the number of short positions.
6. [4 points] If NASDAQ 100 goes up, how would the absolute value of the of a
put option on the index change? What if NASDAQ go down?
7. [4 points] If NASDAQ 100 goes up, how would the absolute value of the of a
call option on the index change? What if NASDAQ go down?
8. [5 points] Instead of shorting the market index to hedge, you can either short call
options to hedge, or long put options to hedge, which is preferable?
9. [5 points] Suppose PSC decided to use options on NASDAQ index to hedge
market risks on July 26, 2000. Among the options listed in Exhibit 10, which
one would you choose? How much positions would you take, why?
8
















































































































































































































































































































































































































































































































学霸联盟


essay、essay代写
essay、essay代写