Question

 13

 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.

 14

 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.

 15

 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.

 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 apppopriate

 absolute, relative, or mixed cell references.

 19

 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.

Answer 1

• Find the Payment 1 and Payment 2 using steps mentioned.
• Remaining payments can be Found by dragging the pointer down.
• Provided the steps and attached screen shot of the excel sheet.

SOLUTION STEPS:

Step 1: Enter loan amount at B12 cell

Step 2: Enter fixed reference for payment amount from cell B6 to C12

Step 3: Interest paid can be calculated from balance

D12 =B12 * $E$7

Step 4:   Calculate Principal Repayment = Payment Amount – interest paid

E12 =C12-D12

Step 5: Remaining Balance = Beginning Balance – Prinicipal Repayment

F12 =B12-E12

Step 6: Cumulative Interest G12 =D12

Cumulative interest H12 =E12

Step 7: Calculate payment 2 details using payment 1 details

Beginning Balance B13 =F12

Payment Amount C13 =$B$6

Interest Paid D13 =B13 * $E$7

Principal Repayment E13 =C13-D13

Remaining Balance F13 =B13-E13

Cumulative Interest = Payment 1 cumulative interest + interest paid for payment 2

G13 =G12+D13

Cumulative Principal = Payment 1 cumulative payment + payment 2 principal payment

H13 =H12+E13

Step 8: Drag the payment 2 for remaining payments

Select the payment 2 row and drag the + that appears lower right corner of the selection

And you would get as below,