LEIFLI D E G H M N Requirements 1 Complete the data table. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values. 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. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values 3. Calculate the loan payment by dividing the loan amount by the appropriate present value factor 6. 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. Use absolute cell references C6 and C19 only to calculate interest expense and payment calculations. 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. Use absolute cell references C6 and C39 only to calculate interest expense and payment calculations. Excel Skills 1 Formulas using both absolute and relative cell references. 2 PMT function Requirement 1 Complete the data table. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values. 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. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values. Use the effective interest amortization method. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the instructions tab, you will be marked wrong) Calculate the loan payment by dividing the loan amount by the appropriate present value factor 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. Use absolute cell references only to cells C6 and C19 for interest expense and payment calculations. a. b. C. Payment (using Pvtable) Present Value of an ordinary Annuity of $1 Beginning Balance % Period Principal Payment Interest Expense Total Payment 10% Ending Balance 8% 1 0.9634 0.9259 090911 0 SWERS H 10% 1 0.01 1.7355 2A LI . Period Beginning Principal Interest Total 21 Ending Balance Payment Expense Payment Balance 6 22 2x 0 23 1 0.9434 09059 2 2 1.8334 1. 25 3 26730 2.57 4 7.4651 26 Total 32121 5 1.2124 27 2.9922 28 29. Requirement 3 Using the Excel PMT function, calculate the payment amount and complete the amortization schedule 30 Use the effective interest amortization method. 31 (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the instructions tab. you will be marked wrong) 32 a. The PMT function calculates a payment amount that results in a negative number 33 Reverse this to a positive number for calculations in the amortiration schedule. 346 Round values to two decimal places. 25 Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year 36 C Use absolute cell references and relative cell references in formulas 37 Use absolute cell references only to cells C and c39 for interest expense and payment calculations 38 39 Payment using PMT function) 40 Beginning Period Balance ENTERANSWERS Principal Payment Interest Expense Total Payment Endine Balance Using the Excel PMT function, calculate the payment amount and complete the amortization schedule H Use the effective interest amortization method. 1 (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the instructions tab, you will be marked wron) The PMT function calculates a payment amount that results in a negative number 3 Reverse this to a positive number for calculations in the amortization schedule. 54. 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. 36 C Use absolute cell references and relative cell references in formulas. 37 Use absolute cell references only to cells C6 and C39 for interest expense and payment calculations. 38 39 Payment (using PMT function) 40 Period Beginning Balance Principal Payment Interest Expense 41 Total Payment Ending Balance 42 0 43 44 1 2 3 45 46 Total 47
