C D E F G H I J K L M N 3 Long-term notes payable amortization schedule 5 Dana's Delivery Services is buying a van to help with deliveries. The cost of the vehicle is $42,000, the interest rate is 6%, and the loan is for three years. 6 The van is to be repaid in three equal installment payments. Payments are due at the end of each year. 8 Requirements 1. Complete the data table. 2. Using the present value of an ordinary annuity table, calculate the payment amount and complete the amortization schedule. Use the effective interest amortization method. a. Calculate the loan payment by dividing the loan amount by the appropriate present value factor. b. Round values to two decimal places. Calculate the interest expense in year 3 as the loan payment minus the loan balance at the beginning of the third year. c. Use absolute cell references and relative cell references in formulas. 3. Using the Excel PMT function, calculate the payment amount and complete the amortization schedule. Use the effective interest amortization method. a. The PMT function calculates a payment amount that results in a negative number. Reverse this to a positive number for calculations in the amortization schedule, b. Round values to two decimal places. Calculate the interest expense in year 3 as the loan payment minus the loan balance at the beginning of year 3. c. Use absolute cell references and relative cell references in formulas. Excel skills 1. Formulas using both absolute and relative cell references. 2. PMT function 29 Excel Hints 30 31 The PMT function uses the interest rate, the number of periods, and the loan amount (in that order). 32 To calculate the payment amount for a loan of $3,000 at 4% for 5 years, the formula would be EPMT(4%, 5, 3000) ($673.88) 96 Requirement 1 Complete the data table. 2 DATA Loan Amount Interest Rate Periods 11 12 13 14 15 Requirement 2 Using the present value of an ordinary annuity table, calculate the payment amount and complete the amortization schedule. Use the effective interest amortization method. a Calculate the loan payment by dividing the loan amount by the appropriate present value factor b. Round values to two decimal places. Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year. c. Use absolute cell references and relative cell references in formulas. 16 17 Payment (using PV table) Present Value of an Ordinary Annuity of $1 Ending Period Beginning Balance Principal Payment Interest Expense Total Payment Balance 0 1 896 0.9434 0 .9259 1.8334 1 .7833 2.673 2.5771 346511 3 31211 21243.9927 10% 0.9091 1.7355 2.4869 3.1699 3.7908 4 Total 5 4 30 32 Requirement 3 Using the Excel PMT function, calculate the payment amount and complete the amortization schedule, Use the effective interest amortization method. a. The PMT function calculates a payment amount that results in a negative number Reverse this to a positive number for calculations in the amortization schedule, b. Round values to two decimal places. Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year. c. Use absolute cell references and relative cell references in formulas 35 36 37 Payment (using PMT function) Ending Period Beginning Balance Principal Payment Interest Expense Total Payment Total
