Financial Modelling - Assignment Example

Name Instructor: Course: Date: Financial Modelling Question 1 A2D1 A2D funding 84 RM ALP1 40LV AE57 par value 105.55 104.4 107.9 107 129.8 105.2 Frequency 6 months 6 months 12 months 6 months 12 months 12 months value 4.75 4.55 6.55 5.75% 6.63% 6.13% Duration 9 years 12 years 8 years 7 years 25 years 35 years market price 104.58 103.4 106.62 105.28 128.1 103.2 The above table shows the things that will be useful in the calculation of the bond duration and the yield of the bonds. b) Yields for the bonds A2D1 A2D funding 84 RM ALP1 40LV AE57 par value 105.55 104.4 107.9 107 129.8 105.2 value 4.75 4.55 6.55 5.75 6.63 6.13 market price 104.58 103.4 106.62 105.28 128.1 103.2 Yield 4.79 4.59 6.63 5.84 6.72 6.25 The calculations show the yield of the bonds of the different companies. The yield is calculated based on the par value, market value, and the coupon rate of the bond. The calculation compares the issuance of the bond at either a discount or at a premium. c) In the case above, all the bonds have a coupon rate that is less than the yield. Such bonds are priced at a discount.In the selected bonds, there is one in which the coupon rate is greater than the yield. There is none in which the coupon rate is equal to the yield. I will use hypothetical values to illustrate the case. Par value 105.55 105.55 value 4.75 4.75 market price 107.5 105.55 Yield 4.66 4.75 From the above examples, when the yield is less than the coupon rate, the bond is priced at a premium. In a case whereby the coupon rate and the yield are equal, the face value and par value of the bonds are equal. d) Durations A2D1 A2D funding 84 RM ALP1 40LV AE57 par value 105.55 104.4 107.9 107 129.8 105.2 value 4.75 4.55 6.55 5.75 6.63 6.13 market price 104.58 103.4 106.62 105.28 128.1 103.2 Yield 4.79 4.59 6.63 5.84 6.72 6.25 Pv 859 1033 743 675 3375 3172 Duration 8.21 9.99 6.97 6.41 26.35 30.74 e) Bonds that have a coupon rate that is high usually have a short duration. This happens because the company offering the bond will not be able to compensate the bond holders over a long duration. The bonds that have a high YTM also have a shorter duration. This happens in cases where the company wants to get finances for a short duration. The companies do that so that they can entice people to invest in the company. That will be beneficial to both the company and the bond holders. The bonds that have a longer time to maturity usually have a longer duration. The two things happen in a parallel manner. Question 2 a) The best deal will be the 5 year fixed rate. The option has an initial rate of 2.65%, a follow up rate of 3.99, an overall cost of 3.6%. The loan is 85% of the total value and the mortgage fee is 999 pounds Initial rate 2.65 Follow up rate 3.99 Overall cost 3.6 Total rate 10.24 Total house value 500,000 Mortgage value 425000 Interest rate 43520 Other costs 999 Total loan value 469519 Total months 60 monthly repayment 7825.317 b) The best deal for a loan for 25 years will be the 10% fixed rate. Initial rate 2.99 Follow up rate 3.99 Overall cost 3.4 Total rate 10.38 Total house value 500,000 Mortgage value 425000 Interest rate 43520 Other costs 999 Total loan value 469519 Total months 120 monthly repayment 3912.658 c) The other bank under consideration is Standard chartered Bank. Initial rate 4.1 Follow up rate 3.7 Overall cost 3.8 Total rate 11.6 Total house value 500,000 Mortgage value 375000 Interest rate 43500 Other costs 999 Total loan value 419499 Total months 60 monthly repayment 6991.65 Initial rate 4.1 Follow up rate 3.7 Overall cost 3.8 Total rate 11.6 Total house value 500,000 Mortgage value 475000 Interest rate 55100 Other costs 999 Total loan value 531099 Total months 120 monthly repayment 4425.825 d) Comparison The two banks have mortgage application fees. The two banks however vary in the percentage of loan that they offer in the loan. Standard chartered bank has a higher issue out percentage for their loan because they offer 90% of the loan value. e) The product that I would choose for a mortgage running for 5 years would be the 5 year fixed rate from Standard charted. The loan I would consider for the 25 years would be the 5 year fixed rate product from standard charted. The two products offer the least costs and that would be appropriate for a person willing to take a loan. Question 3 The stocks under consideration will be Google and Yahoo. The first stock is for Google. The stock has a return rate of 1.96%. The other stock is for Yahoo Inc. The return rate for the company is 3.02%. It will be assumed that the investor will invest equally in the two stocks that mean 50% for each stock. F) Expected return. E(R) = (.5*1.96) + (.5*3.02) = 0.98% + 1.51% = 2.49% g) Variance for the two returns Stock 1 Expected return =2.49 Actual return= 1.96 Deviation from the expected= 1.96-2.49=-0.53 Variance = (-0.53 * -0.53) * 0.5 =0.14 Stock 2 Expected return= 2.49 Actual return= 3.02 Deviation from the expected= 3.02- 2.49=0.53 = ((0.5*(0.53*0.53) =0.14 Standard deviation It will be equal to the square root of the variance = (0.14) ᶺ1/2= 0.37 h) Covariance What is the covariance between the two returns? Google Yahoo 11/03/2015 1.96 3.02 10/03/2016 1.07 -2.06 09/03/2016 1.62 1.76 08/03/2016 -0.17 -3.03 07/03/2016 -2.21 0.3 Average return 0.454 -0.002 Covariance= [(1.96-0.454) * (3.02+ 0.002)] + [(1.07-0.454) * (-2.06 + 0.002)] + [(1.62- 0.454) * (1.76+ 0.002)] + [(-0.17 – 0.454) * (-3.03 + 0.002)] + [(-2.21 – 0.454) * (0.3+ 0.002)] =4.55 -1.27 + 2.05 + 2.06 – 0.8 =6.59 I) Correlation coefficient= covariance of the two stocks/ (standard deviation 1 * standard deviation 2) =6.59/ (0.37*0.37) =48.13 j) I) The minimum variance portfolio is usually constructed to minimise the risk of the portfolio. The risk is minimised by reducing the weight of one stock and increasing the other. In the case of Google and yahoo, Yahoo has the highest rate of return. That means that the weight of Google stocks will be increased and that of yahoo. In this case, the weight of Yahoo will be taken to be 70% and that of Google will be 30%. g) The expected return for the portfolio will be = (.3*1.96) + (.7*3.02) =2.702 The expected return for the portfolio will increase with the new weights. The risk will be equal to = (1.96-2.702) = -0.742 =3.02- 2.702= 0.318 Variance = [(0.318* 0.318) * 0.7] + [-0.742*-0.742) * 0.3] =0.07 + 0.165 =0.235 Risk = (0.235) ^1/2 =0.48 The risk has reduced by 0.05. h) The optimal weight Yahoo- 70% Google- 30% Question 4 In the question, we will use the same stocks used in question 3 Criteria 1- In these criteria, we will assume a portfolio value of 250,000 with a risk of 0.53 and an average return of 2.49 Criteria 2- In these criteria, we will assume a portfolio value of 250,000 with a risk of 0.2 and an average return of 1.23 Criteria 3- In these criteria, we will assume a portfolio value of 250,000 with a risk of 0.7 and an average return of 3.02 Portfolio value 250000 Average return 2.49 Standard deviation 0.53 Confidence level 0.95 minimum return with 95% confidence 1.618228 Value of Portfolio 654556.9 Value at risk -404557 Criteria 2 Portfolio value 250000 Average return 1.23 Standard deviation 0.2 Confidence level 0.95 minimum return with 95% confidence 0.901029 Value of Portfolio 285154.4 Value at risk -225227 Portfolio value 250000 Average return 3.02 Standard deviation 0.7 Confidence level 0.95 minimum return with 95% confidence 1.868602 Value of Portfolio 860580.7 Value at risk -467151 b) In the second criteria, the volatility is lower than the benchmark rate. In that scenario, the value at risk reduces. In the third criteria, the risk rate is higher and that makes the value at risk to increase. All the values are taken at their absolute form. Read More