Problem 6.35

Loan Amortization

Trevor Diaz is looking to purchase a Mercedes Benz SL600 Roadster which has an invoice price of $121,737 and a total cost of $129,482. Trevor plans to put down $20,000 and will pay the rest by taking on a 5.75 percent five-year loan from Bank of America. What is the monthly payment on this auto loan? Prepare an amortization table using Excel.

a. What is the monthly payment on this auto loan?

Hint: Use the present value of an annuity equation to solve for the monthly payment and then use the PMT financial function to solve: PMT(rate,nper,pv,fv,type). The present value of the annuity is the total amount borrowed. Make sure that all cells are properly formatted.

Cost of new car:

Down payment:

Loan amount:

Interest rate on loan:

Term of loan (years):

Frequency of payment:

Monthly payment on loan:

Monthly payment on loan:

b. Prepare an amortization table using Excel.

Hint: Insert the proper equation in each column and copy down the appropriate number of periods. Calculate total interest, principal, payments, and ending balance using the template below.

Loan amount:

Interest rate on loan:

Term of loan:

Frequency of payment:

Payment#	Payment	Interest	Principle	Balance
				$0.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Totals: