Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Naming Cells—For Meaningful Decision Making and Modeling**

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 for-

mulas were created. The formulas are revealed on the right. I used CTRL

þ’

or

CTRL

to show the formulas.

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

þ
B

“

calculates the payment for a loan based on a constant interest

rate.

) Figure 15.1 illustrates the function. Note that for the Rate Argument, I used

B5/12 (the monthly interest rate divided by 12 months) and for the number of

periods, B6*12 (the number of years times 12), since I am calculating monthly

payments. Also I used

”

B4, minus the Loan (minus sign B4). The payment function

requires a minus sign for the loan so that the monthly payment will be positive; this is

FIGURE 15.1
Functions and Formulas without Names