Operational Risk with Excel and VBA

ffirs.qxd 3/5/04 4:45 PM Page iii

Operational Risk

with Excel and VBA

Applied Statistical Methods

for Risk Management

NIGEL DA COSTA LEWIS

John Wiley & Sons, Inc.

ffirs.qxd 3/5/04 4:45 PM Page iv

Copyright © 2004 by Nigel Da Costa Lewis. All rights reserved.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey.

Published simultaneously in Canada.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted

in any form or by any means, electronic, mechanical, photocopying, recording, scanning,

or otherwise, except as permitted under Section 107 or 108 of the 1976 United States

Copyright Act, without either the prior written permission of the Publisher, or authorization

through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc.,

222 Rosewood Drive, Danvers, MA 01923, 978-750-8400, fax 978-646-8600, or on the

web at www.copyright.com. Requests to the Publisher for permission should be addressed

to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ

07030, 201-748-6011, fax 201-748-6008.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their

best efforts in preparing this book, they make no representations or warranties with respect

to the accuracy or completeness of the contents of this book and specifically disclaim any

implied warranties of merchantability or fitness for a particular purpose. No warranty

may be created or extended by sales representatives or written sales materials. The advice

and strategies contained herein may not be suitable for your situation. You should consult

with a professional where appropriate. Neither the publisher nor author shall be liable for

any loss of profit or any other commercial damages, including but not limited to special,

incidental, consequential, or other damages.

For general information on our other products and services, or technical support, please

contact our Customer Care Department within the United States at 800-762-2974,

outside the United States at 317-572-3993 or fax 317-572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that

appears in print may not be available in electronic books.

For more information about Wiley products, visit our web site at www.wiley.com.

Library of Congress Cataloging-in-Publication Data

Lewis, Nigel Da Costa.

Operational risk with Excel and VBA : applied statistical methods for

risk management / Nigel Da Costa Lewis.

p. cm.

“Published simultaneously in Canada.”

Includes index.

ISBN 0-471-47887-3

1. Risk management—Statistical methods. 2. Risk

management—Mathematical models. I. Title.

HD61 .L49 2004

658.15′5′0285554—dc22

2003023869

Printed in the United States of America.

10 9 8 7 6 5 4 3 2 1

ffirs.qxd 3/5/04 4:45 PM Page v

In loving memory of my mother-in-law,

Lydora.

Her devotion and wisdom nurtured my wife

into becoming the encouraging source

of strength that she is today.

These qualities have inspired

and enabled me to complete this work.

ffirs.qxd 3/5/04 4:45 PM Page vi

ftoc.qxd 3/29/04 10:25 AM Page vii

Contents

Preface xiii

Acknowledgments xv

CHAPTER 1

Introduction to Operational Risk Management and Modeling 1

What is Operational Risk? 1

The Regulatory Environment 3

Why a Statistical Approach to Operational Risk Management? 5

Summary 6

Review Questions 6

Further Reading 6

CHAPTER 2

Random Variables, Risk indicators, and Probability 7

Random Variables and Operational Risk Indicators 7

Types of Random Variable 8

Probability 9

Frequency and Subjective Probability 11

Probability Functions 13

Case Studies 16

Case Study 2.1: Downtown Investment Bank 17

Case Study 2.2: Mr. Mondey’s OPVaR 20

Case Study 2.3: Risk in Software Development 20

Useful Excel Functions 24

Summary 24

Review Questions 25

Further Reading 26

vii

ftoc.qxd 3/29/04 10:25 AM Page viii

viii CONTENTS

CHAPTER 3

Expectation, Covariance, Variance, and Correlation 27

Expected Value of a RandomVariable 27

Variance and Standard Deviation 31

Covariance and Correlation 32

Some Rules for Correlation, Variance, and Covariance 34

Case Studies 35

Case Study 3.1: Expected Time to Complete

a Complex Transaction 35

Case Study 3.2: Operational Cost of System Down Time 37

Summary 38

Review Questions 38

Further Reading 39

CHAPTER 4

Modeling Central Tendency and Variability of Operational Risk Indicators 41

Empirical Measures of Central Tendency 41

Measures of Variability 43

Case Studies 44

Case Study 4.1: Approximating Business Risk 44

Excel Functions 47

Summary 47

Review Questions 48

Further Reading 49

CHAPTER 5

Measuring Skew and Fat Tails of Operational Risk Indicators 51

Measuring Skew 51

Measuring Fat Tails 54

Review of Excel and VBA Functions for Skew and Fat Tails 57

Summary 58

Review Questions 58

Further Reading 58

CHAPTER 6

Statistical Testing of Operational Risk Parameters 59

Objective and Language of Statistical Hypothesis Testing 59

Steps Involved In Conducting a Hypothesis Test 61

Confidence Intervals 64

Case Study 6.1: Stephan’s Mistake 65

Excel Functions for Hypothesis Testing 67

ftoc.qxd 3/29/04 10:25 AM Page ix

Contents ix

Summary 67

Review Questions 68

Further Reading 68

CHAPTER 7

Severity of Loss Probability Models 69

Normal Distribution 69

Estimation of Parameters 72

Beta Distribution 72

Erlang Distribution 77

Exponential Distribution 77

Gamma Distribution 78

Lognormal Distribution 80

Pareto Distribution 81

Weibull Distribution 81

Other Probability Distributions 83

What Distribution Best Fits My Severity of Loss Data? 84

Case Study 7.1: Modeling Severity of Loss Legal

Liability Losses 86

Summary 91

Review Questions 91

Further Reading 92

CHAPTER 8

Frequency of Loss Probability Models 93

Popular Frequency of Loss Probability Models 93

Other Frequency of Loss Distributions 98

Chi-Squared Goodness of Fit Test 100

Case Study 8.1: Key Personnel Risk 102

Summary 103

Review Questions 103

Further Reading 103

CHAPTER 9

Modeling Aggregate Loss Distributions 105

Aggregating Severity of Loss and Frequency

of Loss Distributions 105

Calculating OpVaR 108

Coherent Risk Measures 110

Summary 112

Review Questions 112

Further Reading 112

ftoc.qxd 3/29/04 10:25 AM Page x

x CONTENTS

CHAPTER 10

The Law of Significant Digits and Fraud Risk Identification 113

The Law of Significant Digits 113

Benford’s Law in Finance 116

Case Study 10.1: Analysis of Trader’s Profit and Loss

Using Benford’s Law 116

A Step Towards Better Statistical Methods of Fraud Detection 118

Summary 120

Review Questions 120

Further Reading 120

CHAPTER 11

Correlation and Dependence 121

Measuring Correlation 121

Dependence 132

Stochastic Dependence 134

Summary 136

Review Questions 136

Further Reading 136

CHAPTER 12

Linear Regression in Operational Risk Management 137

The Simple Linear Regression Model 137

Multiple Regression 148

Prediction 153

Polynomial and Other Types of Regression 155

Multivariate Multiple Regression 155

Regime-Switching Regression 157

The Difference Between Correlation and Regression 158

A Strategy for Regression Model Building

in Operational Risk Management 159

Summary 159

Review Questions 159

Further Reading 160

CHAPTER 13

Logistic Regression in Operational Risk Management 161

Binary Logistic Regression 161

Bivariate Logistic Regression 165

Case Study 13.1: Nostro Breaks and Volume

in a Bivariate Logistic Regression 172

Other Approaches for Modeling Bivariate Binary Endpoints 173

ftoc.qxd 3/29/04 10:25 AM Page xi

Contents xi

Summary 176

Review Questions 177

Further Reading 177

CHAPTER 14

Mixed Dependent Variable Modeling 179

A Model for Mixed Dependent Variables 179

Working Assumption of Independence 181

Understanding the Benefits of Using a WAI 184

Case Study 14.1: Modeling Failure in Compliance 184

Summary 185

Review Questions 186

Further Reading 186

CHAPTER 15

Validating Operational Risk Proxies Using Surrogate Endpoints 187

The Need for Surrogate Endpoints in OR Modeling 187

The Prentice Criterion 188

Limitations of the Prentice Criterion 191

The Real Value Added of Using Surrogate Variables 193

Validation Via the Proportion Explained 196

Limitations of Surrogate Modelling in Operational

Risk Management 200

Case Study 15.1: Legal Experience as a Surrogate Endpoint

for Legal Costs for a Business Unit 201

Summary 202

Review Questions 202

Further Reading 202

CHAPTER 16

Introduction to Extreme Value Theory 203

Fisher-Tippet–Gnedenko Theorem 203

Method of Block Maxima 205

Peaks over Threshold Modeling 206

Summary 207

Review Questions 207

Further Reading 207

CHAPTER 17

Managing Operational Risk with Bayesian Belief Networks 209

What is a Bayesian Belief Network? 209

Case Study 17.1: A BBN Model for Software Product Risk 212

Creating a BBN-Based Simulation 215

ftoc.qxd 3/29/04 10:25 AM Page xii

xii CONTENTS

Assessing the Impact of Different Managerial Strategies 216

Perceived Benefits of Bayesian Belief Network Modeling 218

Common Myths About BBNs—

The Truth for Operational Risk Management 222

Summary 224

Review Questions 224

Further Reading 224

CHAPTER 18

Epilogue 225

Winning the Operational Risk Argument 225

Final Tips on Applied Operational Risk Modeling 226

Further Reading 226

Appendix

Statistical Tables 227

Cumulative Distribution Function of the Standard

Normal Distribution 227

Chi-Squared Distribution 230

Student’s t Distribution 232

F Distribution 233

Notes 237

Bibliography 245

About the CD-ROM 255

Index 259

flast.qxd 3/1/04 10:37 AM Page xiii

CHAPTER 1: Preface

Preface

U ntil a few year ago most banks and other financial institutions paid little

attention to measuring or quantifying operational risk. In recent years

this has changed. Understanding and managing operational risk are essen￾tial to a company’s future survival and prosperity. With the regulatory spot￾light on operational risk management, there has been ever-increasing

attention devoted to the quantification of operational risks. As a result we

have seen the emergence of a wide array of statistical methods for measur￾ing, modeling, and monitoring operational risk. Working out how all these

new statistical tools relate to one another and which to use and when is a

not a straightforward issue.

Although a handful of books explain and explore the concept of oper￾ational risk per se, it is often quite difficult for a practicing risk manager to

turn up a quickly digestible introduction to the statistical methods that can

be used to model, monitor, and assess operational risk. This book provides

such an introduction, using Microsoft Excel and Visual Basic For Applica￾tions (VBA) to illustrate many of the examples. It is designed to be used “on

the go,” with minimal quantitative background. Familiarity with Excel or

VBA is a bonus, but not essential. Chapter sections are generally short—

ideal material for the metro commute into and from work, read over lunch,

or dipped into while enjoying a freshly brewed cup of coffee. To improve

your understanding of the methods discussed, case studies, examples, inter￾active illustrations, review questions, and suggestions for further reading

are included in many chapters.

In writing this text I have sought to bring together a wide variety of sta￾tistical methods and models that can be used to model, monitor, and assess

operational risks. The intention is to give you, the reader, a concise and

applied introduction to statistical modeling for operational risk manage￾ment by providing explanation, relevant information, examples, and inter￾active illustrations together with a guide to further reading. In common

xiii

flast.qxd 3/1/04 10:37 AM Page xiv

xiv PREFACE

with its sister book Applied Statistical Methods for Market Risk Manage￾ment (Risk Books, March 2003), this book has been written to provide the

time-starved reader, who may not be quantitatively trained, with rapid and

succinct introduction to useful statistical methods that must otherwise be

gleaned from scattered, obscure, or mathematically obtuse sources. In this

sense, it is not a book about the theory of operational risk management or

mathematical statistics per se, but a book about the application of statistical

methods to operational risk management.

Successful modeling of operational risks is both art and science. I hope

the numerous illustrations, Excel examples, case studies, and VBA code list￾ings will serve both as an ideas bank and technical reference. Naturally, any

such compilation must omit some models and methods. In choosing the

material, I have been guided both by the pragmatic “can do” requirement

inherent in operational risk management, and by my own practical experi￾ence gained over many years working as a statistician and quantitative

analyst in the City of London, on Wall Street, at the quantitative research

boutique StatMetrics, and in academia. Thus, this is a practitioners’ guide

book. Topics that are of theoretical interest but of little practical relevance

or methods that I have found offer at best a marginal improvement over the

most parsimonious alternative are ignored. As always with my books on

applied statistical methods, lucidity of style and simplicity of expression

have been my twin objectives.

flast.qxd 3/1/04 10:37 AM Page xv

Acknowledgments

M any people have helped considerably during the process of researching

and writing this text. I particularly would like to thank StatMetrics for

providing me with the time and financial resources to complete this project.

I would also like to express my sincere appreciation to Angela Lewis for her

wonderful cooperation, understanding, and support throughout the period

of this research. The inspiration for this text came from a discussion I had

with an organization keen to set up an operational risk department. It

became clear by the end of my discussion that their analysts and senior

management lacked even a basic understanding of what can and cannot be

achieved using statistical methods.

Following this conversation I decided to “act out” various roles to

gather information about the approach, tools, and techniques of opera￾tional risk. The most enjoyable role was as a job seeker, in which my resume

would be forwarded to potential employers who were seeking a analyst to

model their operational risk. Almost inevitably, I would be offered an inter￾view and would then play the role of a badly informed candidate or a super

knowledgeable expert. Through this process it became clear that there is lit￾tle consensus on how operational risk should be modeled and very little

understanding of the role statistical methods can play in informing decision

makers. I particularly wish to thank and at the same time apologize to those

anonymous individuals who interviewed me as a real candidate for a posi￾tion in their operational risk departments. I am deeply indebted to them all.

xv

flast.qxd 3/1/04 10:37 AM Page xvi