Corporation Finance Lab- Present Value, Effective rates and Loan Amortization Pl
ID: 2818546 • Letter: C
Question
Corporation Finance Lab- Present Value, Effective rates and Loan Amortization Please try the following problems: 1. ABC Company sells heavy equipment to its customers. The price of a new backhoe is S150,000. Bob the builder would like to buy the backhoe, but he cannot afford the cash purchase price. He would like to buy the tractor by putting down S30,000 and paying the balance with an installment note paid with equal annual installments over the next 4 years. Interest rates are 9%. a. Use Excel to determine the payment. b. Do you see that the PV of the note the amount borrowed - PV of 4 payments) c. What is the sales price of the backhoe under the financing arrangement? d. What is the total amount Bob will pay on the note over 4 years. What is the difference between the value of the note and the total to be repaid? Use Excel to create a 4 year amortization schedule. I recommend this format: e. Outstanding Balance Interest Principal Date expense Payment Paid Compute the total interest on the note and compare it to the amount you computed in d. f. Banks often advertise the periodic interest rate. They must disclose the annual rate of interest (simply taking that rate * the number of compounding periods) as well as the effective annual rate of interest. You can use Excel to determine the effective annual rate. The formula is: EAR [I+(APR rate/m)]-1 (where m the compounding periods) 2. The Excel formula (requires analytical tool add-in) Nominal - enter the nominal rate Npery- enter the compounding periods per year EFFECT will give you the effective annual rate Alice's Auto Shop charges an annual interest rate of 8.2% on its loans. The loan payments are made each month . What is the EAR (effective annual rate) on the loan?Explanation / Answer
1 a) Loan amount = 150,000 - 30,000 = 120,000
Payment can be calculated using PMT function in excel
Payment = PMT(rate = 9%, nper = 4, pv = -120,000, fv = 0, 0) = $37,040.24 is the annual payment.
b) Yes. PV of 4 payments of $37,040.24 would be equal to $120,000 with 9% discount rate.
c) Sales Price = $150,000
d) Total Amount Paid on note = 37,040.24 x 4 = $148,160.96
Difference = 148,160.96 - 120,000 = $28,160.96
2) EAR = (1 + APR/n)^n - 1 = (1 + 8.2%/12)^12 - 1 = 8.52%
Year Interest Payment Principal Outstanding 0 $120,000.00 1 $10,800.00 $37,040.24 $26,240.24 $93,759.76 2 $8,438.38 $37,040.24 $28,601.86 $65,157.90 3 $5,864.21 $37,040.24 $31,176.03 $33,981.87 4 $3,058.37 $37,040.24 $33,981.87 $0.00