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 Amount $12000 00 5 Rate 0 094 6 Compound Period 12 7 Time 4 8 9 Rate per Period B5 B6 10 Total No of Payments B6 B7 11 PMT pmt(B9,B10, B4) 12 Full Cost B11 B10 13 Total Interest B12 B4 14 Amortization Schedule 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) Discussion In a textbox answer the questions posed in the sample problem using the worksheet developed in (a) (b) Write the answers as if you were explaining the results to your boss Print the range A1 H65 of the worksheet

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 12, 2024

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

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 Amount	$12000.00
5	Rate	0.094
6	Compound Period	12
7	Time	4
8
9	Rate per. Period	=B5/B6
10	Total No. of Payments	=B6*B7
11	PMT	=pmt(B9,B10,-B4)
12	Full Cost	=B11*B10
13	Total Interest	=B12-B4
14	Amortization Schedule
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).

Discussion:In a textbox answer the questions posed in the sample problem using the worksheet developed in (a)-(b). Write the answers as if you were explaining the results to your boss. Print the range A1..H65 of the worksheet.