Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 15

Naming Cells—For Meaningful Decision Making and Modeling

When you use Excel for modeling and decision making, one of the challenges you face is the ability to follow the logic or understand the meaning of the functions and the model.

The following examples illustrate this problem.

In Figure 15.1 you can see an example of a car loan. The example is in the Car Loan sheet of the Excel file Chapter 15. If you read the formulas and functions on the model, it is not immediately clear what was meant when the functions or the formulas were created. The formulas are revealed on the right. I used CTRL+’ or CTRL+∼ to show the formulas.

FIGURE 15.1 Functions and Formulas without Names

This is the example: You are purchasing a car for $22,000. You are required to pay $4,000 as a down payment. The annual interest rate is 8 percent and the loan duration is three years. The payments are made at the end of period. Some loans require a beginning of the period payment, such as a mortgage, and others, as in this example, require it at the end. The payments are monthly payments.

The loan formula in cell B4 is clear. It is the price minus the down payment. To calculate the Payment amount, you have to use the Excel financial function called PMT, which returns the periodic payment for an annuity. (As the Excel menu defines it, PMT “calculates the payment for a loan based on a constant interest ...