Exercise 1: The example exercise is to work through a loan amortization example using Excel. Open Activity 3- Workbook. Go to the Exercise 1 worksheet. The example loan conditions are (enter these values under Loan Terms): Loan amount borrowed (principal or pv) S100,000 Loan interest (rate) is 7.5% Loan term (number of payments or nper) is 9 years Annual payments of principal and interest 1) Calculate the annual loan payment in cell C7 using the PMT function in Excel. The PMT function is in the formulas under the Financial menu option. In the PMT Menu box, the Rate is the interest rate, Nper is the number of payments or term, and PV is the principal amount borrowed (enter this as a negative value). FV and Type should be blank or you can enter 0. Use your mouse and use the cell reference to enter the required entries. 2) Write the needed formulas in the Loan Amortization Table given to calculate the interest payment and the principal payment for cach period payment. 1st, Interest Payment: Calculate the interest payment as follows: Interest payment = period interest rate * the outstanding loan balance. Start from Pmt Num 1 and use the loan balance of the previous period. You need to use absolute and relative cell addresses to accomplish this task! 2nd Principle Payment: When you make payments on a loan, part of your payment goes for interest on the loan and part goes to pay back the loan (principle). Subtract the Interest Payment from the Annual Loan payment (i.e., principal and interest that you calculated using PMT) to calculate the amount paid on principal. 3rd, Loan Balance: Subtract the principal payment from the previous period outstanding balance. In each period, the loan balance is whatever loan balance was left from the previous payment minus principle payment. (Note: Loan Balance in period 0 is the amount borrowed). 1 4h, copy and paste the formulas for the remaining & payments. 5th, enter formulas to sum the totals of Interest Payments and Principle Payments in your table. a) Calculate the total amount paid (Principal + Interest) using values in Term (cell C6) and Loan Payment (cell C7). b) Use the Excel IPMT formula to calculate the interest payment for payment 3 in D19. Again, enter PV as a negative value. c) Use the Excl PPMT formula to calculate the principal payment for payment 4 in D17. d) Check to see if the results of a, b and c are the same as calculated by your Loan Amortization Table. Autov CFF . Activity 3-Workbook 6 - Page Layout Formulas Home Insert Draw Data Review View Tell me Share Comments X + Calibri (Hedyl v 11 - Wa The General V > Mutsum A FIL Seri R : H+ + Fiber 2Y-0. PAH 114 S% To Merge Center 28 IN HET Rule Form Condition Format CHI Formatting Table Styles Find Select Data SNEY H19 x B C E F G H I L 1 Name(s): 2 3 Loan Terms 4 Borrowed $ 100,000.00 5 Rale 7.5% | (annual) 6 Term Lynas) 7 Annual Loan Payment $15,676.72 Connaioal and interest) 8 9 Pin Num 0 1 2 $ $ $ $ $ Loen Amortization Table Interest Principal Loan Payment Payment Balance $ 100.HD.CRD 7 500.00 $ 8,176.72 91,823.28 6,836.75 $ 8,789.97 $ 83,033.31 6,227.505 9,449.22 73,584.10 5519.81 $ 10.157.91 $ 63,426.19 4,751.96 $ 10.919.75s 52501.44 3,937.98 $ 11,728.73 $ 40,757.70 3,057.58 $ 12,619.14 $ 28,148.57 2.111.14 $ 13,565.57 $ 14,582.99 1,093.72 5 14,592.99 $ 41,090.44 $ 100,000.00 4 10 11 5 12 6 $ 13 14 15 16 7 $ s 8 $ 9 $ Totals a. Calculate the total amount paid (orincoal and interest) using the loan payment and number of payments $141.090.14 Total amounts paid equals to Annual payments number of years = 07 06 Alternatively, we can say that Total Payments equal the Totalinterest payments + Total Pricinciple payments - G16+ H16 Hath of these methods yield the same answer 17 18 Calculate the interest payment for period 3 $5.227.50 19 b. 20 21. 22 Calculate the principal payment for period 4 $10,157.41 23 24 25 Exer Solution for Exercise 1 Exercise 2 Ready HI HI + 130%
