Formulas and Functions: Microsoft Excel 2010

MASTER CORE EXCEL 2010 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS!   Excel expert Paul McFedries shows how to use Excel 2010’s core features to solve problems and get the answers you need! Using real-world examples, McFedries helps you get the absolute most out of breakthrough Excel 2010 improvements–from Sparklines to the brand-new version of Solver. Along the way, you’ll discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions… insider insights… even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more. Create more powerful formulas Use conditional formatting to instantly reveal anomalies, problems, or opportunities Analyze your data with standard tables and PivotTables Use complex criteria to filter data in lists Understand correlations between data Perform sophisticated what-if analyses Use regression to track trends and make forecasts Build loan, investment, and discount formulas Troubleshoot problems with formulas, ranges, and functions About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excelskills, and presents focused tasks and examples for performing them rapidly and effectively. Selectedby Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will: Dramatically increase your productivity—saving you 50 hours a year, or more Present proven, creative strategies for solving real-world problems Show you how to get great results, no matter how much data you have Help you avoid critical mistakes that even experienced users make

Table of Contents:
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

About the Author :
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; and Tricks 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.