Excel Modeling in Corporate Finance, Fifth Edition
By Craig W. Holden
Contents:
Preface ……………………………………………………………………………. 8
Fifth Edition Changes ……………………………………………………………………………. 8
Ready-To-Build Spreadsheets ………………………………………………………………… 8
What is Unique about This Book ………………………………………………………….. 11
Conventions Used in This Book …………………………………………………………….. 12
Craig’s Challenge ………………………………………………………………………………… 14
ExcelTM Modeling Books ………………………………………………………………………. 14
Suggestions for Faculty Members …………………………………………………………. 14
Acknowledgments ………………………………………………………………………………… 15
About The Author …………………………………………………………. 17
PART 1 TIME VALUE OF MONEY …. 18
Chapter 1 Single Cash Flow …………………………………………… 18
1.1 Present Value …………………………………………………………………………………. 18
1.2 Future Value ………………………………………………………………………………….. 19
Problems ……………………………………………………………………………………………… 20
Chapter 2 Annuity ………………………………………………………… 21
2.1 Present Value …………………………………………………………………………………. 21
2.2 Future Value ………………………………………………………………………………….. 22
2.3 System of Four Annuity Variables …………………………………………………… 23
Problems ……………………………………………………………………………………………… 24
Chapter 3 NPV Using Constant Discounting ………………….. 25
3.1 Nominal Rate …………………………………………………………………………………. 25
3.2 Real Rate ……………………………………………………………………………………….. 26
Problems ……………………………………………………………………………………………… 27
Chapter 4 NPV Using General Discounting ……………………. 28
4.1 Nominal Rate …………………………………………………………………………………. 28
4.2 Real Rate ……………………………………………………………………………………….. 30
Problems ……………………………………………………………………………………………… 32
Chapter 5 Loan Amortization ……………………………………….. 33
5.1 Basics …………………………………………………………………………………………….. 33
5.2 Sensitivity Analysis …………………………………………………………………………. 34
Problems ……………………………………………………………………………………………… 36
Chapter 6 Lease Vs. Buy ……………………………………………….. 37
6.1 Car ………………………………………………………………………………………………… 37
6.2 Corporate ………………………………………………………………………………………. 37
Problems ……………………………………………………………………………………………… 39
PART 2 VALUATION ………………………. 40
Chapter 7 Bond Valuation …………………………………………….. 40
7.1 Annual Payments ……………………………………………………………………………. 40
7.2 EAR, APR, and Foreign Currencies ………………………………………………… 41
7.3 Duration and Convexity ………………………………………………………………….. 46
7.4 Price Sensitivity ……………………………………………………………………………… 48
7.5 System of Five Bond Variables ………………………………………………………… 50
Problems ……………………………………………………………………………………………… 51
Chapter 8 Estimating the Cost of Capital ………………………. 54
8.1 Static CAPM Using Fama-MacBeth Method ……………………………………. 54
8.2 APT or Intertemporal CAPM Using Fama-McBeth Method …………….. 58
Problems ……………………………………………………………………………………………… 63
Chapter 9 Stock Valuation …………………………………………….. 64
9.1 Dividend Discount Model ………………………………………………………………… 64
Problems ……………………………………………………………………………………………… 65
Chapter 10 Firm and Project Valuation …………………………. 66
10.1 Cash Flows for Five Equivalent Methods ……………………………………….. 66
10.2 Adjusted Present Value …………………………………………………………………. 69
10.3 Free Cash Flow To Equity …………………………………………………………….. 70
10.4 Free Cash Flow to the Firm …………………………………………………………… 71
10.5 Dividend Discount Model ………………………………………………………………. 72
10.6 Residual Income ……………………………………………………………………………. 73
10.7 Five Equivalent Methods ………………………………………………………………. 74
Problems ……………………………………………………………………………………………… 83
Appendix: Reconciling the Residual Income Method with Other
Approaches to Valuing Firms or Projects ……………………………………………… 84
Chapter 11 The Yield Curve ………………………………………….. 90
11.1 Obtaining It From Treasury Bills and Strips ………………………………….. 90
11.2 Using It To Price A Coupon Bond………………………………………………….. 91
11.3 Using It To Determine Forward Rates …………………………………………… 92
Problems ……………………………………………………………………………………………… 93
Chapter 12 US Yield Curve Dynamics …………………………… 94
12.1 Dynamic Chart……………………………………………………………………………… 94
Problems ……………………………………………………………………………………………… 99
PART 3 CAPITAL STRUCTURE ……. 101
Chapter 13 Capital Structure ……………………………………….101
13.1 Modigliani-Miller With No Taxes ………………………………………………… 101
13.2 Modigliani-Miller With Corporate Taxes …………………………………….. 103
13.3 Trade-off Model: Tax Shield vs. Distress Cost ………………………………. 105
Problems ……………………………………………………………………………………………. 107
PART 4 CAPITAL BUDGETING ……. 108
Chapter 14 Project NPV ……………………………………………….108
14.1 Basics …………………………………………………………………………………………. 108
14.2 Forecasting Cash Flows ……………………………………………………………….. 111
14.3 Working Capital …………………………………………………………………………. 112
14.4 Sensitivity Analysis ……………………………………………………………………… 114
Problems ……………………………………………………………………………………………. 117
Chapter 15 Cost-Reducing Project ……………………………….118
15.1 Basics …………………………………………………………………………………………. 118
15.2 Sensitivity Analysis ……………………………………………………………………… 121
Problems ……………………………………………………………………………………………. 122
Chapter 16 Break-Even Analysis ………………………………….123
16.1 Based On Accounting Profit ………………………………………………………… 123
16.2 Based On NPV ……………………………………………………………………………. 126
Problems ……………………………………………………………………………………………. 130
PART 5 FINANCIAL PLANNING ….. 131
Chapter 17 Corporate Financial Planning …………………….131
17.1 Actual …………………………………………………………………………………………. 131
17.2 Forecast ……………………………………………………………………………………… 134
17.3 Cash Flow …………………………………………………………………………………… 138
17.4 Ratios …………………………………………………………………………………………. 140
17.5 Sensitivity …………………………………………………………………………………… 142
17.6 Full-Scale Estimation ………………………………………………………………….. 143
Problems ……………………………………………………………………………………………. 149
Chapter 18 Du Pont System Of Ratio Analysis ………………152
18.1 Basics …………………………………………………………………………………………. 152
Problems ……………………………………………………………………………………………. 153
Chapter 19 Life-Cycle Financial Planning …………………….154
19.1 Taxable Vs. Traditional Vs. Roth Savings …………………………………….. 154
19.2 Basic Life-Cycle Planning ……………………………………………………………. 156
19.3 Full-Scale Life-Cycle Planning …………………………………………………….. 158
Problems ……………………………………………………………………………………………. 165
PART 6 INTERNATIONAL CORPORATE FINANCE ……………….. 166
Chapter 20 International Parity ……………………………………166
20.1 System of Four Parity Conditions ………………………………………………… 166
20.2 Estimating Future Exchange Rates ………………………………………………. 168
Problems ……………………………………………………………………………………………. 169
PART 7 OPTIONS AND CORPORATE
FINANCE ……………………………………….. 170
Chapter 21 Binomial Option Pricing …………………………….170
21.1 Estimating Volatility …………………………………………………………………… 170
21.2 Single Period ………………………………………………………………………………. 171
21.3 Multi-Period ……………………………………………………………………………….. 174
21.4 Risk Neutral ……………………………………………………………………………….. 178
21.5 Average of N and N-1 ………………………………………………………………….. 181
21.6 Convergence to Normal ……………………………………………………………….. 183
21.7 American With Discrete Dividends ………………………………………………. 185
21.8 Full-Scale ……………………………………………………………………………………. 189
Problems ……………………………………………………………………………………………. 194
Chapter 22 Real Options ………………………………………………196
22.1 Option To Abandon …………………………………………………………………….. 196
22.2 Option to Expand ……………………………………………………………………….. 197
22.3 Option to Contract ……………………………………………………………………… 198
22.4 Option To Choose ……………………………………………………………………….. 199
22.5 Compound Option ………………………………………………………………………. 201
Problems ……………………………………………………………………………………………. 204
Chapter 23 Black-Scholes Option Pricing ……………………..206
23.1 Basics …………………………………………………………………………………………. 206
23.2 Continuous Dividend …………………………………………………………………… 207
23.3 Implied Volatility ………………………………………………………………………… 211
Problems ……………………………………………………………………………………………. 213
Chapter 24 Debt And Equity Valuation ………………………..215
24.1 Two Methods ………………………………………………………………………………. 215
24.2 Impact of Risk …………………………………………………………………………….. 217
Problems ……………………………………………………………………………………………. 218
PART 8 EXCEL SKILLS ………………… 219
Chapter 25 Useful Excel Tricks …………………………………….219
25.1 Quickly Delete The Instructions and Arrows ………………………………… 219
25.2 Freeze Panes ……………………………………………………………………………….. 219
25.3 Spin Buttons and the Developer Tab ……………………………………………. 220
25.4 Option Buttons and Group Boxes ………………………………………………… 221
25.5 Scroll Bar ……………………………………………………………………………………. 223
25.6 Install Solver or the Analysis ToolPak ………………………………………….. 224
25.7 Format Painter……………………………………………………………………………. 224
25.8 Conditional Formatting ………………………………………………………………. 225
25.9 Fill Handle ………………………………………………………………………………….. 226
25.10 2-D Scatter Chart ……………………………………………………………………… 226
25.11 3-D Surface Chart ……………………………………………………………………… 228
DOWNLOADABLE CONTENTS
Excel Modeling in Corporate Finance Fifth Edition.pdf
Ready-To-Build spreadsheets available in both XLSX
and XLS file formats:
Ch 01 Single Cash Flow – Ready-To-Build.xlsx
Ch 02 Annuity – Ready-To-Build.xlsx
Ch 03 NPV Using Constant Discounting – Ready-To-Build.xlsx
Ch 04 NPV Using General Discounting – Ready-To-Build.xlsx
Ch 05 Loan Amortization – Ready-To-Build.xlsx
Ch 06 Lease Vs Buy – Ready-To-Build.xlsx
Ch 07 Bond Valuation – Ready-To-Build.xlsx
Ch 08 Estimating the Cost of Capital – Ready-To-Build.xlsx
Ch 09 Stock Valuation – Ready-To-Build.xlsx
Ch 10 Firm and Project Valuation – Ready-To-Build.xlsx
Ch 11 The Yield Curve – Ready-To-Build.xlsx
Ch 12 US Yield Curve Dynamics – Ready-To-Build.xlsx
Ch 13 Capital Structure – Ready-To-Build.xlsx
Ch 14 Project NPV – Ready-To-Build.xlsx
Ch 15 Cost-Reducing Project – Ready-To-Build.xlsx
Ch 16 Break-Even Analysis – Ready-To-Build.xlsx
Ch 17 Corporate Financial Planning – Ready-To-Build.xlsx
Ch 18 Du Pont System of Ratio Analysis – Ready-To-Build.xlsx
Ch 19 Life-Cycle Financial Planning – Ready-To-Build.xlsx
Ch 20 International Parity – Ready-To-Build.xlsx
Ch 21 Binomial Option Pricing – Ready-To-Build.xlsx
Ch 22 Real Options – Ready-To-Build.xlsx
Ch 23 Black-Scholes Option Pricing – Ready-To-Build.xlsx
Ch 24 Debt and Equity Valuation – Ready-To-Build.xlsx