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.

Now, you will calculate the interest for the first payment. In cell C8, calculate the interest for the first payment using the IPMT function. Copy the function to the range C9:C19.

Next, you will calculate the principal paid. In cell D8, calculate the principal paid for the first payment using the PPMT function. Copy the function to the range D9:D19.

Rows 21-23 contain a summary section for cumulative totals after the first year. 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.

The next summary statistic will calculate the principal paid after the first year. 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.

Rows 25-28 contain a section for what-if analysis. 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.

In cell B28, calculate the APR by multiplying the monthly rate (cell B27) by 12.

Loan (PV) APR Monthly Rate NPER Monthly Payment $ 300,000.00 4.50% 0.38% 360 $1,520.06 Principal Payment Number 1 Beg Balance Interest $ 300,000.00 WN 3 4 5 6 7 8 9 10 11 12 Cumulative Totals after Year 1 Interest Principal Paid What if the goal is $1,400 monthly payment? Monthly Payment $ 1,400.00 Monthly Rate Needed APR Needed Loan (PV) APR Monthly Rate NPER Monthly Payment $ 300,000.00 4.50% 0.38% 360 $1,520.06 Principal Payment Number 1 Beg Balance Interest $ 300,000.00 WN 3 4 5 6 7 8 9 10 11 12 Cumulative Totals after Year 1 Interest Principal Paid What if the goal is $1,400 monthly payment? Monthly Payment $ 1,400.00 Monthly Rate Needed APR Needed