Financial modeling /Simon Benninga ; with a section on Visual Basic for applications by Benjamin Czaczkes.

"Uses Excel"--Cover.

Includes bibliographies and index.

Machine generated contents note: 0.1. Data Tables -- 0.2. What Is Getformula? -- 0.3. How to Put Getformula into Your Excel Notebook -- 0.4. Saving the Excel Workbook: Windows -- 0.5. Saving the Excel Workbook: Mac -- 0.6. Do You Have to Put Getformula into Each Excel Workbook? -- 0.7.A Shortcut to Use Getformula -- 0.8. Recording Getformula: The Windows Case -- 0.9. Recording Getformula: The Mac Case -- 1. Basic Financial Calculations -- 1.1. Overview -- 1.2. Present Value and Net Present Value -- 1.3. The Internal Rate of Return (IRR) and Loan Tables -- 1.4. Multiple Internal Rates of Return -- 1.5. Flat Payment Schedules -- 1.6. Future Values and Applications -- 1.7.A Pension Problem-Complicating the Future Value Problem -- 1.8. Continuous Compounding -- 1.9. Discounting Using Dated Cash Flows -- Exercises -- 2. Corporate Valuation Overview -- 2.1. Overview -- 2.2. Four Methods to Compute Enterprise Value (EV).

Note continued: 2.3. Using Accounting Book Values to Value a Company: The Firm's Accounting Enterprise Value -- 2.4. The Efficient Markets Approach to Corporate Valuation -- 2.5. Enterprise Value (EV) as the Present Value of the Free Cash Flows: DCF "Top Down" Valuation -- 2.6. Free Cash Flows Based on Consolidated Statement of Cash Flows (CSCF) -- 2.7. ABC Corporation, Consolidated Statement of Cash Flows (CSCF) -- 2.8. Free Cash Flows Based on Pro Forma Financial Statements -- 2.9. Summary -- Exercises -- 3. Calculating the Weighted Average Cost of Capital (WACC) -- 3.1. Overview -- 3.2.Computing the Value of the Firm's Equity, E -- 3.3.Computing the Value of the Firm's Debt, D -- 3.4.Computing the Firm's Tax Rate, Tc -- 3.5.Computing the Firm's Cost of Debt, rD -- 3.6. Two Approaches to Computing the Firm's Cost of Equity, rE -- 3.7. Implementing the Gordon Model for rE -- 3.8. The CAPM: Computing the Beta.

Note continued: 3.9. Using the Security Market Line (SML) to Calculate Merck's Cost of Equity, rE -- 3.10. Three Approaches to Computing the Expected Return on the Market, E(rM) -- 3.11. What's the Risk-Free Rate rf in the CAPM? -- 3.12.Computing the WACC, Three Cases -- 3.13.Computing the WACC for Merck (MRK) -- 3.14.Computing the WACC for Whole Foods (WFM) -- 3.15.Computing the WACC for Caterpillar (CAT) -- 3.16. When Don't the Models Work? -- 3.17. Summary -- Exercises -- 4. Valuation Based on the Consolidated Statement of Cash Flows -- 4.1. Overview -- 4.2. Free Cash Flow (FCF): Measuring the Cash Produced by the Business -- 4.3.A Simple Example -- 4.4. Merck: Reverse Engineering the Market Value -- 4.5. Summary -- Exercise -- 5. Pro Forma Financial Statement Modeling -- 5.1. Overview -- 5.2. How Financial Models Work: Theory and an Initial Example -- 5.3. Free Cash Flow (FCF): Measuring the Cash Produced by the Business.

Note continued: 5.4. Using the Free Cash Flow (FCF) to Value the Firm and Its Equity -- 5.5. Some Notes on the Valuation Procedure -- 5.6. Alternative Modeling of Fixed Assets -- 5.7. Sensitivity Analysis -- 5.8. Debt as a Plug -- 5.9. Incorporating a Target Debt/Equity Ratio into a Pro Forma -- 5.10. Project Finance: Debt Repayment Schedules -- 5.11. Calculating the Return on Equity -- 5.12. Tax Loss Carryforwards -- 5.13. Summary -- Exercises -- 6. Building a Pro Forma Model: The Case of Caterpillar -- 6.1. Overview -- 6.2. Caterpillar's Financial Statements, 2007-2011 -- 6.3. Analyzing the Financial Statements -- 6.4.A Model for Caterpillar -- 6.5. Using the Model to Value Caterpillar -- 6.6. Summary -- 7. Financial Analysis of Leasing -- 7.1. Overview -- 7.2.A Simple but Misleading Example -- 7.3. Leasing and Firm Financing-The Equivalent-Loan Method -- 7.4. The Lessor's Problem: Calculating the Highest Acceptable Lease Rental -- 7.5. Asset Residual Value and Other Considerations.

Note continued: 7.6. Leveraged Leasing -- 7.7.A Leveraged Lease Example -- 7.8. Summary -- Exercises -- 8. Portfolio Models-Introduction -- 8.1. Overview -- 8.2.Computing Returns for Apple (AAPL) and Google (GOOG) -- 8.3. Calculating Portfolio Means and Variances -- 8.4. Portfolio Mean and Variance-Case of N Assets -- 8.5. Envelope Portfolios -- 8.6. Summary -- Exercises -- Appendix 8.1: Adjusting for Dividends -- Appendix 8.2: Continuously Compounded Versus Geometric Returns -- 9. Calculating Efficient Portfolios -- 9.1. Overview -- 9.2. Some Preliminary Definitions and Notation -- 9.3. Five Propositions on Efficient Portfolios and the CAPM -- 9.4. Calculating the Efficient Frontier: An Example -- 9.5. Finding Efficient Portfolios in One Step -- 9.6. Three Notes on the Optimization Procedure -- 9.7. Finding the Market Portfolio: The Capital Market Line (CML) -- 9.8. Testing the SML-Implementing Propositions 3-5 -- 9.9. Summary -- Exercises -- Mathematical Appendix.

Note continued: 10. Calculating the Variance-Covariance Matrix -- 10.1. Overview -- 10.2.Computing the Sample Variance-Covariance Matrix -- 10.3. The Correlation Matrix -- 10.4.Computing the Global Minimum Variance Portfolio (GMVP) -- 10.5. Four Alternatives to the Sample Variance-Covariance Matrix -- 10.6. Alternatives to the Sample Variance-Covariance: The Single-Index Model (SIM) -- 10.7. Alternatives to the Sample Variance-Covariance: Constant Correlation -- 10.8. Alternatives to the Sample Variance-Covariance: Shrinkage Methods -- 10.9. Using Option Information to Compute the Variance Matrix -- 10.10. Which Method to Compute the Variance-Covariance Matrix? -- 10.11. Summary -- Exercises -- 11. Estimating Betas and the Security Market Line -- 11.1. Overview -- 11.2. Testing the SML -- 11.3. Did We Learn Something? -- 11.4. The Non-Efficiency of the "Market Portfolio" -- 11.5. So What's the Real Market Portfolio? How Can We Test the CAPM? -- 11.6. Using Excess Returns.

Note continued: 11.7. Summary: Does the CAPM Have Any Uses? -- Exercises -- 12. Efficient Portfolios Without Short Sales -- 12.1. Overview -- 12.2.A Numerical Example -- 12.3. The Efficient Frontier with Short-Sale Restrictions -- 12.4.A VBA Program for the Efficient Frontier Without Short Sales -- 12.5. Other Position Restrictions -- 12.6. Summary -- Exercise -- 13. The Black-Litterman Approach to Portfolio Optimization -- 13.1. Overview -- 13.2.A Naive Problem -- 13.3. Black and Litterman's Solution to the Optimization Problem -- 13.4. BL Step 1: What Does the Market Think? -- 13.5. BL Step 2: Introducing Opinions-What Does Joanna Think? -- 13.6. Using Black-Litterman for International Asset Allocation -- 13.7. Summary -- Exercises -- 14. Event Studies -- 14.1. Overview -- 14.2. Outline of an Event Study -- 14.3. An Initial Event Study: Procter and Gamble Buys Gillette -- 14.4.A Fuller Event Study: Impact of Earnings Announcements on Stock Prices.

Note continued: 14.5. Using a Two-Factor Model of Returns for an Event Study -- 14.6. Using Excel's Offset Function to Locate a Regression in a Data Set -- 14.7. Summary -- 15. Introduction to Options -- 15.1. Overview -- 15.2. Basic Option Definitions and Terminology -- 15.3. Some Examples -- 15.4. Option Payoff and Profit Patterns -- 15.5. Option Strategies: Payoffs from Portfolios of Options and Stocks -- 15.6. Option Arbitrage Propositions -- 15.7. Summary -- Exercises -- 16. The Binomial Option Pricing Model -- 16.1. Overview -- 16.2. Two-Date Binomial Pricing -- 16.3. State Prices -- 16.4. The Multi-Period Binomial Model -- 16.5. Pricing American Options Using the Binomial Pricing Model -- 16.6. Programming the Binomial Option Pricing Model in VBA -- 16.7. Convergence of Binomial Pricing to the Black-Scholes Price -- 16.8. Using the Binomial Model to Price Employee Stock Options -- 16.9. Using the Binomial Model to Price Non-Standard Options: An Example -- 16.10. Summary -- Exercises.

Note continued: 17. The Black-Scholes Model -- 17.1. Overview -- 17.2. The Black-Scholes Model -- 17.3. Using VBA to Define a Black-Scholes Pricing Function -- 17.4. Calculating the Volatility -- 17.5.A VBA Function to Find the Implied Volatility -- 17.6. Dividend Adjustments to the Black-Scholes -- 17.7. Using the Black-Scholes Formula to Price Structured Securities -- 17.8. Bang for the Buck with Options -- 17.9. The Black (1976) Model for Bond Option Valuation -- 17.10. Summary -- Exercises -- 18. Option Greeks -- 18.1. Overview -- 18.2. Defining and Computing the Greeks -- 18.3. Delta Hedging a Call -- 18.4. Hedging a Collar -- 18.5. Summary -- Exercises -- Appendix: VBA for Greeks -- 19. Real Options -- 19.1. Overview -- 19.2.A Simple Example of the Option to Expand -- 19.3. The Abandonment Option -- 19.4. Valuing the Abandonment Option as a Series of Puth -- 19.5. Valuing a Biotechnology Project -- 19.6. Summary -- Exercises -- 20. Duration -- 20.1. Overview -- 20.2. Two Examples.

Note continued: 20.3. What Does Duration Mean? -- 20.4. Duration Patterns -- 20.5. The Duration of a Bond with Uneven Payments -- 20.6. Non-Flat Term Structures and Duration -- 20.7. Summary -- Exercises -- 21. Immunization Strategies -- 21.1. Overview -- 21.2.A Basic Simple Model of Immunization -- 21.3.A Numerical Example -- 21.4. Convexity: A Continuation of Our Immunization Experiment -- 21.5. Building a Better Mousetrap -- 21.6. Summary -- Exercises -- 22. Modeling the Term Structure -- 22.1. Overview -- 22.2. Basic Example -- 22.3. Several Bonds with the Same Maturity -- 22.4. Fitting a Functional Form to the Term Structure -- 22.5. The Properties of the Nelson-Siegel Term Structure -- 22.6. Term Structure for Treasury Notes -- 22.7. An Additional Computational Improvement -- 22.8. Nelson-Siegel-Svensson Model -- 22.9. Summary -- Appendix: VBA Functions Used in This Chapter -- 23. Calculating Default-Adjusted Expected Bond Returns -- 23.1. Overview.

Note continued: 23.2. Calculating the Expected Return in a One-Period Framework -- 23.3. Calculating the Bond Expected Return in a Multi-Period Framework -- 23.4.A Numerical Example -- 23.5. Experimenting with the Example -- 23.6.Computing the Bond Expected Return for an Actual Bond -- 23.7. Semiannual Transition Matrices -- 23.8.Computing Bond Beta -- 23.9. Summary -- Exercises -- 24. Generating and Using Random Numbers -- 24.1. Overview -- 24.2. Rand() and Rnd: The Excel and VBA Random-Number Generators -- 24.3. Testing Random-Number Generators -- 24.4. Generating Normally Distributed Random Numbers -- 24.5. Norm. Inv: Another Way to Generate Normal Deviates -- 24.6. Generating Correlated Random Numbers -- 24.7. What's Our Interest in Correlation? A Small Case -- 24.8. Multiple Random Variables with Correlation: The Cholesky Decomposition -- 24.9. Multivariate Normal with Non-Zero Means -- 24.10. Multivariate Uniform Simulations -- 24.11. Summary -- Exercises.

Note continued: 25. An Introduction to Monte Carlo Methods -- 25.1. Overview -- 25.2.Computing IT Using Monte Carlo -- 25.3. Writing a VBA Program -- 25.4. Another Monte Carlo Problem: Investment and Retirement -- 25.5.A Monte Carlo Simulation of the Investment Problem -- 25.6. Summary -- Exercises -- 26. Simulating Stock Prices -- 26.1. Overview -- 26.2. What Do Stock Prices Look Like? -- 26.3. Lognormal Price Distributions and Geometric Diffusions -- 26.4. What Does the Lognormal Distribution Look Like? -- 26.5. Simulating Lognormal Price Paths -- 26.6. Technical Analysis -- 26.7. Calculating the Parameters of the Lognormal Distribution from Stock Prices -- 26.8. Summary -- Exercises -- 27. Monte Carlo Simulations for Investments -- 27.1. Overview -- 27.2. Simulating Price and Returns for a Single Stock -- 27.3. Portfolio of Two Stocks -- 27.4. Adding a Risk-Free Asset -- 27.5. Multiple Stock Portfolios -- 27.6. Simulating Savings for Pensions -- 27.7. Beta and Return -- 27.8. Summary.

Note continued: Exercises -- 28. Value at Risk (VaR) -- 28.1. Overview -- 28.2.A Really Simple Example -- 28.3. Defining Quantiles in Excel -- 28.4.A Three-Asset Problem: The Importance of the Variance-Covariance Matrix -- 28.5. Simulating Data: Bootstrapping -- Appendix: How to Bootstrap: Making a Bingo Card in Excel -- 29. Simulating Options and Option Strategies -- 29.1. Overview -- 29.2. Imperfect but Cashless Replication of a Call Option -- 29.3. Simulating Portfolio Insurance -- 29.4. Some Properties of Portfolio Insurance -- 29.5. Digression: Insuring Total Portfolio Returns -- 29.6. Simulating a Butterfly -- 29.7. Summary -- Exercises -- 30. Using Monte Carlo Methods for Option Pricing -- 30.1. Overview -- 30.2. Pricing a Plain-Vanilla Call Using Monte Carlo Methods -- 30.3. State Prices, Probabilities, and Risk Neutrality -- 30.4. Pricing a Call Using the Binomial Monte Carlo Model -- 30.5. Monte Carlo Plain-Vanilla Call Pricing Converges to Black-Scholes.

Note continued: 30.6. Pricing Asian Options -- 30.7. Pricing Asian Options with a VBA Program -- 30.8. Pricing Barrier Options with Monte Carlo -- 30.9. Using VBA and Monte Carlo to Price a Barrier Option -- 30.10. Summary -- Exercises -- 31. Data Tables -- 31.1. Overview -- 31.2. An Example -- 31.3. Setting Up a One-Dimensional Data Table -- 31.4. Building a Two-Dimensional Data Table -- 31.5. An Aesthetic Note: Hiding the Formula Cells -- 31.6. Excel Data Tables Are Arrays -- 31.7. Data Tables on Blank Cells (Advanced) -- 31.8. Data Tables Can Stop Your Computer -- Exercises -- 32. Matrices -- 32.1. Overview -- 32.2. Matrix Operations -- 32.3. Matrix Inverses -- 32.4. Solving Systems of Simultaneous Linear Equations -- 32.5. Some Homemade Matrix Functions -- Exercises -- 33. Excel Functions -- 33.1. Overview -- 33.2. Financial Functions -- 33.3. Dates and Date Functions -- 33.4. The Functions XIRR, XNPV -- 33.5. Statistical Functions -- 33.6. Regressions with Excel.

Note continued: 33.7. Conditional Functions -- 33.8. Large and Rank, Percentile, and PercentRank -- 33.9. Count, CountA, CountIf, CountIfs, AverageIf, AverageIfs -- 33.10. Boolean Functions -- 33.11. Offset -- 34. Array Functions -- 34.1. Overview -- 34.2. Some Built-In Excel Array Functions -- 34.3. Homemade Array Functions -- 34.4. Array Formulas with Matrices -- Exercises -- 35. Some Excel Hints -- 35.1. Overview -- 35.2. Fast Copy: Filling in Data Next to Filled-In Column -- 35.3. Filling Cells with a Series -- 35.4. Multi-Line Cells -- 35.5. Multi-Line Cells with Text Formulas -- 35.6. Writing on Multiple Spreadsheets -- 35.7. Moving Multiple Sheets of an Excel Notebook -- 35.8. Text Functions in Excel -- 35.9. Chart Titles That Update -- 35.10. Putting Greek Symbols in Cells -- 35.11. Superscripts and Subscripts -- 35.12. Named Cells -- 35.13. Hiding Cells (in Data Tables and Other Places) -- 35.14. Formula Auditing -- 35.15. Formatting Millions as Thousands.

Note continued: 35.16. Excel's Personal Notebook: Automating Frequent Procedures -- 36. User-Defined Functions with VBA -- 36.1. Overview -- 36.2. Using the VBA Editor to Build a User-Defined Function -- 36.3. Providing Help for User-Defined Functions in the Function Wizard -- 36.4. Saving Excel Workbook with VBA Content -- 36.5. Fixing Mistakes in VBA -- 36.6. Conditional Execution: Using If Statements in VBA Functions -- 36.7. The Boolean and Comparison Operators -- 36.8. Loops -- 36.9. Using Excel Functions in VBA -- 36.10. Using User-Defined Functions in User-Defined Functions -- Exercises -- Appendix: Cell Errors in Excel and VBA -- 37. Variables and Arrays -- 37.1. Overview -- 37.2. Defining Function Variables -- 37.3. Arrays and Excel Ranges -- 37.4. Simple VBA Arrays -- 37.5. Multidimensional Arrays -- 37.6. Dynamic Arrays and the ReDim Statement -- 37.7. Array Assignment -- 37.8. Variants Containing an Array -- 37.9. Arrays as Parameters to Functions -- 37.10. Using Types.

Note continued: 37.11. Summary -- Exercises -- 38. Subroutines and User Interaction -- 38.1. Overview -- 38.2. Subroutines -- 38.3. User Interaction -- 38.4. Using Subroutines to Change the Excel Workbook -- 38.5. Modules -- 38.6. Summary -- Exercises -- 39. Objects and Add-Ins -- 39.1. Overview -- 39.2. Introduction to Worksheet Objects -- 39.3. The Range Object -- 39.4. The With Statement -- 39.5. Collections -- 39.6. Names -- 39.7. Add-Ins and Integration -- 39.8. Summary -- Exercises.

This book is the standard text for explaining the implementation of financial models in Excel. As in previous editions, this fourth edition maintains the "cookbook" features and Excel dependence; it explains basic and advanced models in the areas of corporate finance, portfolio management, options, and bonds with detailed Excel spreadsheets. It also includes: a new section explaining the principles of Monte Carlo methods and their application to portfolio management and exotic option valuation; a new chapter discussing term structure modeling, with special emphasis on the Nelson-Siegel model; and a discussion of corporate valuation using pro forma models with the introduction of a new, simple model for corporate valuation based on accounting data and a minimal number of valuation parameters. --

COPYRIGHT NOT covered - Click this link to request copyright permission:

https://lib.ciu.edu/copyright-request-form