Formulas and Functions

Paul McFedries, president of Logophilia Limited, is a full-time technical writer, passionate computer tinkerer, and Windows expert. He has authored 60+ computer books that have sold 3,000,000+ copies. His recent titles include Microsoft Windows 7 Unleashed, Microsoft Home Server Unleashed, Tweak It and Freak It: A Killer Guide to Making Windows Run Your Way, Formulas and Functions with Microsoft Excel 2007; andTricks of the Microsoft Office 2007 Gurus. He is also proprietor of Word Spy (www.wordspy.com) a website that tracks new words and phrases as they enter the English language.     Editor: Bill Jelen has made more than 75 guest appearances on TechTV with Leo Laporte and was voted guest of the year on the Computer America radio show. He has produced more than 600 episodes of his daily video Learn Excel podcast. Before founding MrExcel.com in 1998, Jelen spent 12 years “in the trenches” as a financial analyst for the accounting, finance, marketing, and operations departments of a publicly held company. Since then, his company has automated Excel reports for hundreds of clients around the world. The website answers more than 30,000 questions a year–for free–for readers all over the world.

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