Calculate the correlation coefficient of a portfolio

The data that appear in the following exercises can be found in the Excel data file QF (4FBL663) Seminar 5 (Data).xlsx, which is posted on Blackboard.

The data refer to Profit after Tax, Personnel Expenses, Net Turnover, Number of Employees, and Research and Development (R&D) as a percentage of Net Turnover, for a sample of 38 firms.

Construct two new variables in your Excel file, i.e. Personnel Expenses divided by Number of Employees, a variable which could be interpreted as the average wage / salary paid by each firm, and Profits per Employee, which can be interpreted as a measure of profitability.

Question 1

Derive the following scatter plots (Excel XY graphs), and use Excel to calculate the correlation coefficient associated with each graph:

  1. (a)  Number of Employees against Net Turnover
  2. (b)  Profit per Employee against Number of Employees
  3. (c)  Personnel Expenses per Employee against Number of Employees
  4. (d)  Profit against Number of Employees
  5. (e)  Personnel Expenses per Employee against Profit per Employee
  6. (f)  Personnel Expenses against Net Turnover
  7. (g)  Profit per Employee against Research and Development (R&D) as apercentage of Net Turnover

(h) As for part (g), above, but only for firms with R&D greater than zero


Within the context of each of these scatter plots and associated correlation coefficients, discuss the notion of there being a relationship between each set of two variables, and, if the data supports a relationship, how such a relationship might be justified (or indeed how such an implied relationship might be nonsense).

Question 2

Use Excel to derive the regression output relating to the implied relationships in parts (b), (d), (e) and (h) in Question 1, above. Comment on the results from these regressions.


Virgin File QF (4BFBL663) Seminar 5 (Data)

Data after awnsering question Quantitative Finance week 5 excel file


Diversification utilising hedging in a portfolio of assets


– If purchase shares in Company, how might returns on this asset be measures?

– We know price of shares in Company A at point of purchase, i.e. time period t, is Pt .

– Assume price of shares in Company A one period into future is Pt+1 .

– Return on investment will then be proportionate increase in Pt , hence:

rt =Pt+1Pt Pt


– However, this calculation implies share prices move in discrete manner.

– Recall from Lecture 1 that if invest £P at an annual interest rate of i, where interest compounded annually, then future value of investment would be:

F =Pe^it – After 1-year we would have:

F =Pei

– Therefore can generalise for any time period, where r denotes rate of return over period, as:

F =Pe^r


– Therefore, in terms of share price, we have: Pt+1 =Pter

– Hence rate of return can be derived as
ln P = ln P + ln er = ln P + r

( t+1) ( t ) ( ) ( t )
– Solving for r, we therefore obtain the log-return, i.e.:

r =ln P −ln P
( t+1) ( t )

– From now on, when we refer to returns, we are generally talking about log-returns.


– Can capture essential characteristics of these returns via their mean and variance, or standard deviation.

– The standard deviation of returns is used as a measure of the risk of the asset.

– Investors seek to maximise their returns on investments, however, will have to trade-off returns against risk.

– This means that asset that, on average, generates high returns will tend to do so with greater variability.

– Hence, high average return assets will be high risk assets and exhibit a large standard deviation, denoted σ.


– Simplest approach for calculating returns and risk is to use historical data on returns.

– Then use sample statistics of these historical returns as estimates of expected return and risk of asset.

– However, problems with using past data:
– How reliable are past returns as a guide to future returns? – How far back do you have to go?
– What about the impact of past shocks?



– Assume that an investor has two investments available to him, i.e. to hold shares in Company A and to hold shares in Company B.

– Let rA and rB denote the returns on Company A’s and Company B’s shares, respectively, where, in both cases, these returns are random variables.

– Hence, rA and rB will both have probability distributions, with expected values of E(rA) and E(rB), respectively.

– Assume that standard deviations of these distribution are σA and σB, respectively.

– Assume further that σB > σA, hence, E(rA) > E(rB).


– If investor is risk-averse, then Company A’s shares will be more attractive.

– If investor is risk-lover, then Company B’s shares will be more attractive.

– However, an alternative strategy would be to combine shares in some way, instead of just buying shares in one of the companies.

– This process of combining them is known as constructing an investment portfolio.


– Assume that investor has investment fund and forms an investment portfolio by investing a proportion, denoted α, in Company A’s shares.

– This proportion is known as the portfolio weight for A.
– Investor will then invest the remainder of investment

fund, i.e. 1 – α, in Company B’s shares.

– Now need to determine what the return this portfolio is?

– Return on portfolio will be a weighted average of returns on the individual shares, i.e.:

Er =Eαr+1−αr=αEr +1−αEr (P)A()B(A)()(B)


  • –  What about the risk on the portfolio?
  • –  Risk on portfolio will be reflected in the variance of the

returns on the portfolio, i.e. Var (rP).

– Important to note that variance of portfolio returns is not a simple weighted average of variances of the returns on individual assets in portfolio.

– In general, portfolio variance is less than the weighted average variances of individual asset returns, thereby indicating that you have effectively reduced risk.

– This is know as portfolio diversification, where by constructing a portfolio, you have reduced overall risk.


– The variance of the returns on the portfolio will be:

Varr =Varαr+1−αr (P)A()B

– It can be shown that: Var r =Var αr + 1−α r  ( P )  A ( ) B 

=α2σ2+1−α σ2+2α 1−α Cov r ;r  ABAB   ( )  ( ) ( )

– Or, alternatively:
Var r =α2σ2+1−α σ2+2α 1−α ρ σ σ  (P) A( )B( )A;BAB


– Assume that an investor is considering constructing a portfolio of two assets, i.e. Asset A and Asset B, where:

rA =0.05;σA =0.04;rB =0.1andσB =0.12

– The portfolio weight for asset A, i.e. α, is assumed to range between 0 and 1, i.e. between 0% and 100%, in increments of 0.05, or 5%.

– Thus, substituting 0.05 for E(rA) and 0.1 for E(rB), and the various assumed values for α, in the portfolio returns equation, will produce various returns on the portfolio.

– Portfolio variance formula is then used to derive corresponding values of portfolio sigma.



– Risk vs. return (assuming ρA;B = 1): Figure 5.3, Correlation=1


– Why does a combination of two perfectly negatively correlated assets produce a riskless portfolio?

– Essentially because any change in returns of one asset is entirely offset by a change in the other.

– In practice, perfectly negatively correlated assets are unlikely to occur, so not all risk can be diversified away.

– However, any negative correlation, and even zero correlation, will allow for some risk diversification.

– Given the possibility of short-selling, even positive correlations can allow for risk diversification.




– How do we identify the optimum portfolio?

– Firstly, can reject all portfolios below minimum variance point, denoted MV, on the combination line, as these are inefficient (as can still reduce variance and increase returns further).

– Efficient frontier therefore defined as segment of combination line that excludes inefficient portfolios.

– Optimum portfolio is therefore that portfolio on efficient frontier with preferred risk-return properties.

– This optimum point will essentially reflect an investor’s attitude to risk vs. return.


– However, if we assume that investor can borrow or lend at risk-free rate, then portfolio can be indentified that will form part of investor’s final portfolio.

– This borrowing / lending occurs as follows:
– Lend at risk-free rate by buying government bonds; and
– Borrow at risk-free rate by short-selling government bonds.

– Capital market line (CML) is defined as the line drawn from risk-free interest rate on the y-axis tangential to the efficient frontier.

– Point of tangency between CML and efficient frontier is known as market portfolio, denoted M.


– The CML allows us to either reduce risk beyond MV point or increase returns beyond what would be optimum on efficient frontier:

– If lend at risk-free, i.e. buy government bonds, can shift down CML to risk levels below MV point; while

– If borrow at risk-free rate, i.e. short-sell government bonds, can shift up CML to returns above the efficient frontier.

– Note that when asset are perfectly positively correlated, with possibility of short-selling, risk can be entirely diversified away, however, occurs at rate of return that is less than risk- free rate.

– CML is therefore effective way to reduce risk of positively correlated assets.



– Assume have constructed a portfolio of two assets with following efficient frontier and rF = 3%:

Figure 5.8 Identification of Market Portfolio with the Capital Market Line

Risk-free rate


– M is market portfolio with rM = 6.25% and σM = 3%.

– What if σM is too high for investor:

– Can’t move around efficient frontier as would increase risk; but

– CanmovedownCMLbylendingatrisk-freerate;where

– If σM = 2% is required, this can be achieved by investing 33% in risk-free asset and 67% in market portfolio.

– What if σM is too low for investor:

  • –  Can move round efficient frontier, and increase risk; but
  • –  BettersolutionismoveupCMLbyborrowingatrisk-freerate;

– If σM = 6% is required, this can be achieved by giving weight of – 100% to risk-free asset, and therefore a weight of 200% to market portfolio; where

– Results in rM = 9.5%, as opposed to rM = 8%, otherwise.


1. Wehaveexaminedtheconceptofriskininvestments.

2. We have highlighted the importance of constructing an investment portfolio in the market.

3. We have looked at how to implement the Efficiency frontier for a portfolio.

4. We have looked at how to construct the Capital Market Line for a portfolio.


Guided Independent Study Week
Guided Independent “Party” Week Readings to Be Done and May Be Examined


– Please ensure that you read:

– Lecture notes for Lecture 6;
– Beninga (Chapters 10 & 11)
– Haugen (Chapters 6, 8 and 9)
– Adams,etal.(Chapter12,pp.245-269,andChapter13) – Elton, et al. (Chapters 4 to 6)
– Jackson & Staunton (Chapter 7)

– For the seminar, please complete the exercises for Seminar 6.

Lecturer by Stefan Van Dellen at the University of Westminster <<<