Financial modelling means a financial model which is designed to represent in mathematical terms the relationships among the variables of a financial problem so that it can be used to answer questions or make projections. Some of the spreadsheet solutions created capture some of these relationships and can answer "what if" questions to some extent. But because they are not primarily designed with these objectives in mind, they do not try to capture as many of these interdependencies as possible, and their structures often make it difficult to answer "what if" questions or make projections with them.
In creating financial models, one always keeps in mind to capture as many of the interdependencies among the variables of the model as possible. In addition to structuring the models in such a way that it is easy to ask "what if" questions, that is, change the values of the independent variables and observe how they affect the values of the key dependent variables.
The question suggests buying a property in West Yorkshire, financed through a mortgage loan offered by Yorkshire Building Society. The loan being offered is for an amount of £250,000, at a fixed rate of 7.5% for 25 years. The calculated monthly payment has worked out to £1,847.48.
In a loan amortization table, each of the monthly payments is applied to the declining remaining loan balance to pay off the loan in full by the end of the loan life. The monthly payment is calculated by using Excel's PMT function. The beginning monthly amount is the initial loan balance, and in the following months the beginning balance will be the month-end balance of the previous month. Each month the monthly payment is split into two components; the interest component and the principal repayment component. Each month -end balance will equal the next month-beginning balance less the principal repayment for that month.
This loan amortization schedule shows how the amortizing loan gets paid off over the months with the equal monthly payments. In the early years, most of the payments go towards paying interest and the remaining loan balance declines slowly. However, towards the end of the loan life, larger and larger portions of the payment contribute to paying off the principal. Because the monthly payment is calculated by setting the Future Value (FV) in the PMT function as zero, this model pays off the loan using equal monthly payments. This model will work only if the interest rate remains fixed for the entire loan-life and all the annual payments are equal.
An option button is used for interest rate, as the user can see the change in loan payment, with the increase or decrease of interest rate. This makes it very user friendly. In this loan amortization table, simple Excel created formulas are used to calculate the loan repayment schedule. A graph showing payments of loan on a monthly basis till end of loan term has been plotted. Freeze Pane has been used to freeze the loan input schedule, to make the synopsis easily readable, and to move between the panes, to see the required data needed.
A large number of first-time buyers of mortgages in the UK market, as of October 2009, now show repayments of both capital and interest, as lenders shy away from interest only mortgages. In the latter half of 2006, interest-only mortgages without a specified repayment vehicle accounted for a high percentage of loans, which is not seen in 2009, with the worsening of the credit crunch.
As of November 2009, sales of houses, have continued to pick up. Different types of housing are reflecting different levels of recovery, which inturn reflect the current credit conditions. All the main housing price indices, in recent months have been pointing to a recovery, as interest rates are at a record low, and sales slowly picking up. Many economists believe that, as markets improve the prices will come under pressure, as more people will choose to sell their properties, and interest rates will also begin to rise.
Secondly, the loan amortization table, was also completed by using Excel VBA.
The loan schedule was similar, with the loan being offered for an amount of £250,000, at a fixed rate of 7.5% for 25 years. The calculated monthly payment also worked out to £1,847.48. Preparing this table in Excel VBA, gives more flexibility to building the model for future outputs, with various differential interest rates. This model can be amended flexibly because anticipating the changes required by the user, the code can be formulated accordingly with slight changes. When the macro is run, the loan amortization schedule displays.
The offering by Leeds Building Society of a mortgage with variable rates is a better deal. This is because, the base lending rate of the Bank of England is at one of its lowest levels of 0.5%, and the mortgage loan offered at two percentage points above the base lending rate, makes it an attractive offer. Also, with the changes in the monetary and economic cycle of England reflected in the lending rates over the loan period, makes it more appealing to the customer. Since the loan repayment period is over 300 months, variable interest rates will benefit the customer, as it reflects the market rate. Also, with the fixed interest rate payment, the customer might end up paying much more over the 30 year period.
The loan payment period of 300 months was divided into 3 periods, each with 100 months. 3 different rates were applied to these periods. The rate for the starting period from month 1 reflects the current base lending rate of the Bank of England at 0.5% with 2% on top applied by the Leeds Building Society, with the current recessionary economic scenario, set to probably continue its effects well into 2015. No one can predict how the interest rates will move for the next 6 months, or for next 300 months. Even with a Bloomberg Yield Curve on the Bond Yield, it is only a future estimated prediction based on past data. So my analysis, gives a user, a clear view in a very simple amortization table, with equal periods of loan payment, for each of the increasing interest rates chosen. Also, increasing interest rates were chosen, to reflect the trend in the economy, to imply that when interest rates go down, they will rise up again. But to predict such a trend over a period of 300 months is not possible. Interest rate change every year, so this table gives a very simple visualization of some of the important concepts discussed herein. Goal seek was used to find the monthly payment required to payoff the loan according to the variable interest rate schedule.
The Bank of England's base lending rate was imported from the Bank of England's website for the 2007-2009 period using the web query feature of Excel. This table shows how the base lending rate has changed from a high point of 5.75% to the current lending rate of 0.5%. An option button has also been included to enable the user to choose between a fixed interest rate or a flexible interest rate, which will show his loan payoff till maturity in both cases. Also, a graph has been plotted, which changes with the selection of the fixed or flexible interest rate, to reflect the loan payoff till maturity against the monthly installments.
In March 2009, the Bank of England decided that in addition to setting the Bank Rate at 0.5%, it would start to inject money directly into the economy in order to meet the inflation target. The increased reductions in the Bank Rate have provided a large boost to the economy but as the Bank Rate approaches zero, further reductions are likely to be less effective in terms of the impact on market interest rates, demand and inflation. And interest rates cannot be less than zero.
In the early 1970s, Fischer Black and Myron Scholes made a major breakthrough by deriving a differential equation to compute values for European call and put option on the stock.
To compute the value by using Black-Scholes Option Pricing Model, five inputs are needed: stock price, exercise price, risk-free rate, time to expiration date, and volatility.
The Black-Scholes equation is used for option evaluation and it is used to calculate the Implied Volatility by Trial and Error. Binomial option pricing model (BOPM) is another way of calculating the call or the put price of the stock.
Black-Scholes option pricing model:
Black-Scholes Option Pricing Model is common to use in evaluating European Option value, which assumes the log returns of share underlying the option are normally distributed, as the European Options can only be exercised at the maturity. In addition, the value of call option depends on the probability of the share price being above the strike price when it expires, on the contrast, the value of put option would depend on the probability of the share price being below the strike price at expiry. To compute the value by using Black-Scholes Option Pricing Model, five inputs are needed: stock price, exercise price, risk-free rate, time to expiration date, and volatility. Some of the features of the Black-Scholes equation are, it follows a Geometric Brownian motion with constant drift and volatility, there are no transaction costs, the stock pay no dividend, and there is no arbitrage opportunity.
The original option-pricing equations, generally referred to as the Black-Scholes equations, applied only to stocks that pay no dividends. Robert C. Merton extended the equation to dividend-paying stocks, also known as the BSM equation. An advantage of the BSM equation is that apart from using it for dividend-paying stocks, it can be used for a number of other types of options, such as options on currencies.
By using put-call parity relationship the Black-Scholes price model can be represented as:
Call option= Sexp-qTN d1-Kexp-rTN d2
Put option= K exp (-rT) N (-d2)-S exp (-qT) N (-d1)
Where S= share price; K=strike price; q= dividend yield; r= risk-free rate; T= maturity; N (d) = cumulative standard normal probability distribution function with
d1= ln(SK)+(r-q+0.5?2)T σT
d2= ln(SK)+(r-q-0.5?2)T σT
when σ is constant.
Binominal Tree Option Pricing Model
Binomial Model, also called Lattice Model or Tree Model, was first introduced by Cox, Ross, and Rubinstein to price American stock options in 1979. The model divides the time to an option's expiry into a large number of intervals, or steps. At each step it computes that the stock price will move either up or down with a given probability. This model also takes into consideration the underlying stock's volatility, the time to expiration, the risk free interest rate and dividend amount/yield. The binominal tree is an extension of the one-step binomial process, and it provides a helpful way to model the share price process using a discrete binomial distribution to approximate the normal distribution of log returns assumed in Black-Scholes Pricing Model.
Call Option: Max (ST-K, 0)
Put Option: Max (K-ST, 0)
The valuation to be used will be as following:
Δ=Time toMaturityNumber of steps=TN
Risk neutral probabilityπ=exprΔ-DU-D
There are similar assumptions between the binomial option pricing model and the Black-Scholes model. For European options without dividends, the binomial model converges on the Black-Scholes formula value as the number of steps increases. In the binomial model, the price movement follows a binomial distribution; and it approaches the normal distribution for many trials, which is assumed by Black-Scholes.
Volatility of a stock price is a measure of uncertainty about future stock price movements. It generally reflects the property of the underlying stock to fluctuate either up or down. Volatility is essentially a mathematical measure of the amount by which a stock price has fluctuated or is expected to fluctuate during a period. Usually it is quoted as the annual standard deviation of an instrument's price. The higher the volatility, the higher the potential for gain under the option and, the higher the fair value of option.
Volatility is not the same as risk. When it is interpreted as uncertainity, it becomes a key input in many investment decisions and portfolio creations.
Market participants generally monitor the average implied volatility calculated from the actively traded options on a stock, believing that it provides useful information about the stock's future price movement.
Implied volatility, which is the volatility of the price of the underlying share, can be calculated from Black-Scholes Option Pricing Model by using trial and error. However, in Excel and VBA, we use Goal-Seek to find out the Implied Volatility by setting option price, resulted from Black-Scholes Option Pricing equation to value the market value of option price by changing constant volatility to sort out implied volatility.
- The data for the option prices on the FTSE 100 equity Index was downloaded from www.euronext.com . With the inputs of stock price, exercise price, risk-free rate, time to expiration date, and estimated volatility, the implied volatility is calculated. We make use of the Black-Scholes formula to calculate the call price and the put price of the various options. Then the implied volatility is matched with an observed option price through trial and error. Here we use Goal seek to perform the trial and error. For the risk free rate, I used the Libor rate, which I downloaded from the website www.bankrate.com. The Libor rate, matches the time period of the maturity of the options. Instead of the Black-Scholes equation to solve for implied volatility of a European option, Newton's Iteration method can also be used.
- I have used date of option prices maturity on the FTSE 100 equity Index, for Jan 2010 and February 2010. However, data of option prices 6-18 months down the line (i.e.) an option maturing in December 2011, would also provide a good model for doing option pricing. I calculated the implied volatility for the different strike levels of the index, at the quoted ask price. I did this for the January 2010 and February 2010 maturities of the option.
- I then calculated the implied volatility of the put option. The prices of the put option were taken at the ask price. The implied volatility was calculated at various strike prices. I calculated the implied volatility again for January 2010 and February 2010 maturities.
- I then calculated the price of the put and the call option by Binomial Option Pricing Method. I prepared an eight step Binomial tree. To calculate the index price for 8 steps, and then I calculated the call/put option price in the reverse direction. I did this in Excel and VBA. For the Black-Scholes and the Binomial Option pricing model calculated call and put option prices to match, I would have to carry out atleast 500-1000 steps of the Binomial Tree. However, even with 8 steps, my prices of the calculated call option in the Black-Scholes equation, comes close to the call option calculated by the Binomial Option Pricing model. There are other tree models which can also be used instead of Binomial model. For example, the trinomial model and Monte Carlo option pricing methods can also be used.
- Next I used the bid price as the market price of the option to calculate the implied volatility. But this does not work, as this indicates that there is an arbitrage opportunity. Here from the bid prices it can be noticed that one can buy the options at a lower price in one market and sell it a higher price in another market to make a profit on the spread between the prices. If this was possible, then it would totally collapse the trading in the market, as large number of investors would book profits, and cause imbalance of prices in the markets. That is why there is always a spread maintained between the buyer's and seller's prices. Also, if this would happen, it would refute the efficiency market hypothesis.
- Chandan Sengupta (2004), Financial Modeling Using Excel and VBA, John Wiley & Sons Inc
- Jackson, M. and Staunton, M. (2001), Advanced Modelling in Finance using Excel and VBA, John Wiley & Sons Ltd.: England.
- Watsham T.J and Parramore, K. (1997) Quantitative Method in Finance, Thomson Learning: London.
- John C.Hull(2003),Options, Futures, and other derivatives, (4th edition), Prentice Hall
- Cox, John C., Stephen A. Ross, and Mark Rubenstein(1979): Option Pricing: A Simplified Approach., Journal of Financial Economics7:229-263
- Black, Fischer, Myron, Scholes(1973), The Pricing of Options and Corporate Liabilities, Journal of Political Economy, 637-654
- Merton, Robert,C(1973), Theory of Rational Option Pricing, Bell Journal of Economics and Management Sciences, 141-183