Economics Homework Help

Overview

The final project for this course is the creation of a portfolio. You are to solve all problems using the Excel spreadsheet. You will select two stocks, two bonds, an equity index (S & P 500, Russell 1000, Wilshire 5000, etc.) and a risk-free asset (Treasury bill, note, or bond) of your choice. The securities should be those of wellknown companies with investment grade bonds and high-quality, blue chip stocks. As you know, the risk-free asset is a Treasury security. The quality of the companies would make it easier for you to find the financial data/information. You will solve all the problems outlined in the Main Elements section using an Excel spreadsheet.

Main Elements

Create a portfolio for two stocks, two bonds, an equity index (S & P 500, Russell 1000, Wilshire 5000, etc.) and a risk-free asset (Treasury bill, note, or bond) of your choice. Complete the following sections in the portfolio:

1. Calculate the bond price and yield-to-maturity for the fixed-income securities.

2. Calculate the bond duration and convexity for the fixed-income securities. Briefly explain your findings from a risk/return perspective.

3. Create a two-asset risky opportunity set, risky asset, and risk-free asset portfolio. Calculate the standard deviation and expected return for the risky asset portfolio. Graph the optimal risky portfolio, the capital allocation line, and the optimal complete portfolio standard deviation and expected return. What are findings regarding the risk/return relationship?

4. Compile the monthly returns (five years) for one of the stocks and the noted index. Calculate the estimated static CAPM for the period and forecast each asset’s expected return in the upcoming month. Explain the variation between the assets

5. Access pricing simulation spreadsheets online (link in the Excel Modeling in Investments text). Create one problem using various strategies in the simulation program. What did you learn from the simulation program on how to solve a trading problem? What is the goal of creating trading simulations using the spreadsheets?

6. Construct an equally weighted portfolio using the two stocks. Calculate the standard deviation for the portfolio. Select 15 quality stocks of your choice to add to the portfolio. Increase the assets in your portfolio with the 15 stocks by adding one at a time. Select three international stocks and add them to the portfolio. What are your observations regarding portfolio risk (standard deviation) for each stock added to the portfolio? Briefly explain.

7. Select one of the stocks paying a dividend. Determine the consensus estimate for dividend growth over the next five years. Estimate the infinite or stable dividend growth for the second stage. Estimate the firm’s cost of equity capital. Construct a two-stage dividend discount model in Excel. Calculate the firm’s stock price.

8. Select a stock option of one of your stocks. Calculate and graph the option payoff and profit using the Excel spreadsheet.

9. Create option trading strategies with charts using one of the stocks and a related option. Create a spread using a combination of calls and puts of a specific stock.

10. Select one of your stocks to calculate the option price using the Binomial Option Pricing Model (single and multi-period). Calculate the price using the Black-Scholes Option Pricing Model.

Requirements: .xls file