16 You are ready to start completing the loan amortization table. 01 Display the Loan worksheet. In cell B8, type a reference formula to cell B1. The balance before the first payment is identical to the loan amount. Do not type the value; use the cell reference instead. In cell B9, subtract the principal from the beginning balance on the previous row. Copy the formula to the range B10:B19. 17 Now, you will calculate the interest for the first payment. 5 In cell C8, calculate the interest for the first payment using the IPMT function. Copy the function to the range C9:C19. 18 Next, you will calculate the principal paid. 5 In cell D8, calculate the principal paid for the first payment using the PPMT function. Copy the function to the range D9:019. 19 Rows 21-23 contain a summary section for cumulative totals after the first year. 5 In cell B22, insert the CUMIPMT function that calculates the cumulative interest after the first year. Use references to cells A8 and A19 for the period arguments. 20 The next summary statistic will calculate the principal paid after the first year. 5 In cell B23, insert the CUMPRINC function that calculates the cumulative principal paid after the first year. Use references to cells A8 and A19 for the period arguments. 21 Rows 25-28 contain a section for what-if analysis. 5 In cell B27, use the RATE financial function to calculate the periodic rate using $1,400 as the monthly payment (cell B26), the NPER, and loan amount in the original input section. File Home Insert Draw Page Layout Formulas Data Review View Help 3 Share Comments fx Q ol .. Define Name Calculate Now Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Function Library Use in Formula Name Manager Create from Selection Eg Trace Precedents Dx Show Formulas Fa Trace Dependents Error Checking FRemove Arrows v (1) Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Defined Names Calculation D5 D E F G I 1 J K L M N O P. Q Principal A B C 1 Loan (PV) $300,000.00 2 APR 4.50% 3 Monthly Rate 0.38% 4 NPER 360 5 Monthly Payment $1,520.06 6 7 Payment Number Beg Balance Interest 8 1 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 9 17 10 18 11 19 12 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 Details Map Loan + Ready + 100%