FHCE 3260 Lecture Notes - Lecture 4: Loan
Get access
Related Documents
Related Questions
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: |
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: |