AutoSave OFF Home Insert v Chapter 12 Excel Template rev221 Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 ' ' Wrap Text Currency v v [A v Poste B I U v v Merge & Center $%9 00 Cell Conditional Format Formatting as Table Styles Insert Delete Format v Sort & Filter Find & Select Analyze Data C27 : x fx 4 B C D E F G H . K L M N 0 P Q R S T U 1 Requirement 1 2 Complete the data table. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values. 3 4 DATA 5 Loan Amount 6 Interest Rate 7 Periods 8 9 10 11 12 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. 13 a 14 b. Round values to two decimal places. 15 16 Calculate the interest expense in the fifth year as the loan payment minus the loan balance at the beginning of the fifth year. Use absolute cell references and relative cell references in formulas. 17 Use absolute cell references only to cells C5 and C19 for interest expense and payment calculations. 18 19 Payment fusing PV table) 20 Present Value of an Ordinary Annuity of $1 Period Beginning Principal 21 Balance Payment Interest Expense Total Payment Ending Balance 6% 8% 10% 22 1 0.9434 09250 23 1 2 1.8334 1.7833 0.9091 1.7355 24 25 2 3 3 2.6730 2.5771 2.4869 4 3.4651 3.3121 3.1699 26 4 5 4.2124 3.9927 3.7908 27 5 28 Total 29 30 31 32 Use the effective interest amortization method. 33 34 Requirement 3 Using the Excel PMT function, calculate the payment amount and complete the amortization schedule. 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) The PMT function calculates a payment amount that results in a negative number. 35 Reverse this to a positive number for calculations in the amortization schedule. 36 b. Round values to two decimal places. 37 38 C Use absolute cell references and relative cell references in formules 39 Calculate the interest expense in the fifth year as the loan payment minus the loan balance at the beginning of the fifth year. Use absolute cell references only to cells C5 and C39 for interest expense and payment calculations. an Instructions Ready ENTERANSWERS1 ENTERANSWERS2 + 100%