Ensure that the Facilities worksheet is active. Enter a reference to the beginning loan balance in cell B12 and enter a reference to the payment amount in cell C12. 13 Enter a function in cell D12, based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 14 Enter a function in cell E12, based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. 15 16 Enter a formula in cell F12 to calculate the remaining balance after the current payment. The remaining balance is calculated by subtracting the principal payment from the balance in column B 17 Enter a function in cell G12, based on the payment and loan details, that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. 18 Enter a function in cell H12, based on the payment and loan details, that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. Enter a reference to the remaining balance of payment 1 in cell B13. Use the fill handle to copy the functions created in the prior steps down to complete the amortization table. Advanced Sort & Filter Queries & Connections Data Types Get & Transform Data L16 Beta Manufacturing Facility Amortization Table Loan Details Payment Details Loan Periodic Rate # of Payments $312,227.32 0.479% 60 $6,000.00 6 Payment 7 APR 8 Years 9 Pmts per Year 5.75% 12 Principal Remaining Cumulative Cumulative Payment Beginning Payment Number Interest Principal 12 13 14 15 17 18 19 20 21 9 10 23 12 13 14 24 Ensure that the Facilities worksheet is active. Enter a reference to the beginning loan balance in cell B12 and enter a reference to the payment amount in cell C12. 13 Enter a function in cell D12, based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 14 Enter a function in cell E12, based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. 15 16 Enter a formula in cell F12 to calculate the remaining balance after the current payment. The remaining balance is calculated by subtracting the principal payment from the balance in column B 17 Enter a function in cell G12, based on the payment and loan details, that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. 18 Enter a function in cell H12, based on the payment and loan details, that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. Enter a reference to the remaining balance of payment 1 in cell B13. Use the fill handle to copy the functions created in the prior steps down to complete the amortization table. Advanced Sort & Filter Queries & Connections Data Types Get & Transform Data L16 Beta Manufacturing Facility Amortization Table Loan Details Payment Details Loan Periodic Rate # of Payments $312,227.32 0.479% 60 $6,000.00 6 Payment 7 APR 8 Years 9 Pmts per Year 5.75% 12 Principal Remaining Cumulative Cumulative Payment Beginning Payment Number Interest Principal 12 13 14 15 17 18 19 20 21 9 10 23 12 13 14 24
