19 You are ready to insert formulas and functions in the loan amortization table. In cell C7, enter a reference to the Payment that is stored in cell D2 Use mixed reference so that the row number does not change. Copy the formula from cell C7 to the range C8.C42. 20 The next column is designed to display the interest portion of each payment In cell D7, insert the IPMT function to calculate the interest for the first payment. Use mixed references for cells in the Basic Output Area and make sure the result is a positive value Copy the function from cell D7 to the range D8 D42. 21 Next, you are ready to calculate the portion of the monthly payment that goes toward the principal In cell E7, insert the PPMT function to calculate the principal portion of the first payment Use mixed references for cells in the Basic Output Area and make sure the result is a positive value. Copy the function from cell E7 to the range E8 E42. 22 23 Now you are ready to calculate the balance of the loan after each payment. In cell F7, enter a formula to calculate the ending balance after subtracting the Principal Repayment from the Beginning Balance. Copy the formula from cell F7 to the range F8 F42 In cell B8, enter a formula that refers to the ending balance for the previous month in cell F7. Copy the relative formula to the range B9 B42 Now you want to calculate the cumulative interest after each payment In cell H7 insert the CUMIPMT function. Use mixed references for cells in the Basic Output Area and the Start_period argument. Make sure the result is a positive value. Copy the function from cell H7 to the range H8 H42 Finally, you want to calculate the cumulative principal after each payment In cell 17, insert the CUMPRINC function Use mixed references for cells in the Basic Output Area and the Start_period argument. Make sure the result is a positive value. Copy the function from cell 17 to the range 18142 24 Name Insert Function 2 AutoSum Recently Financial logical Text Date & Lookup & Math More Used Time Reference Trig Functions Function Library use in Formula Manager Et Create from Selection Defined Names F2 A B C G H 3 12 Payments APR Input Area 2 Years 3 Pmts per Year 4 5 Payment Number 7 1 D Basic Output Areas 750.00 Loan 4.75% Periodic Rate of Payments 0.396% 36 Beginning Balance $ Monthly Payment Principal Interest Paid Repayment Ending Balance Cumulative cumulative Interest Principal 2 3 9 4 10 11 12 13 14 15 5 6 7 12 TU 10 11 12 20 21 16 15 mar 3 Finance . ER niorro e DOLL oc F 6 10 2 * NO $ od ? E a> 2 3 4 5 7 B Q E T Y C Tab W R