Financial Modelling

This document provides information on financial modelling, including the calculation of yield to maturity for bonds and risk-return analysis of stock indices. It also discusses the excess return of stocks compared to benchmark indices. The document includes tables, graphs, and calculations for analysis. Subject, course code, course name, and college/university are mentioned.

Running head: FINANCIAL MODELLING
Financial Modelling
Name of the Student:
Name of the University:
Author’s Note:

1FINANCIAL MODELLING
Table of Contents
In Response to Question 1..........................................................................................................2
In Response to Question 2..........................................................................................................3
In Response to Question 3..........................................................................................................8
In Response to Question 4........................................................................................................12

2FINANCIAL MODELLING
In Response to Question 1
The Yield to Maturity for the bond was calculated on the annual payment of interest
and semi-annual payment of interest on a bond. The Yield to maturity for the bond was
calculated with the help of excel tool and relevant formula was applied for determining the
same. The Yield to Maturity or the YTM was around 2.998% for the annual interest payable
bond and was around 3.0178% for the semi-annual interest payable bond.
Annual Yield To Maturity Semi-Annual Yield To Maturity
Face Value 1000 Face Value 1000
Coupon Rate 2.65% Coupon Rate 2.65%
Current Price (P.V) 965.4 Current Price (P.V) 965.4
No of Years (N) 12 No of Years (N) 12
Annual Coupon Amt. 26.5 Semi Annual Coupon Amt. 13.25
Cumulative period pa. 1 Cumulative period pa. 2
No of Periods 12 No of Periods 24
Yield To Maturity 2.998% Yield To Maturity 1.498%
Annual YTM 2.998% Annual YTM 3.0178%
0 2 4 6 8 10 12 14
2.9700%
2.9800%
2.9900%
3.0000%
3.0100%
3.0200%
3.0300%
3.0400%
Relationship between YTM and Interest Rate
Interest Rate
YTM
The relationship between the yield to maturity and the frequency of interest rate can
be observed by the above-depicted graph where the return on the annual interest payable

3FINANCIAL MODELLING
bond is much higher due to the compounding effect. The return provided by the semi-annual
interest payable bond was much lower compared to the annual interest payable bond and the
investor should asses the same after determining the Yield to Maturity generated from each of
the same. Investors should select the bond with the highest yield to maturity so that the
investors can create wealth by investing in higher yield bonds. The Yield to maturity on the
other hand is greater than the coupon rate because of the bond trading at a discount rate
which alternatively makes the return generated from the bond higher than the interest rate of
the bond.
In Response to Question 2
The analysis of the PHLX Oil Sector Index and the NYSE Arca-Biotech Industry was
conducted in order to analyse out the risk and return analysis of the index. The data was
collected from Yahoo Finance and the trend period taken into consideration for the analysis
of the same was from the trend period 1998-2017. Analysis of a stock could be done by the
return generated by the asset class in respect of the risk undertaken by the investor.
Annual Return of Stocks
The return for the stocks were calculated on the basis of the performance of the stocks
in the trend period analysed for the company and the relevant return was calculated. The
average monthly return for the stocks were calculated by using the excel function average
return and simultaneously the risk of the stock were evaluated by using the standard deviation
formula in the same. The computed monthly return were then converted into annual return
the frequency of the return were plotted in the graph in order to carry out graphical analysis
on the same.

4FINANCIAL MODELLING
Particulars OSX BTK
Monthly Return 0.67% 1.83%
Annual Return 8.76% 22.77%
Standard Deviations 36.27% 35.13%
Variance 13.16% 12.34%
Risk-Return Analysis
Risk return analysis is an important component of financial investment and the same
needs to be dealt with the analysis of the various component of the asset class. These will
include the return generated by the stock by taking a single amount of risk, and the
consistency in the return generated by the company are some of the crucial parts which needs
to be analysed. The analysis of the stock was conducted on the basis of return generated by
each of the index by taking a single amount of risk. The formula applied for the calculation of
the same was Risk/return for determining the return generated by taking a single unit of risk.
The BTK Index has performed well on a risk return basis giving a higher amount of return for
the single amount of risk taken by the investor and the same should be selected by the
investor as an optimal asset class for the purpose of investment.
-50.00% 0.00% 50.00% 100.00% 150.00%
0.00%
10.00%
20.00%
30.00%
40.00%
50.00%
60.00%
70.00%
80.00%
90.00%
Risk Return of BTK
RISK
RETURN
-80.00% -60.00% -40.00% -20.00% 0.00% 20.00% 40.00% 60.00% 80.00%
0.00%
20.00%
40.00%
60.00%
80.00%
100.00%
120.00%
Risk-Return of OSX
RISK
RETURN

5FINANCIAL MODELLING
From the above graphical analysis it is clear that the BTK Index on a risk adjusted
basis has provided consistent and higher return than the OSX index. The return generated
from the stock was placed on the vertical axis and the risk of the return generated were placed
on horizontal axis.
Frequency Distribution
The frequency distribution for the stocks were calculated by analysing the return
generated by the stocks and the frequency of such returns. The number of bins or frequency
number was taken at 10.
OSX BTK
Bin Frequency Bin Frequency
-59.75% 1 -41.74% 1
-47.10% 0 -26.43% 0
-34.44% 1 -11.11% 3
-21.78% 3 4.21% 1
-9.13% 2 19.53% 5
3.53% 2 34.85% 1
16.19% 2 50.17% 6
28.85% 3 65.49% 2
41.50% 1 80.80% 0
54.16% 3 96.12% 0
66.82% 1 111.44% 1
79.47% 1 126.76% 0
-59.75%
-47.10%
-34.44%
-21.78%
-9.13%
3.53%
16.19%
28.85%
41.50%
54.16%
66.82%
79.47%
0
0.5
1
1.5
2
2.5
3
3.5
Return Frequency of OSX
-41.74%
-26.43%
-11.11%
4.21%
19.53%
34.85%
50.17%
65.49%
80.80%
96.12%
111.44%
126.76%
0
1
2
3
4
5
6
7
BTK

6FINANCIAL MODELLING
Equal-Weighted Portfolio
The Equal Weighted Portfolio was created by incorporating half of the investible
amount into OSX and half of the amount into BTK. The return and risk generated on the asset
class was incorporated into the portfolio in accordance with the weightage of each asset class.
The weightage given for each of the asset class was around 50% and the relevant risk and
return of the portfolio was evaluated in accordance with the same. However, it is crucial to
note that the equal weightage portfolio will help he investor in removal of unsystematic risk
by creating diversification in the investment and thereby reducing the risk of the portfolio
rather than investing into an asset class on a standalone basis.
Weight in OSX 50%
Weight in BTK 50%
Date Portfolio Return (%)
12/1/1998 -14.75%
12/1/1999 89.13%
12/1/2000 53.60%
12/1/2001 -19.32%
12/1/2002 -21.12%
12/1/2003 26.63%
12/1/2004 21.49%
12/1/2005 36.03%
12/1/2006 10.26%
12/1/2007 27.58%
12/1/2008 -38.73%
12/1/2009 53.08%
12/1/2010 31.74%
12/1/2011 -13.83%
12/1/2012 21.77%
12/1/2013 39.14%
12/1/2014 11.31%
12/1/2015 -7.15%
12/1/2016 0.42%
12/1/2017 8.09%
Average Return 15.77%
Standard Deviation 29.99%
Variance 8.99%

7FINANCIAL MODELLING
Optimum Portfolio
An Optimum Portfolio was calculated for the assignment by taking various
combinations of weights among the asset class and identifying the performance of the
portfolio in order to find out the optimal weight.
Optimum Portfolio
OSX Return Risk
15.77% 29.99%
0% 22.77% 34.24%
10% 21.37% 32.68%
20% 19.97% 31.44%
30% 18.57% 30.55%
40% 17.17% 30.07%
50% 15.77% 29.99%
60% 14.37% 30.32%
70% 12.97% 31.06%
80% 11.56% 32.17%
90% 10.16% 33.61%
100% 8.76% 35.35%
29.00% 30.00% 31.00% 32.00% 33.00% 34.00% 35.00% 36.00%
0.00%
5.00%
10.00%
15.00%
20.00%
25.00%
Portfolio Exp. Return and Standard
Deviation
Exp. Risk
Exp. Return
The return provided by the BTK Index is highly consistent with respect OSX Index in
terms of returns generated and the risk involved, which makes 100% weightage to the BTK
stock make an optimal portfolio weightage. However, it is crucial to note that because of

8FINANCIAL MODELLING
diversification and concentration risk it is optimal for the investor to consider for a portfolio
of investment rather than standalone investment. Systematic risk is always associated with
investments in the financial market and which cannot be removed but investors can reduce
the unsystematic risk in a portfolio by creating a diversified portfolio. The return and risk as
shown above was determined by taking the respective weights of index and the return/risk
generated by the index.
In Response to Question 3
The excess return for the stocks represents the over and excess return generated by the
stock using the formula:
Excess Return: Return generated on the stock – Return on Benchmark Index.
The excess return provided by the DUK stock was much more consistent than the PCG stock.
Annual Return Excess Return
DJIU (%) PCG (%) DUK (%) DJIU (%) PCG (%) DUK (%)
-26.45% -1.78% -17.38% -26.84% -24.67% 9.08%
2.31% 13.78% 11.38% 1.89% -11.47% 9.07%
8.22% 14.15% 10.19% 7.95% -5.93% 1.97%
9.65% -8.29% 21.30% 9.54% 17.94% 11.65%
5.61% 9.44% 10.95% 5.47% -3.83% 5.35%
6.81% 3.17% 7.33% 6.69% 3.63% 0.53%
25.86% 45.01% 28.78% 25.64% -19.15% 2.92%
-4.06% -3.29% -8.65% -4.64% -0.77% -4.59%
9.41% 16.31% 8.81% 8.52% -6.90% -0.59%
4.54% -29.84% 4.18% 2.71% 34.39% -0.36%
Average Return 3.69% -1.68% 3.50%
Variance of Return 0.017507 0.029938 0.002674
Standard Dev. Of Ret. 12.55% 16.41% 4.91%
The analysis of two major stocks which forms the constituent of the Dow Jones
Utility Average was taken into consideration for the analysis. The evaluation was done by

9FINANCIAL MODELLING
taking into account recent ten-year period data from the year 2008-2017 from Yahoo Finance
for assessing the same. The Capital Asset Pricing Model was taken into consideration for the
evaluating the expected return and the relevant systematic risk in the form of beta in the
company. The expected return for the stocks were calculated by using the Capital Asset
Pricing Model:
Expected Return (E(r)) = Risk Free Rate of Return+ (Beta * (Return on Market-Risk Free
Rate of Return)).
Where;
 Risk Free Rate of Return: Short Term Return (1 Year Treasury bill Rate);
 Return on Market: Return generated by the Dow Jones Industrial Average Index.
 Beta: The beta was calculated by regressing the return generated by the stock in
comparison with the return generated by the market index.
Expected Return using CAPM
Expected Return (E(r)) = Risk Free Rate of Return+ (Beta * (Return on Market-Risk Free
Rate of Return)).
Risk Free Rate 2.14%
Return on Mkt. 3.69% Exp. Return of PCG 2.36%
Beta of PCG 0.139847487 Exp. Return of DUK 1.45%
Beta of DUK -0.441326107
Return on PCG -1.68%
Return on DUK 3.50%
The formula has been applied in the context of determining the expected returns on
the stock where the return on market was taken as the return generated by the Dow Jones
Industrial Average in the trend period analysed. The expected return from the PCG stock was
around 2.36% and the expected return from the DUK stock was around 1.45% in the trend
period analysed for the stocks. The expected return from the PCG stock is at a higher level
than the DUK stock and the same should be considered for the purpose of investment. The

10FINANCIAL MODELLING
systematic risk for each of the stock could be assessed by the beta generated by the stock in
respect to the return generated from the market. The beta for the PCG stock was around 0.14
times and the beta for the DUK stock was around -0.44 times. The interpretation of the same
means that if the market moves by 1 point than the stocks are expected to move by 0.14 and -
0.44 times respectively. The above value computed i.e., the beta of the stock shows the
systematic risk of the stock in comparison with the market returns.
-0.3 -0.2 -0.1 0 0.1 0.2 0.3
-0.06
-0.04
-0.02
0
0.02
0.04
0.06
0.08
0.1
0.12
0.14
DUK Excess Return vs DJU Excess Return
SUMMARY OUTPUT

11FINANCIAL MODELLING
Regression Statistics
Multiple R
0.17246995
3
R Square
0.02974588
5
Adjusted R Square
-
0.09153588
Standard Error
0.05402322
8
Observations 10
ANOVA
df SS MS F
Significance
F
Regression 1
0.00071580
1
0.00071
6
0.24526263
3
0.63374557
1
Residual 8
0.02334807
3
0.00291
9
Total 9
0.02406387
5
Coefficients
Standard
Error t Stat P-value Lower 95% Upper 95%
Lower
95.0%
Upper
95.0%
Intercept
0.03750249
5
0.01780754
2
2.10598
9
0.06829817
8
-
0.00356177
2
0.07856676
1
-
0.00356177
2
0.07856676
1
X Variable 1
-
0.06740114
5
0.13609794
9 -0.49524
0.63374557
1
-
0.38124357
8
0.24644128
8
-
0.38124357
8
0.24644128
8

12FINANCIAL MODELLING
In Response to Question 4
The implied volatility shows the estimated amount of volatility generated by the stock
price which is helpful for determining the pricing of the options. The implied volatility is
highly consistent with the market belief where the increase in the implied volatility shows
that the market trend is bearish where investor and other shareholders/investors belief the
price of the stock will fall. The implied volatility acts as a key tool for assessing the
predictions and the forecast for the stock market which allows the investors asses the
fluctuations in a security price. The Implied Volatility generated by taking into account the
various data inputs and other relevant information for assessing the stock. The implied
volatility for the stock and the same is at the moderate level. There are other crucial factors
also which affects the implied volatility of the stock and the same are due to the tenure of the
option as short dated option usually have a low amount of implied volatility than long term
option.