UNM CS-150L Lab 7 Fall 2008
Excel: Annual Percentage Rate (APR), Loan Amortization Schedules, the PMT( )
function, Freeze Pane.
Due: Sunday November 2 at midnight.
In this lab you will be making an Excel workbook with three worksheets: “PMT”, “Amortization
Schedule” and “Modified Amortization Schedule”. The worksheets will compare various
scenarios of an auto loan and various ways of calculating the amortization schedule. The
information and calculations listed below must be included in your workbook. It is up to you to
organize, format, color, and highlight the information in a professional looking, easy to read
The Excel PMT(rate, nper, -pv) function calculates the periodic payment, P, on a loan by the
Where rate is the periodic interest rate (APR divided by the number of periods per year), nper is
the total number of periods during the term of the loan, and pv is the original amount financed.
For example, consider the following scenario: A five year loan is created on an original
principal of $5,000 at a fixed APR of 6.75% compounded monthly.
The values in rows 1 through 4 are given directly in the scenarios statement and are shown with a
green background. The values of pv, rate, and nper needed in the Excel PMT function are shown
with a blue background in column B. The periodic Interest Rate, rate, is the APR divided by the
number of periods per year, in this case 12. Notice that the periodic interest rate is displayed with
four decimal places. In finance, it is customary to display four decimal places of percentages less
than one. For percentages greater than one, generally two decimal places are used (as in the APR
The total number of periods of the loan, nper, is the number of periods per year times the number
of years of the loan: 12 months per year × 5 year