Page 96 - Excel Data Analysis

05 537547 Ch04.qxd 3/4/03 11:52 AM Page 82

EXCEL DATA ANALYSIS

CREATE AN AMORTIZATION

TABLE FOR A LOAN

common data analysis task is to analyze the terms Function(interest rate, payment period,
under which a company borrows money to acquire total periods, loan total)
A assets. You can use Excel to quickly figure out the The data inside the parentheses is described to Excel
monthly payment amount and to set up an amortization through special arguments; for example, the payment
table so that you can quickly analyze the amount of interest period is per. The PMT function differs from the PPMT and
and principal paid over the life of a loan. After you create IPMT functions in that it does not use the per argument.
the table, you can quickly change the analysis to reflect a
different loan or interest rate. When entering the formulas for the functions, remember to
use absolute cell references for the cells in which you have
Excel provides several financial functions for calculating loan entered the total loan amount, interest rate, and total
information. The PMT function calculates the payment number of payment periods, but a relative entry for the
required for each period to repay the loan in a timely manner. specific payment period. Appendix D has more information
The IPMT function determines the amount of interest paid for on absolute versus relative cell references. Also remember
each period. The PPMT function finds the amount of principal that you should make the interest rate in the formula the
paid each period. Appendix B has more information about the interest rate charged for each payment period. For example,
syntax of the PMT, PPMT, and IPMT functions, but the basic if you have a monthly payment, you calculate the period with
order for the PPMT and IPMT functions is:
a formula dividing the cell with the annual interest rate by 12.
CREATE AN AMORTIZATION TABLE FOR A LOAN

⁄ Specify your initial loan ‹ In the parentheses, type › Create a table with Á In the interest cell, type
amount, annual interest rate, in order the cell reference the desired amortization =-IPMT(rate, per, nper, pv),
and number of periods. for the rate divided by the information, including substituting with your rate,
appropriate annualizing columns for period, balance, per, nper, and pv
■ The loan amount is your number, the cell for the interest, principal payment, references.
pv value, and the number of nper value, and the cell and remaining balance.
periods is your nper value. Note: Use absolute cell references
for the pv value.
ˇ Set the initial Balance for the rate, pv, and nper
¤ In the Payment amount ■ Excel calculates the value equal to the pv cell values. See Appendix D for more
cell, type -=PMT().
payment amount. reference. on absolute cell references.
82