Lab #4, 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...

looking for assignment attached to be done via excel


Lab #4, 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 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. Problem 2. Suppose you obtain an amortized loan of $18,000 for 3 years at an annual rate of 9% compounded monthly. Copy the loan template from Problem 1 to a new sheet and make suitable changes to answer the following questions. (a.) Find the monthly payments and the total interest paid on the loan. State your conclusions using English sentences and document the answers by printing an appropriate portion of a worksheet. (b.) Use the Amortization table to determine the total interest paid during each year of the loan - that is the interest paid in Year 1,in Year 2, and in Year 3. (c.) Suppose your first loan payment is $1013.08 instead of the value found in Part a. What is your balance after this payment and how much interest does it save? (d.) Suppose you negotiate a ¼ percent reduction in the loan rate. How much would you save on each payment? Problem 3. (Simple Interest Loan) Suppose the 3-year loan of $18,000 at 9% annual rate is a simple interest loan payable in 36 equal installments. (a.) Find the monthly payment for this loan. Explain how you determined the value. (b.) How much total interest is saved on an $18,000 loan using the amortized loan plan in Problem 2 instead of the simple interest plan? In the next problem only use the first 10 lines of the template in Problem 1, not the Amortization Table. Problem 4. A bank's interest rate for a 3-year new car loan is 12%. Assume you have $3000 in cash for a down payment. (a.) How expensive a car can you purchase if you can afford monthly payments of $500? Report your answer to the nearest dollar including the down payment. In your answer describe the strategy used to find the answer. Use Excel's Solver to find the loan amount. (b.) If you can afford monthly payments of $560 (instead of $500), how much could you borrow?