We write, we don’t plagiarise! Every answer is different no matter how many orders we get for the same assignment. Your answer will be 100% plagiarism-free, custom written, unique and different from every other student.
I agree to receive phone calls from you at night in case of emergency
Please share your assignment brief and supporting material (if any) via email here at: firstname.lastname@example.org after completing this order process.
No Plagiarism Guarantee - 100% Custom Written
PART ONE: FINANCIAL MODELLING
COURSEWORK ASSIGNMENT: EXCEL APPLICATIONS IN CORPORATE FINANCE
This is an individual assessment contributing 50% of your marks for the module. The assignment is intended to help you develop skills of implementing financial models in Excel. The assignment provides you with an opportunity to produce spreadsheet applications that solve and analyse corporate finance problems.
You are required to set up a separate worksheet (spreadsheet model) for each of the following four problems:
1) Calculate the yield to maturity of a 10-year $1,000 par value bond with an annual coupon rate of 7.25% and a current price of $1,150. Provide the solutions for both annual and semi-annual payments of interest. Comment on the relationship between the yield to maturity and the frequency of interest payments, providing an appropriate table or graph.
2) Obtain from Bloomberg (or Datastream, or Thomson ONE Banker, or Yahoo!Finance) the beginning of month values for the period 2000-2016 of both the Philadelphia Exchange Gold and Silver Index (^XAU) and the NYSE AMEX Composite Index (^XAX). Assume that you are a risk-averse investor.
For each of the two assets represented by the indices, calculate annual returns and build a frequency distribution of annual returns. Provide graphs of the distributions.
Explore the risk-return relationship for the two assets. Plot your results on a graph with the standard deviation of annual returns of each asset on the horizontal axis and the average annual return on the vertical axis, and comment on which asset performed better.
Assume that you form a portfolio by investing equal amount of money in each asset. Determine the average and standard deviation of the portfolio’s annual returns. Provide your interpretation of the risk and return of the equally-weighted portfolio compared to those of the individual assets.
Calculate and graph the average annual returns and standard deviations of all portfolios that are combinations of the Philadelphia Exchange Gold and Silver Index and the NYSE AMEX Composite Index with the proportion of the NYSE AMEX Composite Index being 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100%.
Comment on the profile of the risk-return trade-off between the portfolios.
3) For the most recent ten-year period, collect from Bloomberg (or Datastream, or Thomson ONE Banker, or Yahoo!Finance) end-of-year values of the Nasdaq 100 Index (^NDX) and end-of-year prices of ANY TWO of the member stocks of the Nasdaq 100 Index. Collect also the latest prices for those two the Nasdaq 100 stocks.
Assume that the Nasdaq 100 Index represents the market portfolio. Compute the excess return of each of the two stocks that you have selected against that of the Nasdaq 100 Index. Use the short-term Treasury Bill rate as the risk-free rate in the capital asset pricing model (CAPM).
Using the CAPM framework and regression analysis, provide the estimates of both systematic risk and theoretical return for the selected two stocks.
Discuss the results. Explain which of the two stocks has more systematic risk.
4) Suppose today is 26 June 2017. The January 19th 2018 call on Citigroup Inc common stock is priced at $3.60. The exercise price of the call option is $65.00. The expiry date of the option is January 19th, 2018. The 26 June 2017 price of the Citigroup Inc common stock is $63.78. The risk free rate is 1.50%.
You are required to derive the implied volatility s (sigma) of the underlying asset from the current market price of the January 19th 2018 call on the Citigroup Inc common stock.
Assume a 18% starting value for s to set up a theoretical model of the price of the call option. Assume the stock does not pay a dividend before the expiry date. Comment on the results.
This assignment will help you develop the following key skills:
The assignment will assess your ability to: