Modern portfolio theory (mpt) | Business & Finance homework help

EXERCISE 4
Creating a hyperbola:
One of the salient features of Modern Portfolio Theory (MPT) is the phenomenon of putting two
stocks together such that the resulting portfolio has a lower standard deviation (lower risk) than
either of the component stocks. This is the magic of diversification and this exercise illustrates
the phenomenon graphically.
1) Overview: Given the pps data of two firms* from your portfolio file of last week you will use
Excel to create the hyperbola that would result from plotting the risk/return profile of a set of five
portfolios, each comprised of the two stocks as the dollar mix goes from 100% of the dollars in
stock A and 0% in stock B, to 0% in Stock A and 100% in stock B.
* Note: For Spring 2014, stock A= GE & stock B=GOOG.
2) You will begin by generating data for these five portfolios. The data will be risk and return,
where risk is the standard deviation (population) of the daily returns of the portfolio and returns is
the total annual (or 12 month) return.
3) The dollar mix of the five portfolios that you plot will be:
Portfolio 1: 100% stock A and 0% stock B
Portfolio 2: 75% stock A and 25% stock B
Portfolio 3: 50% stock A and 50% stock B
Portfolio 4: 25% stock A and 75% stock B
Portfolio 5: 0% stock A and 100% stock B

4) Start by creating 5 separate portfolios in a new sheet (or “tab”) [Rename the tab “hyperbola”]
in your portfolio file with the two firms mentioned above (stockA and stockB). Each of the five
new portfolios will have the three tiers (pps, mkt value, daily returns) and each portfolio will have
three columns (the total portfolio, stockA , stockB). There will be a total of 15 new columns.
See also the screenshot called “hyperbola snapshot” available on the Moodle webpage.
5) Populate tier1 of stock A and stock B with the original pps data found in sheet1. Do this five
times, that is, once for each of the five portfolios.
6) Calculate the “normalized number of shares” for stockA and stockB using =10000/ average
pps . Enter “number of share” data for both stocks in each of the five portfolios such that
portfolio1 has 100% of the normalized number of shares for stockA and 0% of the normalized
number of shares for stockB. Portfolio2 has a 75% and 25% mix, and so on for the other three
portfolios.
7) Using the same logic and formulas as in the main sheet, calculate the Market Values (Tier2)
and Daily Returns (Tier3) for all 5 portfolios in the hyperbola tab.
8) Calculate the Standard Deviation (of the population using =STDEVP ) of the Daily Returns of
each of the 5 portfolios. Put these in a row below tier 3.
9) Calculate the Total Return (top to bottom of Tier2) for each of the 5 portfolios using (last dayfirst day)/first day. Put these in a row below the STDEVP.
10) Build and new little block of data (see again “hyperbola snapshot”) in which you display the
std deviations and total returns of the five portfolios. For each of the cells you can simply

reference the appropriate cell above. For example, use “=B766” [I’m don’t the actual row
number] to display “.01857” as calculated in a previous cell.
11) Chart the stdevp and totalK of the 5 portfolios. Use “scatter with data points connected by
smooth lines”.
12) Optional: Modify the two axis so as to exaggerate the curve. Chart Tools>Layout >Axis.
That is, by moving the borders closer to the actual curve, the curve will become more
pronounced.

Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

We value our customers and so we ensure that what we do is 100% original..
With us you are guaranteed of quality work done by our qualified experts.Your information and everything that you do with us is kept completely confidential.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Zero-plagiarism guarantee

The Product ordered is guaranteed to be original. Orders are checked by the most advanced anti-plagiarism software in the market to assure that the Product is 100% original. The Company has a zero tolerance policy for plagiarism.

Free-revision policy

The Free Revision policy is a courtesy service that the Company provides to help ensure Customer’s total satisfaction with the completed Order. To receive free revision the Company requires that the Customer provide the request within fourteen (14) days from the first completion date and within a period of thirty (30) days for dissertations.

The Company is committed to protect the privacy of the Customer and it will never resell or share any of Customer’s personal information, including credit card data, with any third party. All the online transactions are processed through the secure and reliable online payment systems.

Fair-cooperation guarantee

By placing an order with us, you agree to the service we provide. We will endear to do all that it takes to deliver a comprehensive paper as per your requirements. We also count on your cooperation to ensure that we deliver on this mandate.

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors: