BA7006- Financial Modelling using Excel : Financial Modelling and Data Analytics, Assignment

Assignment Task

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:
A. Calculate the Yield to Maturity (YTM) for a $1,000 par bond with a coupon rate of 3% per annum, considering annual, semi-annual, and quarterly coupon payments. The bond has a tenure of 6 years and is currently priced at $850, with a settlement date of January 1, 2020. Discuss how the frequency of coupon payments affects the annual YTM. Additionally, determine the bond's duration and modified duration, and create a data table to show the sensitivity of these metrics to yield changes from 1% to 10% and to changes in coupon frequency. Provide an analysis of what duration and modified duration indicate for the bond, supported by an appropriate table and/or graph. (25%)

B. Choose five companies listed on the S&P 500 index, each from a different Global Industry Classification Standard (GICS) sector, with historical share price data available for the last 10 years. Download the monthly historical share price data from January 1, 2014, to December 31, 2023. Construct an equally weighted portfolio with these stocks and analyse the risk and return dynamics of the portfolio’s monthly returns. Compare the portfolio’s risk and return with those of the individual assets, and critically discuss the value of portfolio diversification using academic literature. Additionally, create multiple portfolios using combinations of these five stocks and calculate the average annual returns and standard deviations. Comment on the risk-return trade-off among the portfolios, graph the average annual return and standard deviation, and discuss the concept of an optimal portfolio using academic literature. Explain why a risk-averse investor might choose to invest on the efficient frontier. (25%)

C. Apply the Capital Asset Pricing Model (CAPM) and regression analysis to estimate the systematic risk and theoretical return for the five stocks and the equally weighted portfolio from Section B. Analyse the results, identify which stock or portfolio has the highest systematic risk, and explain what factors contribute to the systematic risk of an asset. (25%)

D. For one of the five stocks from Section B, find a call option with a one-year expiration. Use the Black-Scholes Option Pricing (BSOP) model to estimate the implied volatility based on the call option inputs and provide a discussion on the estimated implied volatility of the stock. Additionally, calculate the price of a corresponding put option and critically evaluate the usefulness of the BSOP model compared to the binomial option pricing model. (25%)