BA7032 Financial Models in Excel | Individual Assessment

Problem 1: Calculate the yield to maturity of a bond

This is an individual assessment contributing 50% of your marks for the BA7032 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. The deadline for submission is 13:00 10th of January 2022. 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 7-year $1,000 par value bond with a fixed annual coupon rate of 6.5250% and a current price of $950.00. 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 2002-2020 of both EURONEXT 100 (^N100) index and KBW Nasdaq Bank Index (^BKX). 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.
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 NASDAQ 100 Assume that the NASDAQ 100 (^NDX) represents the market portfolio.
Compute the excess returns of each of the two stocks that you have selected against that of the NASDAQ 100 (^NDX). Use the relevant 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.

General points:
i) Provide all the required information and follow the instructions (e.g. word/page count). Answer the question directly and relevantly.
ii) Write in proper sentences and paragraphs and avoid a ‘shopping list’ approach. Keep the style formal and remember to draw conclusions. Use the spelling and grammar check in Word. Poor grammar and spelling is unprofessional and unacceptable at Master’s level. As a result, make sure that you proof read your work and edit it properly.
iii) Do not copy the work of others or allow others to copy your work. Plagiarism is cheating and will be penalised. Guidance on how to avoid plagiarism and academic misconduct can be found in Academic Regulations Policies and regulations - How the University works - Kingston University London.
Feedback on the written elements of the module will be based on postgraduate grade criteria. The general assessment criteria at Masters Level (Level 7) are set out in a document titled University Grade Descriptors and available in the Academic Guidance Area of Academic
Regulations