Equity portfolio, bond portfolio, option portfolio

You have to create a workbook with these sheets:

1. Equity portfolio which contains some equities transactions with their critical information (purchase value, purchase date, quantity, last price and total value for the line)

2. Equity analysis: which contains a dash board for a select equity:

* You must create a sub in VBA to compute a historical volatility, average, skewness, kurtosis and to produce some charts to present this asset.

* Create a specific sheet to download data with Bloomberg excel add-in

3. Efficient Frontier from our equity assets (use a solver to find a efficient points), the program is already created you have just to prepare historical return rates to use it.

4. Option portfolio: which contains some option transactions (call and put) with their information (spot price, exercise price, maturity date, volatility, risk free rate, quantity and total value for the line):

* Create functions to compute a price and Greek letters for each line.

5. Option Analysis: which contains a report sheet with price and sensitivities analysis for a select option (for example you can create different charts to present price variations due to input parameters variation, you can also draw the payoff profile of your option …)

6. Bond Portfolio: which contains bond investment transactions with their critical information (identifier, coupon rate, nominal, maturity date, issue date, coupon frequency, unit price, quantity and total price for the line):

* Create function to compute a “Price” for each line and for the portfolio.

* Create function to compute a “Duration” for each line and for the portfolio.

* Create function to compute a “Convexity” for each line and for the portfolio.

* Create a specific sheet to download data with Bloomberg excel add-in

7. Bond Analysis: which contains some analysis and chart to present:

* A relationship between bond price and yield to maturity for a selected bond

* A cash flow projection for a select bond

* A comparison between two bonds in the same chart.

8. Market Value structure of your portfolio and the weighting for each asset category:

* We have to present the data and the chart of your portfolio market value

* We have to present the data and the chart of your portfolio unrealized gains and losses value