Loan Payments And Amortization Schedule Worksheet

ADVERTISEMENT

MATH 105 Lab -- Loan payments and amortization schedules
The goal of this lab is to build and use a template for calculating amortized loan payments and to
construct an amortization schedule for a loan. You also compare an amortized loan with a simple
interest loan.
Problem 1. Sample amortization loan situation:
Suppose you obtain a 4-year car loan for $12,000 at 9.4% annual interest. What
are your monthly payments? How much total interest do you pay? How much
interest do you pay the first year?
The answers to the questions above can be obtained from the worksheet constructed in steps (a)
and (b) below.
a. Build the worksheet indicated below. Notice that it has three sections - one for assumptions,
one for calculations, and an amortization table.
A
B
C
D
E
F
1 Problem 1
2
3
4 Loan Amt
12000
5 Rate/yr
0.094
6 #Pmt/yr
12
7 Term(yrs)
4
8
9 Rate/period =B5/B6
10 Tot.No.Pmt
=B6*B7
11 Pmt
=pmt(B9,B10,-B4)
12 Full cost
=B11*B10
13 Tot interest
=B12-B4
14 -------------------------------------------------------------------------------------------------
15
Interest
Balance
Unpaid
Interest
16 Pmt #
Pmt
in Period
Reduction Balance
cum
17
=B4
18 =A17+1
=$B$11 =E17*$B$9 =B18-C18 =E17-D18 =F17+C18
19
b. Copy the range A18..F18 to the range A19..A65. Note the trick in cells A18 and cells F18 to
obtain the first term. The table in cells A15.. F65 is called an Amortization Table or schedule.
Notice that the PMT function computes the payment to be made during each period and that the
total interest is obtained by subtracting the loan amount from the Full cost (total amount paid).

ADVERTISEMENT

00 votes

Related Articles

Top 20 Happy Easter Cards And Gift Tags To Download For Free
Top Ten Easter Arts And Crafts Activities For Your Kids
Free Easter Coloring Pages And Easter Printables For Your Kids

Related forms

Loan Amortization Schedule Loan Amortization Schedule Financial
Loan Amortization Schedule Loan Amortization Schedule Financial
Schedule B (100s) - S Corporation Depreciation And Amortization - 1198 Schedule B (100s) - S Corporation Depreciation And Amortization - 1198 Financial
Schedule B (100s) - California S Corporation Depreciation And Amortization - 2015 Schedule B (100s) - California S Corporation Depreciation And Amortization - 2015 Financial
Schedule B (100s) - California S Corporation Depreciation And Amortization - 2014 Schedule B (100s) - California S Corporation Depreciation And Amortization - 2014 Financial
Schedule B Corporation And Partnership - Recapture Of Investment Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings Schedule B Corporation And Partnership - Recapture Of Investment Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings Financial
Schedule B Corporation And Partnership - Recapture Of Investment Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings Schedule B Corporation And Partnership - Recapture Of Investment Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings Financial
Schedule B Corporation And Partnership - Recapture Of Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings - 2011 Schedule B Corporation And Partnership - Recapture Of Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings - 2011 Financial
Schedule B Individual - Recapture Of Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings - 2006 Schedule B Individual - Recapture Of Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings - 2006 Financial
Schedule B Individual - Recapture Of Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings - 2009 Schedule B Individual - Recapture Of Credit Claimed In Excess, Tax Credits, And Other Payments And Withholdings - 2009 Financial

Related Categories

Parent category: Education
Download
Go
Page of 2