Page 196 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Building a Version of the Loan Calculation
Workbook

The Calculate_Table macro relies upon several features in Excel to work, such as named
ranges (discussed later in this chapter), form controls, and command buttons. To build the
workbook yourself, follow these steps.
1 Open a blank workbook by clicking the New button on the Standard toolbar or select­
ing Blank workbook from the New Workbook Task Pane.
2 In column A, type the following descriptions, one per line. You can use Figure 8-2 as
a guide:
■ Bank Name
Chapter 8
■ Principal
■ Future Value
■ Annual Interest Rate
■ Years of Loan
■ Amount of Payment
■ Effective Rate
■ Number of Payments

Figure 8-2. The Loan Calculator requires a number of specific inputs—here are
those descriptions.
3 In column B, assign names to the cells alongside the description. Match the cell name
in column B to the description in column A: Name, Principal, Future_Value, APR,
Years, Payment. Skip the cell for Effective Rate, and name the last cell Payments.
4 Select the field cell for Amount of Payment, and type the following formula:
=PMT(APR/12,Payments,Principal, Future_Value)

170