For Calculating YTM use IRR, RATE, EDATE, EFFECT, XIRR etc functions of excel
Provide data tables and Graphical presenatation for interpreatation and analysis
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 of an 8-year $1,000 par value bond with a fixed annual coupon
rate of 7.5% and a current price of £840.00. Provide the solutions for annual, semi-annual and
quarterly payments of interest. Comment on the relationship between the yield to maturity and
the frequency of interest payments providing an appropriate table and/or graph. (25%)
B. Obtain from Bloomberg the end of year values for the period 2010–2021 of both FTSE100
(UKX) Index and S&P500 (SPX) Index. Assume that you are a risk-averse investor. Explore the
risk-return relationship for the two assets. 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 FTSE100 (UKX) index and
S&P500 (SPX) Index, with the proportion of FTSE100 (UKX) index ranging from 0, 10, 20, 30,
40, 50, 60, 70, 80, 90, 100%. Comment on the profile of the portfolios. (25%)
C. For the most recent ten-year period, collect from Bloomberg end-of-year values of FTSE100
(UKX) index and end-of-year prices of any two of the member stocks of the FTSE100 (UKX)
index. Assume that the FTSE100 (UKX) index represents the market portfolio. Compute the
excess returns of each of the two stocks that you have selected against that of the FTSE100
(UKX) index. 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 and why. (25%)
D. Suppose that ABC Ltd. stock is currently trading at $50. In one year’s time, ABC Ltd.’s stock
can go up in value to $60 or can go down in value to $30, both these states are equally likely. If
the stock is in the up state at the end of period 1, it can go up to $80 or go down to $40 in period
2, while if the stock is in the down state at the end of period 1, it can go up to $40 or fall to $20
in period 2. Consider the one-year risk free rate as 4%. You are required to set up the excel
worksheet for a two-period binomial optional pricing model to estimate the price of a put option
with an exercise price of $40. Comment on the results and the binomial option pricing model.
(25%)
Each assignment task should be addressed in a separate worksheet and all worksheets to be
collated in one workbook. Your Excel workbook should use a consistent font of no less than size
10.
• Where appropriate Input the data 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
source of the data
• Add a brief comment on the characteristics of your data.
• 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.
• 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 changes.
• There should be no hard coding of data within your Excel workbook expect for input data.
The Report (Word/Pdf)
• 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).
• The report should include a title page with your K ID and word count. The page number should
be in the footer. Include sections and subsections as appropriate to address the assignment task.
• A good report must be factually accurate and analytical.
• It should summarise the issues addressed in your coursework and, where relevant, relate your
empirical findings to theory.
• It will critically interpret the modelling output, using evidence in the argument.
• The report should state 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.
General points:
• Please ensure you keep backup files of your work.
• Provide all the required information and follow the instructions (e.g. word/page count).
• Answer the question directly and relevantly.
• 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.
• 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.
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 Alastair
Day’s Mastering Financial Mathematics in Microsoft Excel and in Benninga’s Principles of
Corporate Finance with Microsoft Excel.