Microsoft Office Tutorials and References
In Depth Information
Chapter 15: Naming Cells—For Meaningful Decision Making and Modeling
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
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
calculates the payment for a loan based on a constant interest
) 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