P14-46 Using Excel for long-term notes payable amortization schedule Patrick's Delivery Services is buying a van to help with deliveries. The cost of the vehicle is $35,000, the interest rate is 6%, and the loan is for three years. The van is to be repaid in three equal installment payments. Payments are due at the end of each year. 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 the third year as the loan payment minus the loan balance at the beginning of the third year. Use absolute cell references and relative cell references in formulas. C. 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 schedul 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. Excel Skills 1. Formulas using both absolute and relative cell references. 2. PMT function Excel Hints The PMT function uses the interest rate, the number of periods, and the loan amount (in that order). To calculate the payment amount for a loan of $3,000 at 4% for 5 years, the formula would be =PMT(4%, 5, 3000) ($673.88) Requirement 1 Complete the data table. DATA Loan Amount Interest Rate Periods 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. Use absolute cell references and relative cell references in formulas. C. Payment (using PV table) Period Beginning Balance Principal Payment Interest Expense Total Payment Ending Balance 0 1 2 3 4 Total 5 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. Round values to two decimal places. b. Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year. Present Value of an Ordinary Annuity of $1 6% 8% 1 0.9259 0.9434 1.8334 2 1.7833 3 2.673 2.5771 3.4651 3.3121 4.2124 3.9927 10% 0.9091 1.7355 2.4869 3.1699 3.7908 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. Round values to two decimal places. b. Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year. Use absolute cell references and relative cell references in formulas. C. Payment (using PMT function) Period Beginning Balance Principal Payment Interest Expense Total Payment Ending Balance 0 1 2 3 Total 2. The default setting in Excel includes three sheets. To add additional sheets, select "Insert" in the menu bar and click on "Worksheet." Please note there is a difference between assigning one sheet per requirement and one file per requirement. If you assign one file per requirement, a problem with five requirements must be saved five times. This is very time consuming. Make sure to assign one sheet per requirement to avoid this lengthy process. 3. Use the formula function of "Excel" to compute an answer. To enter a formula into a cell, place your cursor in that cell and press the equal (=) sign on your keyboard. This sign is located on the top right segment of the keyboard. The equal sign tells the computer that you are entering a formula. You may then add (+), subtract (-), multiply (*), or divide (/) depending on the calculation that needs to be completed. For instance, if you want to add E10 to E11, enter =E10+E11. Once you hit the enter key on your keyboard, the two cells will be added. To subtract, enter =E10-E11 and press the enter key. Alternatively, you may wish to use AutoSum function (located on the toolbar) to add up a range of cells. For example, if you want to add two or more cells, place your cursor in the cell that the total needs to appear and click on the AutoSum symbol. A symbol =SUM() will appear. You may then specify the range of cells that needs to be calculated and press the enter key. If you don't use formulas for all the math functions in an excel assignment, there