Introduction ............................................................................................................................................................................................ 1
What's in the Book ............................................................................................................................................................................. 2
This Book's Special Features ......................................................................................................................................................... 2
I MASTERING EXCEL RANGES AND FORMULAS
1 Getting the Most Out of Ranges ............................................................................................................................... 5
Advanced Range-Selection Techniques................................................................................................................................. 5
Mouse Range-Selection Tricks ............................................................................................................................................ 6
Keyboard Range-Selection Tricks ..................................................................................................................................... 7
Working with 3D Ranges ....................................................................................................................................................... 7
Selecting a Range Using Go To ........................................................................................................................................... 8
Using the Go To Special Dialog Box ................................................................................................................................. 9
Data Entry in a Range .................................................................................................................................................................... 13
Filling a Range ................................................................................................................................................................................... 14
Using the Fill Handle ...................................................................................................................................................................... 14
Using AutoFill to Create Text and Numeric Series ................................................................................................ 14
Creating a Custom AutoFill List ....................................................................................................................................... 16
Filling a Range........................................................................................................................................................................... 17
Creating a Series ............................................................................................................................................................................... 17
Advanced Range Copying ........................................................................................................................................................... 18
Copying Selected Cell Attributes ............................................................................................................................................. 19
Combining the Source and Destination Arithmetically ..................................................................................... 20
Transposing Rows and Columns .................................................................................................................................... 21
Clearing a Range ............................................................................................................................................................................... 22
Applying Conditional Formatting to a Range .................................................................................................................. 22
Creating Highlight Cells Rules ......................................................................................................................................... 22
Creating Top/Bottom Rules ............................................................................................................................................... 24
Adding Data Bars ..................................................................................................................................................................... 26
Adding Color Scales ................................................................................................................................................................ 28
Adding Icon Sets ...................................................................................................................................................................... 31
From Here .................................................................................................................................................................................... 32
2 Using Range Names ............................................................................................................................................................ 33
Defining a Range Name ............................................................................................................................................................... 34
Working with the Name Box ............................................................................................................................................ 34
Using the New Name Dialog Box ................................................................................................................................... 35
Changing the Scope to Define Sheet-Level Names ............................................................................................. 37
Using Worksheet Text to Define Names ..................................................................................................................... 37
Naming Constants .................................................................................................................................................................. 39
Working with Range Names ..................................................................................................................................................... 41
Referring to a Range Name ............................................................................................................................................... 41
Working with Name AutoComplete............................................................................................................................. 43
Navigating Using Range Names ..................................................................................................................................... 43
Pasting a List of Range Names in a Worksheet...................................................................................................... 44
Displaying the Name Manager ....................................................................................................................................... 44
Filtering Names ........................................................................................................................................................................ 44
Editing a Range Name's Coordinates ........................................................................................................................... 45
Adjusting Range Name Coordinates Automatically ............................................................................................ 45
Changing a Range Name .................................................................................................................................................... 47
Deleting a Range Name ....................................................................................................................................................... 47
Using Names with the Intersection Operator.......................................................................................................... 47
From Here .................................................................................................................................................................................... 49
3 Building Basic Formulas................................................................................................................................................. 51
Understanding Formula Basics ................................................................................................................................................ 51
Formula Limits in Excel 2007 and Excel 2010 ......................................................................................................... 52
Entering and Editing Formulas ....................................................................................................................................... 52
Using Arithmetic Formulas ................................................................................................................................................ 53
Using Comparison Formulas ............................................................................................................................................. 54
Using Text Formulas .............................................................................................................................................................. 54
Using Reference Formulas ................................................................................................................................................. 55
Understanding Operator Precedence ................................................................................................................................... 55
The Order of Precedence ..................................................................................................................................................... 55
Controlling the Order of Precedence ............................................................................................................................ 56
Controlling Worksheet Calculation ........................................................................................................................................ 58
Copying and Moving Formulas ................................................................................................................................................ 59
Understanding Relative Reference Format............................................................................................................... 60
Understanding Absolute Reference Format ............................................................................................................. 62
Copying a Formula Without Adjusting Relative References .......................................................................... 63
Displaying Worksheet Formulas ............................................................................................................................................. 63
Converting a Formula to a Value ............................................................................................................................................ 63
Working with Range Names in Formulas ........................................................................................................................... 64
Pasting a Name into a Formula ...................................................................................................................................... 64
Applying Names to Formulas ........................................................................................................................................... 65
Naming Formulas .................................................................................................................................................................... 68
Working with Links in Formulas ............................................................................................................................................. 69
Understanding External References .............................................................................................................................. 69
Updating Links .......................................................................................................................................................................... 71
Changing the Link Source .................................................................................................................................................. 72
Formatting Numbers, Dates, and Times ............................................................................................................................. 72
Numeric Display Formats .................................................................................................................................................... 72
Date and Time Display Formats ...................................................................................................................................... 80
Deleting Custom Formats ................................................................................................................................................... 83
From Here .................................................................................................................................................................................... 83
4 Creating Advanced Formulas ................................................................................................................................... 85
Working with Arrays ...................................................................................................................................................................... 85
Using Array Formulas ............................................................................................................................................................ 86
Understanding Array Formulas ................................................................................................................................................ 87
Array Formulas That Operate on Multiple Ranges ............................................................................................... 88
Using Array Constants ................................................................................................................................................................... 89
Functions That Use or Return Arrays ............................................................................................................................ 90
Using Iteration and Circular References .............................................................................................................................. 91
Consolidating Multisheet Data ................................................................................................................................................ 93
Consolidating by Position ................................................................................................................................................... 93
Consolidating by Category ................................................................................................................................................. 97
Applying Data-Validation Rules to Cells .............................................................................................................................. 98
Using Dialog Box Controls on a Worksheet .................................................................................................................... 101
Displaying the Developer Tab ....................................................................................................................................... 101
Using the Form Controls .................................................................................................................................................. 101
Adding a Control to a Worksheet ............................................................................................................................... 101
Linking a Control to a Cell Value ................................................................................................................................. 102
Understanding the Worksheet Controls .................................................................................................................. 103
From Here ................................................................................................................................................................................. 108
5 Troubleshooting Formulas ...................................................................................................................................... 109
Understanding Excel's Error Values .................................................................................................................................... 110
#DIV/0! ................................................................................................................................................................................... 110
#N/A ............................................................................................................................................................................................ 111
#NAME? ...................................................................................................................................................................................... 111
Case Study: Avoiding #NAME? Errors When Deleting Range Names ..................................................... 112
#NULL! ...................................................................................................................................................................................... 113
#NUM! ......................................................................................................................................................................................... 113
#REF! ......................................................................................................................................................................................... 113
#VALUE! ................................................................................................................................................................................... 114
Fixing Other Formula Errors .................................................................................................................................................... 114
Missing or Mismatched Parentheses ......................................................................................................................... 114
Erroneous Formula Results ............................................................................................................................................. 115
Fixing Circular References ............................................................................................................................................... 116
Handling Formula Errors with IFERROR() ................................................................................................................... 117
Using the Formula Error Checker ......................................................................................................................................... 118
Choosing an Error Action ................................................................................................................................................. 119
Setting Error Checker Options ....................................................................................................................................... 119
Auditing a Worksheet................................................................................................................................................................. 122
Understanding Auditing .................................................................................................................................................. 123
Tracing Cell Precedents ..................................................................................................................................................... 123
Tracing Cell Dependents .................................................................................................................................................. 124
Tracing Cell Errors ................................................................................................................................................................. 124
Removing Tracer Arrows .................................................................................................................................................. 124
Evaluating Formulas ........................................................................................................................................................... 124
Watching Cell Values.......................................................................................................................................................... 125
From Here ................................................................................................................................................................................. 126
II HARNESSING THE POWER OF FUNCTIONS
6 Understanding Functions ......................................................................................................................................... 127
About Excel's Functions ............................................................................................................................................................. 128
The Structure of a Function ..................................................................................................................................................... 128
Typing a Function into a Formula ....................................................................................................................................... 130
Using the Insert Function Feature ...................................................................................................................................... 131
Loading the Analysis ToolPak ................................................................................................................................................ 134
From Here ................................................................................................................................................................................. 134
7 Working with Text Functions ............................................................................................................................... 137
Excel's Text Functions ................................................................................................................................................................. 137
Working with Characters and Codes ................................................................................................................................. 137
The CHAR() Function ........................................................................................................................................................ 139
The CODE() Function ........................................................................................................................................................ 141
Converting Text .............................................................................................................................................................................. 142
The LOWER() Function ..................................................................................................................................................... 142
The UPPER() Function ..................................................................................................................................................... 143
The PROPER() Function.................................................................................................................................................. 143
Formatting Text ............................................................................................................................................................................. 143
The DOLLAR() Function.................................................................................................................................................. 144
The FIXED() Function ..................................................................................................................................................... 144
The TEXT() Function ........................................................................................................................................................ 145
Displaying When a Workbook Was Last Updated ............................................................................................. 145
Manipulating Text ........................................................................................................................................................................ 146
Removing Unwanted Characters from a String ........................................................................................................... 146
The TRIM() Function ........................................................................................................................................................ 146
The CLEAN() Function ..................................................................................................................................................... 147
The REPT() Function: Repeating a Character .................................................................................................... 147
Padding a Cell ......................................................................................................................................................................... 147
Building Text Charts............................................................................................................................................................ 148
Extracting a Substring ................................................................................................................................................................ 149
The LEFT() Function ........................................................................................................................................................ 149
The RIGHT() Function ..................................................................................................................................................... 150
The MID() Function ........................................................................................................................................................... 150
Converting Text to Sentence Case ............................................................................................................................... 150
A Date-Conversion Formula ........................................................................................................................................... 151
Searching for Substrings ........................................................................................................................................................... 151
The FIND() and SEARCH() Functions ................................................................................................................... 151
Case Study: Generating Account Numbers ......................................................................................................................152
Extracting a First Name or Last Name ...................................................................................................................... 153
Extracting First Name, Last Name, and Middle Initial .................................................................................... 154
Determining the Column Letter .................................................................................................................................. 154
Substituting One Substring for Another.......................................................................................................................... 155
The REPLACE() Function .............................................................................................................................................. 155
The SUBSTITUTE() Function ..................................................................................................................................... 156
Removing a Character from a String ......................................................................................................................... 156
Removing Two Different Characters from a String ........................................................................................... 157
Case Study: Generating Account Numbers, Part 2 ..................................................................................................... 157
Removing Line Feeds ......................................................................................................................................................... 158
From Here ................................................................................................................................................................................. 158
8 Working with Logical and Information Functions......................................................................... 159
Adding Intelligence with Logical Functions ................................................................................................................... 159
Using the IF() Function ................................................................................................................................................. 160
Performing Multiple Logical Tests .............................................................................................................................. 163
Combining Logical Functions with Arrays .............................................................................................................. 168
Case Study: Building an Accounts Receivable Aging Worksheet ...................................................................... 173
Getting Data with Information Functions ....................................................................................................................... 176
The CELL() Function ........................................................................................................................................................ 176
The ERROR.TYPE() Function ..................................................................................................................................... 179
The INFO() Function ........................................................................................................................................................ 180
The IS Functions .................................................................................................................................................................... 181
From Here ................................................................................................................................................................................. 183
9 Working with Lookup Functions ........................................................................................................................ 185
Understanding Lookup Tables .............................................................................................................................................. 186
The CHOOSE() Function .......................................................................................................................................................... 187
Determining the Name of the Day of the Week ................................................................................................. 187
Determining the Month of the Fiscal Year ............................................................................................................. 188
Calculating Weighted Questionnaire Results ....................................................................................................... 189
Integrating CHOOSE() and Worksheet Option Buttons ............................................................................... 189
Looking Up Values in Tables ................................................................................................................................................... 190
The VLOOKUP() Function .............................................................................................................................................. 190
The HLOOKUP() Function .............................................................................................................................................. 191
Returning a Customer Discount Rate with a Range Lookup ....................................................................... 192
Returning a Tax Rate with a Range Lookup .......................................................................................................... 193
Finding Exact Matches ...................................................................................................................................................... 193
Advanced Lookup Operations ....................................................................................................................................... 195
From Here ................................................................................................................................................................................. 200
10 Working with Date and Time Functions.................................................................................................... 201
How Excel Deals with Dates and Times ............................................................................................................................ 201
Entering Dates and Times ............................................................................................................................................... 202
Excel and Two-Digit Years .............................................................................................................................................. 203
Using Excel's Date Functions .................................................................................................................................................. 204
Returning a Date ................................................................................................................................................................... 205
Returning Parts of a Date ................................................................................................................................................ 207
Calculating the Difference Between Two Dates ................................................................................................. 216
Using Excel's Time Functions ................................................................................................................................................. 220
Returning a Time .................................................................................................................................................................. 220
Returning Parts of a Time ............................................................................................................................................... 221
Calculating the Difference Between Two Times ................................................................................................. 224
Case Study: Building an Employee Time Sheer ............................................................................................................ 224
From Here ................................................................................................................................................................................. 228
11 Working with Math Functions ............................................................................................................................. 229
Understanding Excel's Rounding Functions ................................................................................................................... 232
ROUND() Function .............................................................................................................................................................. 232
MROUND() Function ........................................................................................................................................................... 233
ROUNDDOWN() and ROUNDUP() Functions .......................................................................................................... 233
CEILING() and FLOOR() Functions ...................................................................................................................... 234
Determining the Fiscal Quarter in Which a Date Falls .................................................................................... 235
Calculating Easter Dates ................................................................................................................................................... 235
EVEN() and ODD() Functions..................................................................................................................................... 236
INT() and TRUNC() Functions ................................................................................................................................. 236
Using Rounding to Prevent Calculation Errors..................................................................................................... 237
Setting Price Points ............................................................................................................................................................. 237
Case Study: Rounding Billable Time .................................................................................................................................. 238
Summing Values ............................................................................................................................................................................ 238
SUM() Function .................................................................................................................................................................... 238
Calculating Cumulative Totals ...................................................................................................................................... 239
Summing Only the Positive or Negative Values in a Range ........................................................................ 240
MOD() Function ............................................................................................................................................................................. 240
Better Formula for Time Differences ......................................................................................................................... 241
Summing Every nth Row ................................................................................................................................................ 241
Determining Whether a Year Is a Leap Year ......................................................................................................... 242
Creating Ledger Shading ................................................................................................................................................. 242
Generating Random Numbers............................................................................................................................................... 244
RAND() Function ................................................................................................................................................................. 244
RANDBETWEEN() Function............................................................................................................................................ 246
From Here ................................................................................................................................................................................. 247
12 Working with Statistical Functions ................................................................................................................ 249
Understanding Descriptive Statistics ................................................................................................................................ 249
Counting Items with the COUNT() Function ................................................................................................................ 252
Calculating Averages ................................................................................................................................................................... 253
AVERAGE() Function ........................................................................................................................................................ 253
MEDIAN() Function ........................................................................................................................................................... 253
MODE() Function ................................................................................................................................................................. 254
Calculating the Weighted Mean ................................................................................................................................. 254
Calculating Extreme Values .................................................................................................................................................... 256
MAX() and MIN() Functions ........................................................................................................................................ 256
LARGE() and SMALL() Functions ............................................................................................................................ 256
Performing Calculations on the Top k Values ...................................................................................................... 258
Performing Calculations on the Bottom k Values ............................................................................................. 258
Calculating Measures of Variation ...................................................................................................................................... 258
Calculating the Range ....................................................................................................................................................... 258
Calculating the Variance .................................................................................................................................................. 259
Calculating the Standard Deviation ........................................................................................................................... 260
Working with Frequency Distributions ............................................................................................................................. 261
FREQUENCY() Function .................................................................................................................................................. 262
Understanding the Normal Distribution and the NORMDIST() Function ............................................. 263
Shape of the Curve I: The SKEW() Function ......................................................................................................... 264
Shape of the Curve II: The KURT() Function ....................................................................................................... 265
Using the Analysis ToolPak Statistical Tools .................................................................................................................. 267
Using the Descriptive Statistics Tool .......................................................................................................................... 270
Determining the Correlation Between Data ......................................................................................................... 272
Working with Histograms ............................................................................................................................................... 274
Using the Random Number Generation Tool ....................................................................................................... 276
Working with Rank and Percentile ............................................................................................................................. 279
From Here ................................................................................................................................................................................. 281
IIIBUILDING BUSINESS MODELS
13 Analyzing Data with Tables ................................................................................................................................... 283
Converting a Range to a Table .............................................................................................................................................. 285
Basic Table Operations ............................................................................................................................................................... 286
Sorting a Table ................................................................................................................................................................................ 287
Performing a More Complex Sort ............................................................................................................................... 288
Sorting a Table in Natural Order ................................................................................................................................. 289
Sorting on Part of a Field ................................................................................................................................................. 290
Sorting Without Articles ................................................................................................................................................... 291
Filtering Table Data...................................................................................................................................................................... 292
Using Filter Lists to Filter a Table ................................................................................................................................ 292
Using Complex Criteria to Filter a Table .................................................................................................................. 296
Entering Computed Criteria ........................................................................................................................................... 299
Copying Filtered Data to a Different Range .......................................................................................................... 300
Referencing Tables in Formulas ........................................................................................................................................... 301
Using Table Specifiers ........................................................................................................................................................ 301
Entering Table Formulas .................................................................................................................................................. 303
Excel's Table Functions .............................................................................................................................................................. 305
About Table Functions ...................................................................................................................................................... 305
Table Functions That Don't Require a Criteria Range ...................................................................................... 305
Table Functions That Accept Multiple Criteria ..................................................................................................... 307
Table Functions That Require a Criteria Range ................................................................................................... 309
Case Study: Applying Statistical Table Functions to a Defects Database ..................................................... 313
From Here ................................................................................................................................................................................. 314
14 Analyzing Data with PivotTables..................................................................................................................... 315
What Are PivotTables? ............................................................................................................................................................... 315
How PivotTables Work ...................................................................................................................................................... 316
PivotTable Terms .................................................................................................................................................................. 317
Building PivotTables .................................................................................................................................................................... 318
Building a PivotTable from a Table or Range ....................................................................................................... 319
Building a PivotTable from an External Database ............................................................................................. 322
Working with and Customizing a PivotTable ....................................................................................................... 323
Working with PivotTable Subtotals ................................................................................................................................... 323
Hiding PivotTable Grand Totals ................................................................................................................................... 324
Hiding PivotTable Subtotals .......................................................................................................................................... 324
Customizing the Subtotal Calculation ...................................................................................................................... 324
Changing the Data Field Summary Calculation ........................................................................................................... 325
Using a Difference Summary Calculation................................................................................................................ 326
Using a Percentage Summary Calculation ............................................................................................................. 327
Using a Running Total Summary Calculation ....................................................................................................... 330
Using an Index Summary Calculation ....................................................................................................................... 331
Creating Custom PivotTable Calculations ........................................................................................................................ 332
Creating a Calculated Field ............................................................................................................................................. 334
Creating a Calculated Item ............................................................................................................................................. 335
Case Study: Budgeting with Calculated Items.............................................................................................................. 337
Using PivotTable Results in a Worksheet Formula .................................................................................................... 339
From Here ................................................................................................................................................................................. 340
15 Using Excel's Business-Modeling Tools ....................................................................................................... 341
Using What-If Analysis .............................................................................................................................................................. 341
Setting Up a One-Input Data Table ............................................................................................................................ 342
Adding More Formulas to the Input Table ............................................................................................................. 344
Setting Up a Two-Input Table ...................................................................................................................................... 345
Editing a Data Table ............................................................................................................................................................ 346
Working with Goal Seek............................................................................................................................................................ 347
How Does Goal Seek Work? ........................................................................................................................................... 347
Running Goal Seek .............................................................................................................................................................. 347
Optimizing Product Margin ............................................................................................................................................ 349
Note About Goal Seek's Approximations ................................................................................................................ 351
Performing a Break-Even Analysis .............................................................................................................................. 352
Solving Algebraic Equations .......................................................................................................................................... 352
Working with Scenarios ............................................................................................................................................................ 354
Understanding Scenarios ................................................................................................................................................. 354
Setting Up Your Worksheet for Scenarios .............................................................................................................. 355
Adding a Scenario ................................................................................................................................................................ 355
Displaying a Scenario ......................................................................................................................................................... 357
Editing a Scenario ................................................................................................................................................................ 358
Merging Scenarios ............................................................................................................................................................... 358
Generating a Summary Report .................................................................................................................................... 359
Deleting a Scenario ............................................................................................................................................................. 360
From Here ................................................................................................................................................................................. 361
16 Using Regression to Track Trends and Make Forecasts ............................................................ 363
Setting Up and Performing a Find ...................................................................................................................................... 363
Choosing a Regression Method ............................................................................................................................................ 364
Using Simple Regression on Linear Data ......................................................................................................................... 364
Analyzing Trends Using Best-Fit Lines ..................................................................................................................... 365
Making Forecasts .................................................................................................................................................................. 372
Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model ..................................................... 377
Using Simple Regression on Nonlinear Data ................................................................................................................. 384
Working with an Exponential Trend .......................................................................................................................... 384
Working with a Logarithmic Trend ............................................................................................................................ 388
Working with a Power Trend ........................................................................................................................................ 391
Using Polynomial Regression Analysis ..................................................................................................................... 394
Using Multiple Regression Analysis ................................................................................................................................... 396
From Here ................................................................................................................................................................................. 399
17 Solving Complex Problems with Solver ..................................................................................................... 401
Some Background on Solver .................................................................................................................................................. 401
The Advantages of Solver ................................................................................................................................................ 402
When Do You Use Solver? ............................................................................................................................................... 402
Loading Solver ................................................................................................................................................................................ 403
Using Solver ...................................................................................................................................................................................... 403
Adding Constraints ....................................................................................................................................................................... 406
Saving a Solution as a Scenario ............................................................................................................................................ 408
Setting Other Solver Options .................................................................................................................................................. 408
Selecting the Method Solver Uses ............................................................................................................................... 409
Controlling How Solver Works...................................................................................................................................... 409
Working with Solver Models ......................................................................................................................................... 412
Making Sense of Solver's Messages ................................................................................................................................... 413
Case Study: Solving the Transportation Problem ....................................................................................................... 415
Displaying Solver's Reports ..................................................................................................................................................... 417
The Answer Report .............................................................................................................................................................. 417
The Sensitivity Report ........................................................................................................................................................ 418
The Limits Report ................................................................................................................................................................. 420
From Here ................................................................................................................................................................................. 420
IV BUILDING FINANCIAL FORMULAS
18 Building Loan Formulas ............................................................................................................................................. 421
Understanding the Time Value of Money ....................................................................................................................... 421
Calculating the Loan Payment .............................................................................................................................................. 422
Loan Payment Analysis ..................................................................................................................................................... 423
Working with a Balloon Loan ....................................................................................................................................... 424
Calculating Interest Costs, Part 1 ................................................................................................................................ 424
Calculating the Principal and Interest ...................................................................................................................... 425
Calculating Interest Costs, Part 2 ................................................................................................................................ 426
Calculating Cumulative Principal and Interest .................................................................................................... 426
Building a Loan Amortization Schedule ........................................................................................................................... 428
Building a Fixed-Rate Amortization Schedule ..................................................................................................... 428
Building a Dynamic Amortization Schedule ......................................................................................................... 429
Calculating the Term of the Loan ........................................................................................................................................ 431
Calculating the Interest Rate Required for a Loan ..................................................................................................... 433
Calculating How Much You Can Borrow ........................................................................................................................... 434
Case Study: Working with Mortgages ............................................................................................................................... 435
From Here ................................................................................................................................................................................. 438
19 Building Investment Formulas ........................................................................................................................... 439
Working with Interest Rates .................................................................................................................................................. 439
Understanding Compound Interest ........................................................................................................................... 440
Nominal Versus Effective Interest ............................................................................................................................... 440
Converting Between the Nominal Rate and the Effective Rate ................................................................ 441
Calculating the Future Value .................................................................................................................................................. 442
The Future Value of a Lump Sum ............................................................................................................................... 442
The Future Value of a Series of Deposits ................................................................................................................. 443
The Future Value of a Lump Sum Plus Deposits ................................................................................................. 444
Working Toward an Investment Goal ............................................................................................................................... 444
Calculating the Required Interest Rate .................................................................................................................... 444
Calculating the Required Number of Periods ....................................................................................................... 445
Calculating the Required Regular Deposit ............................................................................................................. 446
Calculating the Required Initial Deposit .................................................................................................................. 447
Calculating the Future Value with Varying Interest Rates ........................................................................... 448
Case Study: Building an Investment Schedule ............................................................................................................. 449
From Here ................................................................................................................................................................................. 451
20 Building Discount Formulas .................................................................................................................................. 453
Calculating the Present Value ............................................................................................................................................... 454
Taking Inflation into Account ....................................................................................................................................... 454
Calculating Present Value Using PV() ..................................................................................................................... 455
Income Investing Versus Purchasing a Rental Property ................................................................................ 456
Buying Versus Leasing ...................................................................................................................................................... 457
Discounting Cash Flows ............................................................................................................................................................. 458
Calculating the Net Present Value .............................................................................................................................. 459
Calculating Net Present Value Using NPV() ........................................................................................................ 460
Net Present Value with Varying Cash Flows ......................................................................................................... 462
Net Present Value with Nonperiodic Cash Flows ............................................................................................... 463
Calculating the Payback Period ............................................................................................................................................ 464
Simple Undiscounted Payback Period ...................................................................................................................... 464
Exact Undiscounted Payback Point ............................................................................................................................ 465
Calculating the Internal Rate of Return ................................................................................................................... 466
Using the IRR() Function.............................................................................................................................................. 467
Calculating the Internal Rate of Return for Nonperiodic Cash Flows ..................................................... 468
Calculating Multiple Internal Rates of Return ..................................................................................................... 468
Case Study: Publishing a Book .............................................................................................................................................. 469
From Here ................................................................................................................................................................................. 473
TOC, 9780789743060, 4/13/10