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.

0% Plagiarism Guaranteed & Custom Written

BA7032 FINANCIAL MODELLING AND RESEARCH METHODS

PART ONE: FINANCIAL MODELLING

COURSEWORK ASSIGNMENT: EXCEL APPLICATIONS IN CORPORATE FINANCE

Module weighting: 50%

Issue Date: October 2021

Submission deadline: 13:00 10th January 2022.

Prepared by Dr Natalia Isachenkova (Room 3011 in Kingston Business School) 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. (20%) 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.

Calculate and graph the average annual returns and standard deviations of all portfolios that are combinations of EURONEXT 100 (^N100) index and KBW Nasdaq Bank Index (^BKX), with the proportion of EURONEXT 100 (^N100) index being 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100%. Comment on the profile of the portfolios.

(35 %) Page 2 of 4 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 (^NDX) index and end-of-year prices of ANY TWO of the member stocks of the NASDAQ 100 (^NDX) index. Collect also the latest prices for those two the NASDAQ 100 (^NDX) index stocks. 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. (35%) 4) Suppose today is the 25th of August 2021. The January 20 th, 2023, call option on Amazon.com, Inc. (AMZN) common stock is priced at $426.80. The exercise price of the call option is $3,300. The expiry date of the option is January 20 th , 2023. The 25th of August 2021 price of Amazon.com, Inc. common stock is $3,299.18. The risk-free rate is 1.70%. You are required to derive the implied volatility  (sigma) of the underlying asset from the current market price of the January 20 th, 2023 call option on Amazon.com, Inc. common stock. Assume an 18% starting value for  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. (10%) Key skills This assignment will help you develop the following key skills:

  • Communication
  • Numeracy
  • Proficiency in Information Communication Technology (ICT)
  • Abstract thought and analytical skills necessary for analysis and interpretation of financial and business information. Learning outcomes for BA7032 PART ONE Coursework Assignment The assignment will assess your ability to:
  • discuss and analyse data on financial assets
  • use Excel to prepare and transform financial data for modelling
  • set up in Excel pricing models of financial assets, using relevant formal valuation theories
  • set up in Excel portfolio analysis and analysis of systematic risk
  • perform correlation and regression analyses in Excel
  • use Excel to perform sensitivity analysis
  • understand the effects of inputs on valuation and risk of financial assets

interpret the output of Excel financial and statistical analyses Page 3 of 4 Requirements for BA7032 PART ONE Coursework Assignment

Your submission must include the elements specified in the following assessment criteria table: Required element for each of the four problems Available Mark, % 1 Input the data you obtain via Bloomberg / or Datastream / or Thomson ONE Banker / or Yahoo!Finance into spreadsheet to create a source data worksheet. If you download timeseries make sure you align the dates correctly. Add a brief comment stating the exact data sources. Add a brief comment on the characteristics of your data. Use separate data section and calculation section. For each problem place your calculations/models/graphs in easy-to-read layout on a separate worksheet. When uploading your work, ALL your worksheets for all the four problems must be combined together in ONE workbook. Add brief comments that inform the user of the exact financial models, which you utilise in modelling, and about your important results. These comments should appear on the screen. 25 2 Your spreadsheet model should give the user all the details of your methodological procedure, presenting it step by step. Charts must accurately represent the data and be labelled correctly. Appropriate statistical techniques must be used. An indication of the reliability of the empirical estimates must be provided. A good spreadsheet implementation of a financial problem will make use of relevant corporate finance theories. Your spreadsheet model will not only find the solution to the problem but would also be useful and reliable as an analytical tool capable of providing a series of answers when input assumptions change. 40 3 There should be no hard coding of data within your Excel workbook. 15 4 Overall quality of the documentation supplied. Each Excel worksheet should have a footer that will include:

a title that explains the worksheet’s purpose;

your name and KU ID number;

a date for the file. The accompanying report on this Coursework Assignment, in Word (or PDF) format, must be presented in a single document no longer than THREE single-spaced A4 pages using 12- point Times New Roman (excluding references).

A good report must be factually accurate and analytical. It will briefly summarise the issues addressed in your coursework and, where relevant, relate your empirical findings to theory. You will need briefly state your modelling assumptions and critically interpret the modelling output, using evidence in the argument. The report should state, in bullet points, major strengths of the spreadsheet design features adopted in your work, in terms of their usefulness for financial decision making and further analysis. References should be listed in alphabetical order at the end of the report and cited in the text of the report using the Harvard style of referencing. You must always give credit to the authorship of the work that you use. Your Word (or PDF) file should include professional presentation and correct spelling and grammar. Appropriately name your uploaded Excel and Word (PDF) files. Please name your files using your last name and the module code, for example HollandBA7032.xls. This makes it easier to identify each individual’s work. 20 Total 100% You MUST submit the required Word (or PDF) file with the report and the Excel file with a combined workbook with separate worksheets for each problem through CANVAS, by uploading the files onto their designated BA7032 Coursework Assignment Dropbox by the deadline. Please make sure you keep backup files of your work. The Excel and Word (PDF) files uploaded must be named as follows: Page 4 of 4

Name your file using your last name and the module code, for example HollandBA7032.xls Your submission must be presented as follows:

For the Word (PDF) document - Name and ID in the header; page number in the footer using the following page layout: o Times New Roman font size 12, single spaced, with margins 1” (or 2.5 cm) 1 inch margins (left, right, top and bottom)

For each Excel document – you must include the headers and footers as described in Assessment Criterion 7 above. Your Excel workbook should use a consistent font of no less than size 10.

You must complete and attach the specific BA7032 Coursework Assignment front cover sheet. 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 Policies and regulations - How the University works - Kingston University London. Indicative Reading

For advice on design approaches that can assist with production of useful to financial managers Excel applications you might wish to consult Day’s Mastering Financial Modelling in Microsoft Excel and the latest edition of Benninga’s Principles of Corporate Finance with Microsoft Excel.

For working knowledge of the concepts and theories of corporate finance involved with this assignment you will need to read the relevant chapters of the latest edition of Corporate Finance by Berk and DeMarzo, the core text supporting BA7031 Corporate Finance and Financial Statement Analysis.

Basic statistics concepts and Excel tools for basic statistical analyses are covered in Chapter 16 of Day’s Mastering Financial Mathematics in Microsoft Excel and in Chapter 9 of Benninga’s Principles of Corporate Finance with Microsoft Excel. END OF ASSIGNMENT PAPER


100% Plagiarism Free & Custom Written,
Tailored to your instructions
paypal checkout

Our Giveaways

Plagiarism Report

for £20 Free

Formatting

for £12 Free

Title page

for £10 Free

Bibliography

for £18 Free

Outline

for £9 Free

Limitless Amendments

for £14 Free

Get all these features for
£83.00 FREE

ORDER NOW
Still Not Convinced?

View our samples written by our professional writers to let you comprehend how your work is going to look like. We have categorised this into 3 categories with a few different subject domains

View Our Samples

Recent Uploads

FLAT 50% OFF ON EVERY ORDER.Use "FLAT50" as your promo code during checkout