Professional Financial Computing Using Excel and VBA

Preface ix
<p>CHAPTER 1 Financial Engineering and Computing 1</p>
<p>1.1 Financial Engineering and Spreadsheet Modeling 1</p>
<p>1.2 Lehman Brothers′ Products for Retail Investors 3</p>
<p>1.3 Risk Management and Basel II 4</p>
<p>1.4 About the Book 4</p>
<p>1.5. Chapter Highlights 6</p>
<p>1.6 Other Remarks 7</p>
<p>CHAPTER 2 The GARCH(1,1) Model 9</p>
<p>2.1. The Model 9</p>
<p>2.2. Excel Implementation 10</p>
<p>2.3. Excel Plus VBA Implementation 15</p>
<p>CHAPTER 3 Finite Difference Methods 21</p>
<p>3.1. Difference Equations 21</p>
<p>3.2. Excel Implementation 24</p>
<p>3.3. VBA Implementation 28</p>
<p>3.4. Crank Nicholson Scheme 33</p>
<p>CHAPTER 4 Portfolio Mean–Variance Optimization 37</p>
<p>4.1. Portfolio Selection 37</p>
<p>4.2. Excel Implementation 42</p>
<p>4.3. Excel Plus VBA Implementation 48</p>
<p>CHAPTER 5 Newton Raphson Method 59</p>
<p>5.1. Newton Raphson Method for Systems of Equations 59</p>
<p>5.2. VBA Routine 61</p>
<p>CHAPTER 6 Yield Curve Construction Using Cubic Spline 67</p>
<p>6.1. Cubic Spline Interpolation 67</p>
<p>6.2. Yield Curve Construction 75</p>
<p>6.3. Excel Plus VBA Implementation 77</p>
<p>CHAPTER 7 Binomial Option Pricing Model 85</p>
<p>7.1. Risk–Neutral Option Pricing</p>
<p>and the Binomial Tree 85</p>
<p>7.2. VBA Implementation 89</p>
<p>CHAPTER 8 The Black Derman Toy Model 95</p>
<p>8.1. The Term Structure Model and</p>
<p>the Black Derman Toy Tree 95</p>
<p>8.2. Excel Plus VBA Implementation 98</p>
<p>CHAPTER 9 Monte Carlo Option Pricing 109</p>
<p>9.1. TheMonte Carlo Method 109</p>
<p>9.2. Risk–Neutral Valuation 112</p>
<p>9.3. VBA Implementation 114</p>
<p>9.4. Exotic Options 124</p>
<p>9.5. American Options 132</p>
<p>CHAPTER 10 Portfolio Value–at–Risk 143</p>
<p>10.1. Portfolio Risk Simulation 143</p>
<p>10.2. Monte Carlo Simulation for Multiple–Asset Portfolios 152</p>
<p>10.3. Historical Simulation for Multiple–Asset Portfolios 160</p>
<p>10.4. VBA Implementation of Portfolio Risk Simulation 164</p>
<p>10.5. Drill Down of Portfolio Risk 180</p>
<p>CHAPTER 11 The Hull White Model 189</p>
<p>11.1. Hull White Trinomial Tree 189</p>
<p>11.2. Excel Plus VBA Implementation 196</p>
<p>11.3. The General Hull White Model 203</p>
<p>11.4. Implementation of the General Hull White Model 210</p>
<p>CHAPTER 12 CreditMetrics Model 221</p>
<p>12.1. The CreditMetrics Model 221</p>
<p>12.2. Individual (Segregate) Asset Valuation Framework 221</p>
<p>12.3 Monte Carlo Simulation in Detail 225</p>
<p>12.4. Excel and VBA Implementation 227</p>
<p>CHAPTER 13 KMV Merton Model 243</p>
<p>13.1. KMV Merton Model of Credit Risk 243</p>
<p>13.2. Excel and VBA Implementation 248</p>
<p>APPENDIX A VBA Programming 255</p>
<p>A.1 Introduction 255</p>
<p>A.2 A Brief History of VBA 255</p>
<p>A.3 Essential Excel Elements for VBA 256</p>
<p>A.3.1 Excel Cell Reference 257</p>
<p>A.3.2 Excel Defined Names 261</p>
<p>A.3.3 Excel Worksheet Functions 264</p>
<p>A.4 The VBA Development Environment (VBE) 266</p>
<p>A.4.1 The Developer Tab in the Ribbon 266</p>
<p>A.4.2 The Windows of VBE 268</p>
<p>A.4.3 The Project Explorer 272</p>
<p>A.4.4 The VBA Project Structure 273</p>
<p>A.4.5 The Procedure to Create a VBA Subroutine 275</p>
<p>A.4.6 The Procedure to Create a VBA Function 278</p>
<p>A.5 Basic VBA Programming Concepts 280</p>
<p>A.5.1 Variables and Data Types 285</p>
<p>A.5.2 Declaration and Assignment Statements 287</p>
<p>A.5.3 Flow Control Statements 293</p>
<p>A.6 VBA Arrays 300</p>
<p>A.7 Using Worksheet Matrix Functions in VBA 304</p>
<p>A.8 Summary 311</p>
<p>APPENDIX B The Excel Object Model 315</p>
<p>APPENDIX C VBA Debugging Tools 321</p>
<p>APPENDIX D Summary of VBA Operators 327</p>
<p>APPENDIX E Summary of VBA Functions 331</p>
<p>APPENDIX F Summary of VBA Statements 333</p>
<p>APPENDIX G Excel Array Formula 341</p>
<p>Index 349</p>