Posted: February 28th, 2023

# Finance- Plagiarism free pls

refer to excel for data analysis

PART A

Investment Portfolio Outline

You are a portfolio manager in an investment company (can be a mutual fund, or a pension fund, hedge fund, private equity firm, or any you may imagine). You have \$1 million to invest for your fund.

Goal of the project: To Learn and Apply the basic tools and skills In Investment Analysis

Assets Available for investment: Stocks

Portfolio Goal: (Choose one), Capital Appreciation, Capital Preservation, Income, or any objective you may define

Portfolio Selection Strategies: Risk and Reward Specification

Data recording and analysis:

Download one year weekly stock price data of your stocks

Calculate weekly portfolio returns and standard deviation

Performance Calculation and Analysis: calculate Sharpe ratio for your portfolio

Compare the portfolio Sharpe ratio with that of the market portfolio (Use the S&P500 portfolio as a proxy for the market portfolio)

Prepare your report in MS Word: You will prepare a report that includes an Introduction of your portfolio, description of the stocks in your portfolio, any major news that moves your stocks, explanation of any theory you applied, the data sources, weekly and overall results, and your summary conclusion. Include everything, Excel calculation, in a word document.

Your report will be assessed by the following Assessment Matrix:

Case introduction, explanation, and Portfolio setup: 30%

Weekly Data Recoding and Analysis: 30%

Application of investment theory: 30%

Summary of the Case: 10%
Total 100%

Make sure your report includes each element mentioned in the matrix above for the grading!

Note: Please refer to the example below for your project data collection.

To help you understand
how to collect data to perform your portfolio analysis, I am providing the following example for your reference.

This example has two parts. Part 1 is about data collection and part 2 is about portfolio performance calculation.

Part 1: data collection
The data we need:

1) Weekly data for your stocks

2) Weekly data for S&P 500

3) T-bill rate

1) Weekly data for your stocks

What stocks to choose for your portfolio? You may use Yahoo Finance, stock “
Screeners” for selecting the stocks.

Click “Screener”, I have the following page

You may choose your own criteria to choose your stocks. I use predefined criteria “Growth Technology Stocks.” I get the following page

For simplicity of explanation, I choose only two stocks in the list (
You may choose more stocks in your portfolio): Apple and AMD.

Click AAPL, I then get the following page. I then click Historical Data

I get the following historical data page. I then click Weekly for frequency and I have the following one year weekly price for Apple stock.
Please note to use Adjusted Close price for your data. The adjusted close include the dividend information.

Click Download button and you will get the data from an Excel file

Please repeat the above steps for the other stocks you selected to get the weekly historical price

2) Collecting Weekly data for S&P 500

2) Collecting Weekly data for S&P 500

I then click the S&P500 index to get the historical data for S&P500 index as a market portfolio

I have the following weekly S&P500 data.

Please note that sometime, Yahoo finance doesn’t show download button. You may just highlight the data and then copy and paste the data in Excel.

The data in the table above is arranged in reversed chronical order. Please sort the data by date to get the data in chronical order as follows:

3) T-bill rate

To find T-bill rate for risk free rate proxy, I google “T-Bill rate today”

I see two numbers, 0.09% and 0.17%. I will take the average of the two numbers to get my proxy for risk free rate.

Part II Portfolio performance Computation

I now put all the data in an Excel file. You may open the Excel file to check the formula for the calculation of average returns, standard deviation, Sharpe ratios, CAPM calculation, and alpha (or abnormal) returns.

Please note I divide the fund among the investments equally. But you may distribute different percentage of your fund to your stocks. I bought 6498 shares of Apple (\$500000/2=6498.8974) and 9400 shares (\$500000/2=9400.264) of AMD.

To prepare your report, you may visit Slideshare.net

Type “Investment Analysis Project” in the search bar

The search will result in a list of investment analysis project report. Click the reports to get some idea how to write your report. Some of those reports are very long, but you don’t have to follow their length. Remember there is no requirement on the number of pages. Just make sure your explanation is clear and to the point. Finally, prepare your report in a Word document.

You may always send me an email for any questions at caif@wpunj.edu.

PART B

Managing Bond Portfolios

[endnoteRef:1]. A 9-year bond has a yield of 10% and a duration of 7.194 years. If the market yield changes by 50 basis points, what is the percentage change in the bond’s price? [1: ]

[endnoteRef:2]. Find the duration of a 6% coupon bond making annual coupon payments if it has 3 years until maturity and has a yield to maturity of 6%. What is the duration if the yield to maturity is 10%? [2: ]

[endnoteRef:3]. You predict that interest rates are about to fall. Which bond will give you the highest capital gain? [3: ]

a. Low coupon, long maturity.

b. High coupon, short maturity.

c. High coupon, long maturity.

d. Zero coupon, long maturity.

[endnoteRef:4]. You will be paying \$10,000 a year in tuition expenses at the end of the next 2 years. Bonds currently yield 8%. [4: ]

a. What is the present value and duration of your obligation?

b. What maturity zero-coupon bond would immunize your obligation?

c. Suppose you buy a zero-coupon bond with value and duration equal to your obligation. Now suppose that rates immediately increase to 9%. What happens to your net position, that is, to the difference between the value of the bond and that of your tuition obligation? What if rates fall to 7%?

[endnoteRef:5]. Pension funds pay lifetime annuities to recipients. If a firm will remain in business indefinitely, the pension obligation will resemble a perpetuity. Suppose, therefore, that you are managing a pension fund with obligations to make perpetual payments of \$2 million per year to beneficiaries. The yield to maturity on all bonds is 16%. [5: ]

a. If the duration of 5-year maturity bonds with coupon rates of 12% (paid annually) is 4 years and the duration of 20-year maturity bonds with coupon rates of 6% (paid annually) is 11 years, how much of each of these coupon bonds (in market value) will you want to hold to both fully fund and immunize your obligation?

b. What will be the par value of your holdings in the 20-year coupon bond?

[endnoteRef:6]. A 12.75-year maturity zero-coupon bond selling at a yield to maturity of 8% (effective annual yield) has convexity of 150.3 and modified duration of 11.81 years. A 30-year maturity 6% coupon bond making annual coupon payments also selling at a yield to maturity of 8% has nearly identical duration—11.79 years—but considerably higher convexity of 231.2. [6:

PART C: Explain the underlisted.
Calculate Bond Price, Current Yield, YTM, Realized Yield, Yield to Call -MO5.1
Explain convertible bonds -MO5.2
Discuss TIPS (Treasury Inflation Protected Security) -MO5.3
Calculate duration -MO5.4
Explain bond portfolio immunization -MO5.5
]

Use a financial calculator or Excel to answer questions a and b:
a. What is the price of the zero-coupon bond (\$1,000 face value)?
b. What is the price of the coupon bond?
Now suppose the yield to maturity on both bonds increases to 9%. Please answer questions c and d:
c. What will be the actual percentage capital loss on each bond?

d. What percentage capital loss on each bond would be predicted by the duration-with-convexity rule?

## Sheet1

L

P
Q(# of Shares)
P

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

1.50%

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

5%

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

.970551

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

1.25%

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

1.23%

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

1.39%

6498.8974555478

9400.2633840997

-0.77%

6498.8974555478

9400.2633840997

6498.8974555478
123
9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997
82.760002

6498.8974555478

9400.2633840997

6498.8974555478

9400.2633840997

0.96%

0.71%

Annualized Mean=

Annualized st dev=

50%
50%
1.2

 Date AA P AMD Portfolio Value Return S&P500 S&P Return Q(# of Shares) 5/4/20 6498.8974555478 76.936127 9400.2633840997 53.189999 1000000.00 2,929.80 5/11/20 76.542412 54.200001 1006935.57 0.69% 2,863.70 -2.26% 5/18/20 79.323433 55.169998 1034127.37 2.70% 2,955.45 3.20% 5/25/20 79.08712 53.799999 1019713.24 – 1.39% 3,044.31 3.01% 6/1/20 82.460152 53.099998 1035054.04 1. 50% 3,193.93 4.91% 6/8/20 84.276009 53.5 1050615.23 3,041.31 -4.78% 6/15/20 86.992348 54.23 1075130.63 2.33% 3,097.74 1.86% 6/22/20 87.964951 50.099998 1042628.37 -3.02% 3,009.05 -2.86% 6/29/20 90.571838 52.34 1080626.87 3.64% 3,130.01 4.02% 7/6/20 95.439842 55.880001 1145540.47 6.01% 3,185.04 1.76% 7/13/20 95.845299 55.040001 1140279.28 -0.46% 3,224.73 1.2 7/20/20 92.151382 69.400002 1251260.68 9.73% 3,215.63 -0.28% 7/27/20 105.728088 77.43 1414978.40 13.08% 3,271.12 1.73% 8/3/20 110.556305 84.849998 1516106.42 7.15% 3,351.28 2.45% 8/10/20 114.538445 81.300003 1508615.05 -0.49% 3,372.85 0.64% 8/17/20 123 83.809998 1593507.95 5.63% 3,397.16 0.72% 8/24/20 124.406647 85.550003 1612698.60 1.20% 3,508.01 3.26% 8/31/20 120.571503 82.010002 1554497.45 -3.61% 3,426.96 -2.31% 9/7/20 111.640282 76.339996 1443154.81 -7.16% 3,340.97 -2.51% 9/14/20 106.496849 74.93 1396473.84 -3.23% 3,319.47 -0.64% 9/21/20 111.919373 78.059998 1461137.07 4.63% 3,298.46 -0.63% 9/28/20 112.657005 81.800003 1501087.90 2.73% 3,348.42 1.51% 10/5/20 116.594315 83.099998 1538896.37 2.52% 3,477.14 3.84% 10/12/20 118.637726 83.169998 1552834.30 0.91% 3,483.81 0.19% 10/19/20 114.670517 81.959999 1515677.51 -2.39% 3,465.39 -0.53% 10/26/20 108.510361 75.290001 1412943.55 -6.78% 3,269.96 -5.64% 11/2/20 118.3088 85.879997 1576171.35 11.55% 3,509.44 7.32% 11/9/20 119.082054 81.43 1539365.51 -2.34% 3,585.15 2.16% 11/16/20 117.164917 84.639999 1557081.06 1.15% 3,557.54 -0.77% 11/23/20 116.416031 87.190002 1576184.83 1.23% 3,638.35 2.27% 11/30/20 122.067596 94.040001 1677305.57 6.42% 3,699.12 1.67% 12/7/20 122.227356 91.650002 1655877.21 -1.28% 3,663.46 – 0.96% 12/14/20 126.471016 95.919998 1723595.41 4.09% 3,709.41 12/21/20 131.773087 91.809998 1719417.94 -0.24% 3,703.06 -0.17% 12/28/20 132.49202 91.709999 1723150.20 0.22% 3,756.07 1.43% 1/4/21 131.852966 94.580002 1745975.83 1.32% 3,824.68 1.83% 1/11/21 126.950294 88.209999 1654234.17 -5.25% 3,768.25 -1.48% 1/18/21 138.862503 92.790001 1774703.62 7.28% 3,841.47 1.94% 1/25/21 131.763107 85.639999 1661353.47 -6.39% 3,714.24 -3.31% 2/1/21 136.555939 87.900002 1713746.21 3.15% 3,886.83 4.65% 2/8/21 135.369995 93.769997 1761218.39 2.77% 3,934.83 2/15/21 129.869995 89.580002 1686087.39 -4.27% 3,906.71 – 0.71% 2/22/21 121.260002 84.510002 1582472.60 -6.15% 3,811.15 -2.45% 3/1/21 121.419998 78.519997 1527204.77 -3.49% 3,841.94 0.81% 3/8/21 121.029999 81.050003 1548452.93 3,943.34 2.64% 3/15/21 119.989998 79.059998 1522987.50 -1.64% 3,913.10 3/22/21 121.209999 77.410004 1515405.78 -0.50% 3,974.54 1.57% 3/29/21 81.089996 1561631.71 3.05% 4,019.87 1.14% 4/5/21 133 82.760002 1642319.18 5.17% 4,128.80 2.71% 4/12/21 134.160004 82.150002 1644123.76 0.11% 4,185.47 1.37% 4/19/21 134.320007 1650897.77 0.41% 4,180.17 -0.13% 4/26/21 131.460007 81.620003 1621594.63 -1.77% 4,181.17 0.02% 5/3/21 128.100006 77.830002 1564131.32 -3.54% 4,167.59 -0.32% average weekly return= average weekly rm= weekly standard deviation= 4.45% weekly mkt st dev 2.44% 2.85% Annualized Mean= 49.88% 36.87% Annualized st dev= 32.10% 17.56% T-bill rate as a proxy for risk free rate= 0.130% (refer to the example in the project outline) Sharp Ratio of your portfolio=(r-rf)/St. Dev= 1.5497460566 Sharpe ratio for the mkt=(r-rf)/St. Dev= 2.0915693081 CAPM r=rf+b(rm-rf)= 59.46% alpha=actual r -required r= -0.0958427847 In this example, I invested 50% (w1) in stock 1 and 50% (w2) in stock 2. I also go to Yahoo finance to find out the beta for stock 1, beta(AAPL), and the beta for stock 2, beta(AMD). weight for Apple= weight for AMD BetaApple BetaAMD portfolio beta 2.03 w(aaple)*Beta(Apple)+w(AMD)*Beta(AMD) portfolio beta= 1.615 For writing the report Visit slideshare.net

### Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

## Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
\$0.00