16.You are ready to start completing the loan amortization table. 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.

B22 X fx E F 12 15 A B D 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 Principal 8 1 ($1,125.00) ($395.06) 9 2 ($ 1,123.52) ($396.54) 10 3 ($1,122.03) ($398.02) 11 4 ($1,120.54) ($399.52) 5 ($1,119.04) ($401.02) 13 6 ($1,117.54) ($402.52) 14 7 ($1.116.03) ($404.03) 8 Formula Bar) ($405.54) 16 9 ($1,112.99) ($407.06) 17 10 ($1,111.47) ($408.59) 11 ($1,109.93) ($410.12) 19 12 ($1,108.39) ($411.66) 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What if the goal is $1,400 monthly payment? 26 Monthly Payment $ 1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31 18 32 33 34 35 3R Details Map Loan + Ready B22 X fx E F 12 15 A B D 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 Principal 8 1 ($1,125.00) ($395.06) 9 2 ($ 1,123.52) ($396.54) 10 3 ($1,122.03) ($398.02) 11 4 ($1,120.54) ($399.52) 5 ($1,119.04) ($401.02) 13 6 ($1,117.54) ($402.52) 14 7 ($1.116.03) ($404.03) 8 Formula Bar) ($405.54) 16 9 ($1,112.99) ($407.06) 17 10 ($1,111.47) ($408.59) 11 ($1,109.93) ($410.12) 19 12 ($1,108.39) ($411.66) 20 21 Cumulative Totals after Year 1 22 Interest 23 Principal Paid 24 25 What if the goal is $1,400 monthly payment? 26 Monthly Payment $ 1,400.00 27 Monthly Rate Needed 28 APR Needed 29 30 31 18 32 33 34 35 3R Details Map Loan + Ready