VBA高级建模在金融中的应用与实践

需积分: 9 165 浏览量更新于2024-07-25 收藏 1.61MB PDF 举报

《高级建模：VBA在金融中的应用》是一本由Mary Jackson和Mike Staunton合著的专业书籍，作为Wiley Finance系列的一部分，它专为金融专业人士提供深入探讨高级建模技术的实用指南。该书主要聚焦于如何利用Excel和Visual Basic for Applications (VBA) 这两种广泛使用的工具，在复杂的金融环境中进行模型构建与分析。书中内容涵盖了多个金融领域的核心主题，包括但不限于： 1. **高级金融建模**：讲解如何运用VBA在资产定价、风险管理、信用风险评估等方面设计和实施高级模型，提升决策支持的精确性和效率。 2. **金融操作风险**：通过Jack King的《操作风险：测量与模型》一章，讨论了如何量化和管理操作层面的风险，确保金融机构稳健运营。 3. **信贷风险分析**：Didier Cossin和Hugues Pirotte的著作深入剖析了信用风险的金融方法和数学模型，对于定价和管理信贷风险具有指导意义。 4. **金融工程词典**：John F. Marshall编撰的词典，提供了关于金融工程概念的全面解释，便于理解复杂金融工具和模型。 5. **衍生品定价**：Pricing Financial Derivatives: The Finite Difference Method 介绍了基于有限差分法的定价技术，适用于期权和其他衍生产品。 6. **利率建模**：Jessica James和Nick Webber的作品研究了利率市场中的动态模型，对理解利率风险至关重要。 7. **混合工具手册**：如 convertible bonds、preferred shares等，Izzy Nelken主编的手册详细介绍了这些复杂金融产品的特性和定价策略。 8. **外汇期权**：David F. DeRosa的修订版《外汇期权》更新了这一领域的重要知识，反映了市场最新发展。 9. **股权衍生品**：The Handbook of Equity Derivatives, Revised Edition 由Jack Francis, William Toy, 和 J. Gregg Whittaker合著，覆盖了股权衍生品的定价和策略。 10. **波动率与相关性在定价中的应用**：Riccardo Rebonato的著作展示了如何通过考虑波动率和相关性来定价股票、外汇和利率期权。 11. **风险管理与分析**：第一卷着重于金融风险的度量和模型构建，Carol Alexan的作品提供了实用的风险管理框架。《高级建模：VBA在金融中的应用》是一本理论与实践结合的综合性教程，为金融从业人员提供了宝贵的工具和技术，帮助他们利用VBA进行高效且精准的金融模型设计和分析。通过深入学习这本书，读者将能提升金融决策的科学性和有效性。

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 ﬁnance including equities, equity options and bond

options spanning developments from the early ﬁfties to the late nineties. The models are

implemented in Excel spreadsheets, complemented with functions written using the VBA

language within Excel. The resulting user-deﬁned functions provide a portable library of

programs with more than sufﬁcient speed and accuracy.

Advanced Modelling in Finance should be viewed as a complement (or dare we say,

an antidote) to traditional textbooks in the area. It contains relatively few derivations,

allowing us to cover a broader range of models and methods, with particular emphasis

on more recent advances.

The major theoretical developments in ﬁnance such as portfolio theory in the 1950s,

the capital asset pricing model in the 1960s and the Black–Scholes formula in the 1970s

brought with them analytic solutions that are now straightforward to calculate. The subse-

quent decades have seen a growing body of developments in numerical methods. With an

intelligent choice of parameters, binomial trees have assumed a central role in the more

numerically-intensive calculations now required to value equity and bond options. The

centre of gravity in ﬁnance now concerns the search for more efﬁcient ways of performing

such calculations rather than the theories from yesteryear.

The breadth of the coverage across ﬁnance and the sophistication needed for some

of the more advanced models are testament to the ability of Excel, the built-in functions

contained in Excel and the real programming environment that VBA provides. This allows

us to highlight the commonality of assumptions (lognormality), mathematical problems

(expectation) and numerical methods (binomial trees) throughout ﬁnance as a whole.

Without exception, we have tried to ensure a consistent and simple notation throughout

the book to reinforce this commonality and to improve clarity of exposition.

Our objective in writing a book that covers the broad range of subjects in ﬁnance

has proved to be both a challenge and an opportunity. The opportunity has provided

us with the chance to overview ﬁnance as a whole and, in so doing, to make impor-

tant connections and bring out commonalities in asset price assumptions, mathemat-

ical problems, numerical methods and Excel solutions. In the following sections we

summarise a few of these unifying insights that apply to equities, options and bonds

with regard to ﬁnance, mathematical topics, numerical methods and Excel features. This

is followed by a more detailed summary of the main topics covered in each chapter of

the book.

1.1 FINANCE INSIGHTS

The genesis of modern ﬁnance as a subject separate from economics started with

Markowitz’s development of portfolio theory in 1952. Markowitz used utility theory to

model the preferences of individual investors and to develop a mean–variance approach

2 Advanced Modelling in Finance

to examining the trade-off between return (as measured by an asset’s mean return) and

risk (measured by an asset’s variance of return). This subsequently led to the development

by Sharpe, Lintner and Treynor of the capital asset pricing model (CAPM), an equilibrium

model describing expected returns on equities. The CAPM introduced beta as a measure

of diversiﬁable risk, arguing that the creation of portfolios served to minimise the speciﬁc

risk element of total risk (variance).

The next great theoretical development was the equity option pricing formula of Black

and Scholes, which rested on the ability to create a (riskless) hedge portfolio. Contempora-

neously, Merton extended the Black–Scholes formula to allow for continuous dividends

and thus also options on commodities and currencies. The derivation of the original

formula required the solving of the diffusion (or heat) equation familiar from physics, but

was subsequently encompassed by the broader risk-neutral approach to the valuation of

derivatives.

1.2 ASSET PRICE ASSUMPTIONS

Although portfolio theory was derived through individual preferences, it could also have

been obtained by making assumptions about the distribution of asset price returns. The

standard assumption is that equity returns follow a lognormal distribution–equivalently we

can say that equity log returns follow a normal distribution. More recently, practitioners

have examined the effect of departures from strict normality (as measured by skewness

and kurtosis) and have also proposed different distributions (for example, the reciprocal

gamma distribution).

Although bonds have characteristics that are different from equities, the starting point

for bond option valuation is the short interest rate. This is frequently assumed to follow

the lognormal or normal distribution. The result is that familiar results grounded in these

probability distributions can be applied throughout ﬁnance.

1.3 MATHEMATICAL AND STATISTICAL PROBLEMS

Within the equities part, the mathematical problems concern optimisation. The optimi-

sation can also include additional constraints, exempliﬁed by Sharpe’s development of

returns-based style analysis. Beta is estimated as the slope coefﬁcient in a linear regression.

Options are valued in the risk-neutral framework as statistical expectations. The normal

distribution of log equity prices can be approximated by an equivalent discrete bino-

mial distribution. This binomial distribution provides the framework for calculating the

expected option value.

1.4 NUMERICAL METHODS

In the context of portfolio optimisation, the optimisation involves portfolio variance, and

the numerical method needed for optimisation is quadratic programming. Style analysis

also uses quadratic programming, the quantity to be minimised being the error variance.

Although not usually thought of as optimisation, linear regression chooses slope coef-

ﬁcients to minimise residual error. Here optimisation is of a different kind, regression

analysis, which provides analytical formulas to calculate the beta coefﬁcients.

Turning to option valuation, the binomial tree provides the structure within which

the risk-neutral expectation can be calculated. We highlight the importance of parameter

Introduction 3

choice by examining the convergence properties of three different binomial trees. Such

trees also allow the valuation of American options, where the option can be exercised at

any date prior to maturity.

With European options, techniques such as Monte Carlo simulation and numerical

integration are also used. Numerical search methods, in particular the Newton–Raphson

approach, ensure that volatilities implied by option prices in the market can be estimated.

1.5 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 spread-

sheets and once in the VBA functions. This dual approach serves as an important check

on the accuracy of the numerical calculations.

Some of the VBA procedures are macros, normally seen by others as the main purpose

of VBA in Excel. However, the majority of the procedures we implement are user-deﬁned

functions. We demonstrate how easily these functions can be written in VBA and how

they can incorporate Excel functions, including the powerful matrix functions.

The Goal Seek and Solver commands within Excel are used in the optimisation tasks.

We show how these commands can be automated using VBA user-deﬁned functions and

macros. Another under-used aspect of Excel involves the application of array functions

(invoked by the CtrlCShiftCEnter keystroke combination) and we implement these in

user-deﬁned functions. To improve efﬁciency, our binomial trees in user-deﬁned functions

use one-dimensional arrays (vectors) rather than two-dimensional arrays (matrices).

1.6 TOPICS COVERED

There are four parts in the book, the ﬁrst part illustrating the advanced modelling features

in Excel followed by three parts with applications in ﬁnance. The three parts on applica-

tions 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 manipulation.

Chapter 3 introduces the VBA programming environment and illustrates a step-by-step

approach to the writing of VBA subroutines (macros). The examples chosen demonstrate

how macros can be used to automate and repeat tasks in Excel.

Chapter 4 moves on to VBA user-deﬁned functions, which have a crucial role

throughout the applications in ﬁnance. We emphasise how to deal with both scalar

and array variables–as input variables to VBA functions, their use in calculations and

ﬁnally as output variables. Again, we use a step-by-step approach for a number of

examples. In particular, we write user-deﬁned functions to value both European options

(the Black–Scholes formula) and American options (binomial trees).

Chapter 5 introduces the ﬁrst application part, that dealing with equities.

Chapter 6 covers portfolio optimisation, using both Solver and analytic solutions. As

will become the norm in the remaining chapters, Solver is used both in the spreadsheet

4 Advanced Modelling in Finance

and automated in a VBA macro. By using the array functions in Excel and VBA, we detail

how the points on the efﬁcient frontier can be generated. The development of portfolio

theory is divided into three generic problems, which recur in subsequent chapters.

Chapter 7 looks at (equity) asset pricing, starting with the single-index model and

the capital asset pricing model (CAPM) and concluding with Value-at-Risk (VaR). This

introduces the assumption that asset log returns follow a normal distribution, another

recurrent theme.

Chapter 8 covers performance measurement, again ranging from single-parameter

measures used in the very earliest days to multi-index models (such as style analysis) that

represent current best practice. We show, for the ﬁrst time in a textbook, how conﬁdence

intervals can be determined for the asset weights from style analysis.

Chapter 9 introduces the second application part, that dealing with options on equities.

Building on the normal distribution assumed for equity log returns, we detail the creation

of the hedge portfolio that is the key insight behind the Black–Scholes option valuation

formula. The subsequent interpretation of the option value as the discounted expected

value of the option payoff in a risk-neutral world is also introduced.

Chapter 10 looks at binomial trees, which can be viewed as a discrete approxima-

tion to the continuous normal distribution assumed for log equity prices. In practice,

binomial trees form the backbone of numerical methods for option valuation since they

can cope with early exercise and hence the valuation of American options. We illustrate

three different parameter choices for binomial trees, including the little-known Leisen and

Reimer tree that has vastly superior convergence and accuracy properties compared to the

standard parameter choices. We use a nine-step tree in our spreadsheet examples, but the

user-deﬁned functions can cope with any number of steps.

Chapter 11 returns to the Black–Scholes formula and shows both its adaptability

(allowing options on assets such as currencies and commodities to be valued) and its

dependence on the asset price assumptions.

Chapter 12 covers two alternative ways of calculating the statistical expectation that

lies behind the Black–Scholes formula for European options. These are Monte Carlo

simulation and numerical integration. Although these perform less well for the simple

options we consider, each of these methods has a valuable role in the valuation of more

complicated options.

Chapter 13 moves away from the assumption of strict normality of asset log returns and

shows how such deviation (typically through differing skewness and kurtosis parameters)

leads to the so-called volatility smile seen in the market prices of options. Efﬁcient

methods for ﬁnding the implied volatility inherent in European option prices are described.

Chapter 14 introduces the third application part, that dealing with options on bonds.

While bond prices have characteristics that are different from equity prices, there is a

lot of commonality in the mathematical problems and numerical methods used to value

options. We deﬁne the term structure based on a series of zero-coupon bond prices, and

show how the short-term interest rate can be modelled in a binomial tree as a means of

valuing zero-coupon bond cash ﬂows.

Chapter 15 covers two models for interest rates, those of Vasicek and Cox, and Ingersoll

and Ross. We detail analytic solutions for zero-coupon bond prices and options on zero-

coupon bonds together with an iterative approach to the valuation of options on coupon

bonds.

Chapter 16 shows how the short rate can be modelled in a binomial tree in order

to match a given term structure of zero-coupon bond prices. We build the popular

剩余277页未读，继续阅读

13909

粉丝: 0
资源: 2

VBA高级建模在金融中的应用与实践

Excel与VBA在金融高级建模中的应用

使用Excel和VBA进行财务建模

Linux Arm版Eclipse Modeling工具下载

Advanced Modeling with Java.pdf

Advanced Predictive Modeling Using Sas EM51 Course Notes 2004

Credit Risk Modeling Using Excel and VBA

Advanced control and modeling of deposition processes

modeling and evaluation of advanced linear interference aware receivers

Advanced Electrical Drives-Analysis, Modeling, Control

Visually modeling user behaviors using Advanced Model Server.pdf

最新资源