Financial Modelling - Coursework Example

FINANCIAL MODELING Descriptive statistics The mean is used in statistics to mean the average of a series of quantities while the median is used to express the common run and is that point that divides a series of quantities into halves. In finance, the mean would also be used to denote the average returns over a given period while the median is the middle value of the returns. The mean of S&P GSPC, Unilever, and Abbot Laboratories is $1,654.886, $2,307.918 and $34.51427 and the median is $1,649.6, $2,358.29 and $34.75 respectively. Statistically, the range, variance and the standard deviation are measures of variability. According to Lane (2015), the simplest measure of variability is the range and is arrived at by subtracting the lowest data from the highest In the financial parlance, the range would be seen as the extent to which returns from an investment have changed from the least to the highest returns. As shown in the spreadsheet, the range of returns of S&P GSPC, Unilever, and Abbot Laboratories is $810.96, $767.57, and $20.76 respectively. The variance is another measure of dispersion and is used statistically to show how scores are close to the middle of the distribution. In finance, it is used to assess the volatility or the variability from an average. The volatility of returns is used to indicate the risk an investor would take when purchasing a given security. In this case, an investor who would be interested in investing in S&P GSPC, Unilever, and Abbot Laboratories, he or she would be willing to experience volatility of 57518.25, 719, 24.06, and 28.52309 respectively. Statistically, the standard deviation is arrived at by establishing the square root of the variance and sheds light on historical volatility. In finance, it is applied to the investment’s annual rate of return to measure the investment’s volatility. As shown in the spreadsheet, the standard deviations of S&P GSPC, Unilever, and Abbot Laboratories are 239.8296, 268.1866, and 5.3407. After estimating the weekly returns from Unilever and Abbot Laboratories, this report came up with figure 1 below, where the returns for the two stocks were plotted on the Y-axis against the time on the X-Axis on a weekly basis. From figure 1 below, the returns of Unilever stock are more volatile than that of Abbot Laboratories. Between the 60th and 100th week, the returns from both stocks were more volatile and almost exhibited a common trend. Afterward, the returns from the Unilever stock dropped and again started to rise and drop while the returns from Abbot exhibited a relatively stable trend. Figure 1 the relationship between the weekly returns of Unilever and Abbot To calculate the covariance, correlation between the Unilever and Abbot Laboratories, this report first established the expected return of this portfolio. The Expected return of a portfolio made up a stock of Unilever Abbot Laboratories at the weights of 60%, and 40% was established using this expression: E(Rp)= WU +WA. I.e. E (Rp) = weight of the Unilever stock multiplied by its weakly returns plus the weight of the Abbot laboratories multiplied by its weekly returns (60%*2307.918) + (40%*34.51427). This gives an expected portfolio return of $1,398.557. From the computations done in the spreadsheet, this report found out that the covariance of the stocks in this portfolio is 1218.96, and the correlation between them is 0.8510. This covariance indicates that the returns of Unilever and that of Abbot are positively related, and the degree of this relationship is 0.851. To compute the standard deviation of this portfolio, this report used this formula: As indicated in the spreadsheet, this report first computed the variance of the portfolio, which was found to be 26, 482.33, and then computed the standard deviation by establishing its square root. This standard deviation was 162.7339. Capital Asset Pricing Model The Capital Pricing Model is usually a useful tool used to establish the stock’s beta. Beta risk measures the systematic risk by capturing the commonly regarded risk aspects: the variability in the returns of an asset over time and that returns correlation with the market portfolios return. Beta measures stocks or funds sensitivity to market movements or volatility – the degree to which a stock price fluctuates relative to S&P 500, which is a benchmark. As noted by Carlberg (2015), the Capital Pricing Model supports the use of regression analysis, and this report used it for this purpose. Regression analysis is used to calculate the beta in a view to establishing the tendency of investment returns to respond to the market swings. The markets beta is usually 1 by definition and individual securities, and portfolio values are measured depending on how they deviate away from the markets beta. Securities or portfolios with a beta of 1 show that their prices move with the broader returns of the market. Where the beta is greater than 1, it shows that individual securities and portfolio prices are more volatile than the market, and correspondingly, a beta of less than 1 is an indicator that individual securities and portfolio prices are less volatile than the market. In case the beta is 0, then, the market movements have no effect on the investment’s price. As indicated by figure 2 below, this report established that the alpha is 0.00025 as shown at the Y-intercept. The slope of the slope of the Capital Asset Pricing model regression line gives the beta of the Abbot Laboratories’ stock. This slope was found to be 2. The R-squared in this regression analysis indicates the degree of Abbot Laboratories’ returns whose explanation can be done using the market return. Figure 2 A scatter diagram of Abbot’s excess returns against the excess returns of S&P 500. CAPM Regression The beta (coefficient) established under the 90% confidence interval is 0.01388 as shown in computations below in the spreadsheet namely, regression at 90%. At 95% confidence interval, the beta was found to be 2.20296 as indicated in the Excel spreadsheet, regression at 95%. Under 90% confidence interval, the stock looks less risk as a 1% change in the market would make this stock to change by 0.01388 while under 95% confidence interval, the stock is very risky and it would change by 2.20296% when the market change by 1%. SUMMARY OUTPUT AT 95% C.I Regression Statistics Multiple R 0.623554 R Square 0.388819 Adjusted R Square 0.38485 Standard Error 0.000347 Observations 156 ANOVA   df SS MS F Significance F Regression 1 1.18E-05 1.18E-05 97.97126 3.49E-18 Residual 154 1.86E-05 1.2E-07 Total 155 3.04E-05         Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 90.0% Upper 90.0% Intercept -0.00206 0.000573 -3.58488 0.000452 -0.00319 -0.00092 -0.003 -0.00111 0.396329799 0.013879 0.001402 9.898043 3.49E-18 0.011109 0.016649 0.011558 0.016199 SUMMARY OUTPUT AT 95% C.I Regression Statistics Multiple R 0.525237157 R Square 0.275874071 Adjusted R Square 0.271141222 Standard Error 0.043030235 Observations 155 ANOVA   df SS MS F Significance F Regression 1 0.107928378 0.107928 58.28922 2.28E-12 Residual 153 0.283294971 0.001852 Total 154 0.391223349         Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept -0.00074697 0.003457782 -0.21603 0.829255 -0.00758 0.006084 -0.00758 0.006084 -0.024285164 2.2029642 0.288544853 7.634737 2.28E-12 1.632918 2.773011 1.632918 2.773011 The Jensen’s alpha is used to indicate the excess returns that a stock generates over its expected return as per the Capital Asset pricing model. According to Spaulding (2014), the Sharpe ratio is vital in determining portfolio’s returns compared to the acceptable risk, and higher performance is realised when it is high (Spaulding, 2014). This report recommends that an investment in Abbot Laboratories would be favourable since its Sharpe ratio is above, 0.185625, that of Unilever, 0.001576. According to the Morningstar, Inc. (2015), the Treynor ratio is used to measure efficiency using the relationship between the risk and the annualised risk-adjusted returns. This ratio is preferred to the Shape ratio because it considers the systematic risks since it uses the beta and not the standard deviation. Using this Treynors ratio, this report suggests that an investment in Unilever would be good because higher returns would be expected. Forecasting Through linear regression, the returns of a stock can be used to predict the returns of another stock. This is significantly supported by the autocorrelation. Therefore, the returns of Unilever can be used to predict the returns of the Abbot Laboratories. To overcome, this autocorrelation, the dependent variables are lagged by one or more periods in order to make them independent variable. This report lagged the variables for Abbot Laboratories as shown in the spreadsheet namely forecasting and the figure 3 below was constructed. Figure 3 Forecasting Bond valuation When valuing bond it is necessary to consider its terms which in most cases include the determination of its par value, coupon rate, coupon payments, and required rate of return, maturity date, and yield to maturity. In this respect, this report considered these terms when valuing the bond issued by your company. Its par value was $1,000, and this represented the amount of money that your company borrowed. It can also be viewed as the amount of money that will be paid to the bondholders upon their maturity. The coupon rate is 7% per annum, and this is the interest cost of the bond and is used to determine the periodic interest payments. Based on the time value of money, a rate of return of 5% for this bond was used, where all future payments were discounted. The coupon amount per annum will be (7%*$1000) = $70 The fair value of this bond was thus established using this expression: B0 = 70/2 * 1/(5%/2) {1-[1/(1+5%/2) ^24]} + 1,000* {1/(1+5%/2) ^24). B0 = $35*17.8850 + $1,000*0.5529 B0 = $1,178.875 From these computations, this report has determined that the fair value of your companys bond is $1,178.875. The returns received from investing in bonds are the difference between what investors pay for such bonds and what is received over the term of the bonds. The bond yield is the annualised return of such bonds and depends on the bonds purchase price and the stated interest rates. It is that rate of return that an investor will receive from her investment by receiving the present value of coupons, capital gains and the par value in relation to the price she pays. It is known that, whenever the required rate of return on a bond is equal to the coupon rate, then the fair market value is equal to the bond’s face value. For instance, in the case of your company, if the required rate of return was 7%, the fair value of this bond would be $1,000. From this relationship, this report found that the bond’s yield will usually move in an opportunity direction with the bond’s value. As shown in figure 4, below, the curve is sloping downward as the interest rate increases. It is evidence that at a rate of 5%, the value of the bond is $$1,178.875 and $1,000 at the rate of 7%. Bond yield 0 5% 7% 8% 9% 10% Yield Figure 4 Bond price/bond yield When undertaking fixed income investments, interest rates are fundamental factors that must be considered because the interest rate will always represent opportunities and risk (Williams, 2012). Whenever interest rates change, the prices of bonds also change. According to the RBC Global Asset Management Inc. (2015) and Spaulding (2014), the bond prices exhibit an inverse relationship with the interest rates such that, whenever interest rates increase, the bond prices usually fall and when interest rates fall, the bond prices rise, just as figure 4 above illustrates. According to Waring (2012), this movement is attributable to the fact that, after issuing bonds in the primary market, their face value changes when they are traded on the secondary market. For instance, a rise in the price of a bond when traded in the secondary market would attract investors to buy it now since it will pay them higher yields making them decline to buy the lower rated bonds. Consequently, this will make this bond’s value fall. The key point is that the yield of the bond will rise when its value falls. Therefore, when the interest rates or the yield of bonds rise, it translates to the declining value of the bond. This report thus concludes that rising bond prices are undesirable for existing bond investors. Callable Bonds Total coupon payments = $35*24 = $840. As a percentage of the bond’s price = (840/1000) *100 = 84%. The corresponding percentage of the price that comes from the face value = (1,178.875-1000) /1000 = 17.8875%. Callable bonds are those that must not see their maturity. Therefore, the bond issuer can redeem such a bond early and pay the bondholders a call price. This price is usually higher than the par value of the bond, and this is evidenced by the call price of $1,100 that your company will be paying to redeem these bonds. This report established that your company follows the rules regarding the call date, at least after 5 years after their issue. To compute the yield to call off this bond, this report used this function: Bond’s market price = coupon/2* [1-{1+ (YTC/2) ^-2 (5)} / (YTC/2)] +1100/ {1+YTC/2}2 (5) 1,178.875 = 70/2* [1-{1+ (YTC/2) ^-2 (5)} / (YTC/2)] +1100/ {1+YTC/2}2 (5) YTC = 4.74%. Therefore, the yield to call of your company’s bond will be 4.74% as was computed using an online calculator as indicated by figure 5 below. Figure 5 Determination of Yield to Call References Carlberg, D., 2015. Capital Asset Pricing Model Regression - Determination of Alpha and Beta. [Online] Available at: HYPERLINK "http://www.firstclassanalytics.com/capital-asset-pricing-model-regression---determination-of-alpha-and-beta.html" http://www.firstclassanalytics.com/capital-asset-pricing-model-regression---determination-of-alpha-and-beta.html [Accessed 11 April 2015]. Lane, D.M., 2015. Measures of Variability. [Online] Available at: HYPERLINK "http://onlinestatbook.com/2/summarizing_distributions/variability.html" http://onlinestatbook.com/2/summarizing_distributions/variability.html [Accessed 11 April 2015]. Morningstar, Inc., 2015. Treynor Ratio. [Online] Available at: HYPERLINK "http://www.morningstar.com/invglossary/treynor-ratio.aspx" http://www.morningstar.com/invglossary/treynor-ratio.aspx [Accessed 11 April 2015]. RBC Global Asset Management Inc., 2015. Fixed Income Investing. [Online] Available at: HYPERLINK "http://funds.rbcgam.com/learning-centre/investing-basics/understanding-yield-interest-rates.html" http://funds.rbcgam.com/learning-centre/investing-basics/understanding-yield-interest-rates.html [Accessed 11 April 2015]. Spaulding, W.C., 2014. Bond Yields. [Online] Available at: HYPERLINK "http://thismatter.com/money/bonds/bond-yields.htm" http://thismatter.com/money/bonds/bond-yields.htm [Accessed 11 April 2015]. Waring, D., 2012. Yield to Maturity – What it is and How it Works. [Online] Available at: HYPERLINK "http://learnbonds.com/how-interest-rate-changes-affect-bond-prices/" http://learnbonds.com/how-interest-rate-changes-affect-bond-prices/ [Accessed 11 April 2015]. Williams, R., 2012. The Impact of Interest Rates on Bond Investments. [Online] Available at: HYPERLINK "http://www.schwab.com/public/schwab/nn/articles/The-Impact-of-Interest-Rates-on-Bond-Investments" http://www.schwab.com/public/schwab/nn/articles/The-Impact-of-Interest-Rates-on-Bond-Investments [Accessed 11 April 2015]. Read More