FE5002 Corporate Finance and Investments
Assignment 003
Individual Report on Performance of an Investment Portfolio
Background
An investment portfolio was set up on Friday 28th February 2020. The sum invested
was £1,000,000. The portfolio consisted of two shares in FTSE100 stocks and a Gilt.
The portfolio was liquidated a year later on Friday 26th February 2021.
Requirements
You are required prepare a report that:
• Determines the return before taxation on each of the three securities and the
portfolio as a whole over the investment period (52 weeks)
• Assesses the level of risk taken for each of the three securities and for the
overall risk of the portfolio.
• Evaluate and comments on the performance of the investment portfolio
relative to the FTSE100 with consideration of risk and return.
• Discuss how the portfolio may have been hedged and any difficulties
obtaining a perfect hedge.
The report must be supported by spreadsheets that show how the calculations
underlying the report have been undertaken.
Weighting
50%
Word limit
A report of 3000 words in a Microsoft Word Document plus supporting calculations in a Microsoft Excel document.
Your report and your spreadsheet must have your student number as the first part of
filename.
For example: 10004068 Assignment Report.doc (for the report)
10004068 Assignment Calculations.xlsx (for the calculations)
1
Assessment Criteria
The assignment will be assessed on the following:
• Demonstration of an understanding of risk and return
• Ability to explain the analysis undertaken in the evaluation
• Selection of appropriate data and analysis using spreadsheets to determine
measures of risk and return.
• Quality of interpretation of the results.
• Demonstration of critical and analytical thinking in discussion and the drawing
of conclusions.
• Clarity of the report and quality presentation of data.
2
Guidance for the Assignment
Portfolio selection
You will select the portfolio yourself. In selecting the portfolio you should:
• Select two shares quoted on the FTSE100 and one gilt.
• Determine the amounts invested in each. You may select any proportions of
the three investments. However, to make the process straightforward it may
be desirable to balance the proportions is a relatively simple way. For
example e.g. 1/3 in each or 40% in each of the shares and 20% in the gilt.
Each of the three securities should constitute a minimum of 10% of the
portfolio.
• Do not try to select the portfolio for any specific criteria. It is not necessary to
create a portfolio with any specific risk or return in mind. The report will
assume that someone else has selected the portfolio and you are reporting on
the outcomes at the year end.
Measures of investment performance
To analyse the risk and the return of the investments you should calculate the
following:
The return on each investment (the bond and the two shares)
The risk of each investment (the bond and the two shares). You will need to
calculate the standard deviation of the share returns and their betas. You may
also do this for the bond (but note that you would expect the beta for a bond
should be close to zero).
The portfolio returns and portfolio risk measured by standard deviation and
beta
Use performance measures such as Sharpe, Treynor, Jensen and the
differential based on standard deviation for the shares and the portfolio
Data sources
All the data required for the exercise can be obtained from Bloomberg and this is the
easiest and fastest source to use. However you may also use Yahoo Finance for
source data for FTSE100 shares. This can be accessed at
http://uk.finance.yahoo.com/. To obtain data on government bonds you can use the
UK Debt Management Office website at
http://www.dmo.gov.uk/index.aspx?page=Gilts/Data but note that this website is
quite slow.
3
Simplifying assumptions that can be made
To simplify the calculations you can make the following assumptions:
• Assume that the investment was made at closing prices on 28th February
2020.
• Assume that the investment was liquidated at closing prices on Friday 26th
February 2021
• You may assume that coupons and dividends were recorded and received on
the payment day. This is not technically correct since dividends are paid to the
person who holds the investment on the `record day’ which precedes the
actual payment. There is a possibility that the share could have been sold at
the ex-dividend price just after the record day and the dividend will accrue to
the previous owner. The same concept applies to the coupon of a bond.
• Assume that dividends and are not reinvested but are held in a bank current
account with no interest accruing.
• To determine the risk you only need to consider the annual returns for a same
investments maturing during the investment period. You should use weekly
data for this.
• Beta can be calculated for one year (it is normally calculated over a longer
period and hence the beta quoted may differ from you calculation).
• Assume that the risk free rate over the period of investment was 2%
Data required
You will need to download the following data into a spreadsheet.
For each of the shares
• Share closing prices from Friday 1st March 2019 to Friday 26th February
2021.You will require the prices for the year prior to the investment to
determine the average return over the year and the standard deviation of
returns.
• Dividend payments and their dates during the period Friday 1st March 2019 to
Friday 26th February 2021.
For the Bond
• Bond closing prices from Friday 1st March 2019 to Friday 26th February 2021.
Again you will require the prices for the year prior to the investment to
determine the average annual return over the year and the standard deviation
of returns.
• Coupon payments and their dates during the period Friday 1st March 2019 to
Friday 26th February 2021.
For the FTSE 100 Index
• The closing index levels from Friday 1st March 2019 to Friday 26th February
2021. Again you will require the prices for the year prior to the investment to determine the average annual return over the year and the standard deviation
of returns.
The annual dividend yield for the index
You may also download other information which is relevant to your report and
evaluation.
Calculation of return on the investments, the portfolio and the FTSE100
It is easy to determine the return at the end of the period. The basic equation was
covered in the lecture of week 10. This is how to calculate the return on a share.
R=
D + P1 − Po
P0
For a bond the Dividend (D) is replaced by the coupon (C).
For the FTSE100 index the capital gain is the closing index value minus the closing
index value. The return in terms of the capital gain is therefore:
Index Value 1 − Index Value o
Index Value o
To this you will need to add the dividend yield for the index to get the total return
Return in terms of capital gain (loss) =
Determination of risk
You should determine the risk of the individual investments and the portfolio as a
whole using:
1. Standard deviation (unsystematic risk)
2. Beta (systematic risk)
Calculation of average returns on shares and their standard deviation (shares)
The data required for this is:
• Closing weekly prices for the year from 28th February 2020 to 26th February
2021
• Closing weekly prices for the year from 1st March 2019 to 28th February 2020
• Dividends and their dates for the two years.
To calculate the standard deviation of returns (also required for calculating the two
share portfolio risk and beta) the annual returns for over the whole year of
investment needs to be determined. This requires determining the weekly returns for
investments made for one year that were placed in the previous year.
This can be calculated easily on a spreadsheet by listing the share prices for the two
years and matching the pervious year price with the investment year price to
determine the capital gain/loss. It necessary to add to the share prices any dividends
that were paid over the period. Dividend are usually paid semi-annually or quarterly
with one or three interim dividends and a final dividend (usually larger than the
5
interims)
The total of capital gain plus dividends is then divided by the investment price at the
beginning of the period to obtain the return.
Calculation of average returns on shares and their standard deviation (Gilt).
The same approach can be taken with the bond but coupons are used in the
calculation rather than dividends. Note that coupons are usually paid semi-annually.
Calculation of average returns on shares and their standard deviation (FTSE
100).
Again the annual return over the whole year of investment needs to be determined
this time using two years of FTSE100 data. This requires determining the weekly
returns for investments made for one year that were placed in the previous year.
In each week the dividend yield for the FTSE100 needs to be added to the capital
gain.
Calculation functions in EXCEL
Calculation of average return
Having calculated the returns over 52 weeks, the average return over the year can
easily be found using the AVERAGE function in Excel.
Standard deviation of returns
This can be calculated using the STDEV function in Excel.
Calculation of Covariance of the two shares
The COVAR function in excel enables you to calculate the covariance between the
returns of the two shares. It can also be used to determine the Covariance between
an individual investment and the market (FTSE100 returns over the period)
Calculation of the Correlation Coefficient
The CORREL function in Excel enables you to calculate the correlation coefficient
between the two shares.
6
Presentation of your coursework
You are expected to include elements required by a formal report.
You should use headings to aim the structure of your report.
You should submit your coursework with an official cover page which you can
generate and download from your Evision. You may also produce your own
cover page which may include information such as the title of your report,
coursework, module code, module title, name of the school and date for
submission. You should NOT display your name on this cover page and any
other pages of your coursework.
You should choose Times New Roman with font of size 12 and double
Your coursework should be submitted online via Turnitin on Weblearn.
Warnings
The coursework submitted should be your own
The use of any other person’s work constitutes plagiarism and is an
assessment offence.
7
