AutoSave W H FC ... 9e_prob 8-59 stu-2-1-2.xls - Compatibility Mode Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Comments Share Arial 10 AA g Wrap Text v General AP- O. V v Paste BIUV A Merge & Center v ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data 021 X V fx A B C D E F G H K M N O P Q R Student instructions: This worksheet is for problem 8-59. Enter formulas in the blanks where indicated to complete the calculations needed for for the loan amortization table OUT A WON - PROBLEM 8-59 LOAN AMORTIZATION SCHEDULE 10 Given: Amount Borrowed: $22,000 Interest rate: 6.0% annually, or 0.5000% monthly NOTE: Cell D21 references this amount to find the monthly interest paid Term: years, or months Remember that Excel financial functions are built for annual interest rates and payments so remember to adjust! a. Required Payments: (hint: found using Equation 8-4a) =-PMT(E12,E13,B24) OR =-PMT(C12/12,C13*12,B24) Remember the negative sign to flip the finance value positive! 'Cause Excel... 18 b. Loan amortization table: Pay attention to what happens to the payments of interest and payments of principal over time! What are you mostly paying in the beginning versus the end? Why does paying extra early make such a difference? Col. 1 Col. 2 Col. 3 Col. 4 Col. 5 Answer: TVM = Time Value of Money! Which we'll get to as a specific chapter soon! Col. 1 x E12 Col. 2 - Col. 3 Col. 1 - Col. 4 Beginning Total Payment of Payment of Ending 22 Month Balance Payment Interest Principal Balance $22,000.00 $0.00 $0.00 $0.00 $0.00 2 8 6 0 VO U A W N - $0.00 $0.00 $0.00 $0.00 0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.0 $0.00 $0.00 $0.00 20 $0.00 $0.00 $0.00 23 0.00 24 $0.00 48 25 $0.00 8-59 Select destination and press ENTER or choose Paste 100%