## Blackscholes Formula In The Spreadsheet

Figure 11.1 contains the details of the call used as an example throughout the previous chapter, together with the calculations required to evaluate its Black-Scholes value. In early implementations, various polynomial approximations to the cumulative normal probability distribution were used. Now, in the current versions of Excel, the NORMSDIST function takes care of this task. Having evaluated d1 and d2, the corresponding cumulative probabilities N(d1) and N(d2) are in cells E11 and E16....

## Xy Charts

Excel provides many types of charts, but for mathematical, scientific and financial purposes, the XY (Scatter) chart is preferable. Where unambiguous, we refer to this type simply as an XY chart. The important point is that the XY chart has both the X and Y axes numerically scaled. In all other two-axis chart types (including the Line chart), only the vertical axis is numerically scaled, the horizontal X axis being for labels. Creating an XY chart is handled by the Chart Wizard which proceeds...

## Communicating Between Macros And The Spreadsheet

Having introduced the use of variables and control structures in programming, this section discusses how VBA macros can obtain inputs directly from the spreadsheet and how results can be returned. It concentrates on the communication between macros and the spreadsheet. In most cases, a subroutine consists of three parts input of data, calculations (or manipulation of inputs), then output of results. Writing the VBA code for the calculations usually involves conventional programming techniques....

## Writing VBA Userdefined Functions

As well as automating spreadsheet operations, VBA code can be used to write functions which work in the same way as Excel's 'built-in' functions. Functions are particularly useful when they automate calculation tasks that are required repeatedly. Function calculations are carried out 'off sheet', allowing leaner and cleaner layouts to be devised, and functions are portable so that once programmed they can be copied to other workbooks. Whereas a VBA subroutine usually performs one or more...

## Implied Volatility

Next, we use the Black-Scholes formula to estimate the volatility implied by different option prices in the market. Finding the implied volatility that matches an observed option price is simply a matter of trial and error. There are a number of different approaches Excel's Goal Seek, Corrado and Miller's (1996) approximation, Manaster and Koehler's (1982) approach or a user-defined function. We examine each in turn. In Figure 13.2, the details of our standard call are set out (cells B4 to...

## Expected Value And Variance Functions With Array Inputs

Suppose that the cash flows from an investment are uncertain, having five possible levels, the probabilities of each level being as shown in the first column of Figure 4.3. Evaluating the Expected Value of the cash flows requires each cash flow to be multiplied by its probability, as shown in column D, before the products are summed. Figure 4.3 Cash flows with their probabilities in the ExpValues sheet of VBFNS workbook Figure 4.3 Cash flows with their probabilities in the ExpValues sheet of...

## Problem Threecombining A Riskfree Asset With A Risky Portfolio

The last of the generic problems focuses on combining the risky portfolio with the risk-free asset. Problem Three is solved in two stages. First, the optimal combination of the risky assets is decided which is a special case of Problem Two . Second, the split between the risk-free asset and the optimal risky portfolio is decided essentially Problem One . See Figure 6.14 for details. First, ignoring the risk-free option, the optimal split between assets1 and 2 is decided. In the previous section...

## Numerical Methods

The calculation of statistical expectation is at the heart of all numerical methods of option valuation. Binomial trees provide an important insight into the mechanics behind the Black-Scholes formula, taking advantage of the close relationship between the normal and the binomial distribution as the number of steps in the tree increases. The modelling of the share price process with a tree allows option payoffs to be calculated at intermediate points in the option's life. This, combined with...

## Userdefined Functions In Modulel

Many of the functions in this module involve straightforward transfers of the formulas from the spreadsheets into VBA. This is true of the ISHorizonWealth function and all the functions whose names start with Portfolio. There are two useful functions CorrMatrix and VCVMatrix for calculating the correlation matrix and the vari-ance-covariance matrix, the only input being a matrix of returns data retsmat . Both functions systematically select two columns of the returns matrix, apply Excel...

## Estimating Beta Coefficients

To estimate betas, it is best to work with log returns in fact log of excess returns . The sheet Beta illustrates how the beta coefficient for a share is estimated by regression using monthly returns on ShareA and Index columns B and C in Figure 7.1 . Here, the index is the FTSE100 and there are 60 monthly returns for both ShareA and Index. The beta strictly speaking, the 'unadjusted' beta is simply the slope estimate from the regression of share excess returns on index excess returns. Beta...

## Pros And Cons Of Developing Vba Functions

In reflecting on the advantages and disadvantages of developing functions for spreadsheet calculations, the following points are worth making. When compared to spreadsheet calculations and complex cell formulas, functions have the advantage of compressing extensive calculations into one cell. Provided it is programmed accurately and intelligibly named, the user-defined function can pack considerable power into a single cell. Entering functions into cells is less error prone than entering...

## Valueatrisk

In this section, we examine the concept of Value-at-Risk VaR , implemented for equities as an analytic calculation derived from the assumed lognormal distribution of returns. Empirical observation of share returns shows that they typically demonstrate some skewness. However, rescaling returns by taking their logs usually 'natural' logs gives a more symmetrical spread of values. So when compared with returns, log returns are usually more symmetric and tend to follow the normal distribution. When...

## Performance Measurement and Attribution

The broad objective of performance measurement is to assess and compare the performance past returns of different investment strategies. Our main emphasis in this chapter will be on the choice between passive and active investment strategies when assembling a portfolio of risky assets. The secondary emphasis will be on choosing the level of risk-free assets to complement the chosen risky portfolio. Under a pure passive investment strategy, an investor holds a portfolio that is an exact copy by...

## Macros In ModuleM

The macros to generate the efficient frontier using Solver are contained in this module sheet. Solver is an add-in to Excel and, unless it appears on the Tools menu, needs to be installed. Additionally, to use the Solver functions in VBA, the module sheet must include a reference to the SOLVER.xla add-in file Call SolverAdd Range portret1 , 2, Range target1 Call SolverOk Range portsd1 , 2, 0, Range change1 Call SolverSolve True SolverFinish End Sub The EffFrontier1 macro contains a single...

## Introduction to VBA

This chapter introduces the use of VBA and macros within Excel, and attempts to do so in the context of examples where VBA enhances spreadsheet functionality. Whilst not intended as a full introduction to programming in VBA, it argues the case for mastering some VBA, touches on the 'object-oriented' focus of the language, and suggests an incremental approach to mastering the code. Most of the examples in this chapter involve simple code, the first 'hands-on' examples being in section 3.3.3....

## Generating The Efficient Frontier Huang And Litzenbergers Approach

If there are no constraints on individual asset weights, the efficient frontier can also be produced elegantly from algebra. Although some of the more advanced textbooks such as Elton and Gruber, 1995 demonstrate this through the iterative solution of a set of simultaneous equations, there is a better approach. Huang and Litzenberger denoted HL have described how to find two points on the frontier, and then to generate the whole of the frontier from these points by applying a result due to...

## Introduction to Equities

In Part II of the book we look at equities. The coverage of relevant topics is spread over the following three chapters Chapter 6 on portfolio optimisation, Chapter 7 on asset pricing and Chapter 8 on performance measurement and attribution. In this introductory chapter, we briefly summarise the finance theory covered in the Equities part of the book and introduce the range of numerical methods implemented in the spreadsheets that accompany the text. The development of mean-variance portfolio...

## Summary

Developing VBA functions capitalises on the user's knowledge of spreadsheets and of Excel's functions. Programming is involved, but at a straightforward and intuitively obvious level. In contrast to subroutines that perform actions with objects, VBA functions are procedures which return values, any calculations involved being carried out 'off-sheet'. A key distinction is that Function procedures are 'passive', that is, the code within the function cannot manipulate ranges. Most of Excel's...

## Portfolio Variance Function With Array Inputs

Functions to calculate the mean and variance of returns for portfolios are easy to develop. To illustrate the process, a simple spreadsheet formulation using Excel functions is explained, then the cell formulas are coded in VBA to produce the corresponding functions. The spreadsheet in Figure 4.4 contains risk and return information for three assets, A, B and C, and shows the results of constructing a portfolio with equal amounts of each of the three assets. The asset returns are in cells C5...

## Two Functions With Multiple Inputs For Valuing Options

As yet, Excel does not have a built-in function to calculate the value of an option using the Black-Scholes formula. This allows us to develop a user-defined function suitable for valuing a call, named BSCallValue say. The underlying theory that is the background to the Black-Scholes formula is introduced in Part III of the book. Although at this stage you will not necessarily understand the option value formula, remember that our purpose here is merely to turn the formula into workable VBA...

## Generating the Efficient Frontier with Solver

This application requires a degree of familiarity with portfolio theory and optimisation with Excel's Solver add-in. The reader may prefer to delay inspecting this material until Chapter 6 has been studied. In looking at portfolios with several risky assets the problem is to establish the asset weights for efficient portfolios, i.e. those that have minimum risk for a specified expected return. It is straightforward to get the optimum weights for a target return with Solver. If the optimisation...

## Goal Seek

Goal Seek is another of Excel's static procedures. This tool produces a solution that matches a formula cell to a numerical target. For example, in Figure 2.29 there is a discrepancy between the market price in G8 and the Black-Scholes call value G4 for an option on a share. The Black-Scholes value depends on the volatility of the share, and always contains an estimate of future volatility. Suppose we want to know the size of the volatility that will match the two, or equivalently make the...

## Appendix 3a The Visual Basic Editor

The Visual Basic Editor was substantially changed for Excel 97 and later versions and this appendix details some of its features. It can be activated by pressing the VBE button on the VB toolbar or by pressing Alt F11 in the Excel window. Once activated, you can toggle between the Excel and the Visual Basic windows with Alt Tab in the usual manner. The Visual Basic Editor can also be opened via the Excel menubar by choosing Tools then Macro then Visual Basic Editor. The Visual Basic window...

## Normal Probability Plot

One useful data visualisation is the so-called normal probability plot. This plot of a set of readings shows whether the variation in the readings can be assumed to be statistically normal. Suppose there are 50 readings. Then the order statistics of a set of 50 normally distributed readings called norm-scores are plotted against the standardised values of the readings called z-scores . If the readings are effectively normal, the points resulting from the two scores will lie more or less on a...

## Using Excel Functions and VBA Functions in Code

As seen in the Quartiles subroutine, to use an Excel function in a VBA procedure, you need to prefix the function name with Application. In Excel 2000, the function name can be prefixed by WorksheetFunction or Application. For compatibility with earlier versions of Excel, we continue to use Application. In contrast, VBA functions require no prefix, as we have seen with MsgBox, IsNumeric, InputBox, etc. In fact, there are relatively few specific numeric functions built into VBA, currently just...

## Objectoriented Aspects Of

A few concepts that you need to grasp follow from the fact that VBA is an 'object-oriented' programming language. Each Excel object represents a feature or a piece of functionality in Excel, e.g. workbooks, worksheets, ranges, charts, scenarios, etc. are all Excel objects as is Excel itself the Application object . You program in VBA to manipulate the properties and apply methods to Excel objects. Many statements can be made about objects and VBA, but essentially they can be condensed into four...

## Accessing Functions In Excel

Excel provides many worksheet functions, which are essentially calculation routines that have been coded up. They are useful for simplifying calculations performed in the spreadsheet, and also for combining into VBA macros and user-defined functions topics covered in Chapters 3 and 4 . The Paste Function button labelled fx on the standard toolbar gives access to them. It was previously known as the function wizard. As Figure 2.1 shows, functions are grouped into different categories...

## Preface

When asked why they tackled Mount Everest, climbers typically reply Because it was there. Our motivation for writing Advanced Modelling in Finance is for exactly the opposite reason. There were then, and still are now, almost no books that give due prominence to and explanation of the use of VBA functions within Excel. There is an almost similar lack of books that capture the true vibrant spirit of numerical methods in finance. It is no longer true that spreadsheets such as Excel are inadequate...

## Introduction

We hope that our text, Advanced Modelling in Finance, is conclusive proof that a wide range of models can now be successfully implemented using spreadsheets. The models range across the complete spectrum of finance including equities, equity options and bond options spanning developments from the early fifties to the late nineties. The models are implemented in Excel spreadsheets, complemented with functions written using the VBA language within Excel. The resulting user-defined functions...

## Related Excel Workbooks

Part I which concentrates on Excel functions and procedures and understanding VBA has three related workbooks, AMFEXCEL, VBSUB and VBFNS which accompany Chapters 2, 3 and 4 respectively. Part II on equities has three related workbooks, EQUITY1, EQUITY2 and EQUITY3 which accompany Chapters 6, 7 and 8 respectively. Part III on options on equities has four files, OPTION1, OPTION2, OPTION3 and OPTION4 which accompany Chapters 10, 11, 12 and 13 respectively. Part IV on bonds has two related...

## Topics Covered

There are four parts in the book, the first part illustrating the advanced modelling features in Excel followed by three parts with applications in finance. The three parts on applications cover equities, options on equities and options on bonds. Chapter 2 emphasises the advanced Excel functions and techniques that we use in the remainder of the book. We pay particular attention to the array functions within Excel and provide a short section detailing the mathematics underlying matrix...

## Advanced Excel Functions and Procedures

The purpose of this chapter is to review certain Excel functions and procedures used in the text. These include mathematical, statistical and lookup functions from Excel's extensive range of functions, as well as much-used procedures such as setting up Data Tables and displaying results in XY charts. Also included are methods of summarising data sets, conducting regression analyses, and accessing Excel's Goal Seek and Solver. The objective is to clarify and ensure that this material causes the...

## Excel Solutions

The spreadsheets demonstrate how Excel can be used as a prototype for building models. Within the individual spreadsheets, all the formulas in the cells can easily be examined and we have endeavoured to incorporate all intermediate calculations in cells of their own. The spreadsheets also allow the hallmark ability to 'what-if' by changing parameter values in cells. The implementation of all the models and methods occurs twice once in the spreadsheets and once in the VBA functions. This dual...

## Simple Binomial Tree

1.2 Asset price assumptions 2 1.3 Mathematical and statistical problems 2 1.7 Related Excel workbooks 5 1.8 Comments and suggestions 5 Part One Advanced Modelling in Excel 7 2 Advanced Excel functions and procedures 9 2.1 Accessing functions in Excel 9 2.2 Mathematical functions 10 2.3 Statistical functions 12 2.3.1 Using the frequency function 12 2.3.2 Using the quartile function 14 2.3.3 Using Excel's normal functions 15 2.7.1 Setting up Data Tables with one input 20 2.7.2 Setting up Data...

## Advanced Modelling in Finance using Excel and VBA

Chichester New York Weinheim Brisbane Singapore Toronto Copyright 2001 by John Wiley amp Sons, Ltd, Baffins Lane, Chichester, West Sussex PO19 1UD, England National 01243 779777 International 44 1243 779777 e-mail for orders and customer service enquiries cs-books wiley.co.uk Visit our Home Page on http www.wiley.co.uk or http www.wiley.com All Rights Reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic,...

## Excel and VBA

Advanced Modelling in Finance _ using Excel and VBA _ JOHN WILEY amp SONS, LTD Chichester New York Weinheim Brisbane Singapore Toronto Advanced Modelling in Finance _ using Excel and VBA _ Operational Risk Measurement and Modelling Jack King Advance Credit Risk Analysis Financial Approaches and Mathematical Models to Assess, Price and Dictionary of Financial Engineering John F. Marshall Pricing Financial Derivatives The Finite Difference Method Domingo A Tavella and Curt Randall Handbook of...