Question

3.4 calculate the beginning balance as well as the interest and balance reduction elements of each payment.
a. the beginning balance of the loan is in cell c6.
b. the beginning balance in the amortization table will be in cell e15.
c. set cell e15 equal to c6.
d. in cell c16 use the ipmt function to calculate the amount of interest paid in each period.
e. in cell d16 use the ppmt function to calculate the amount of loan balance reduced during each period.
f. place a minus sign in front of these calculations to make the results positive numbers.
g. use relative and absolute references correctly so these calculations will autofill down for all 120 months.

Explanation:

Step1: Set initial balance

Set cell E15 equal to C6 to establish the starting - point of the amortization table.

Step2: Calculate interest

In cell C16, use the IPMT function with appropriate arguments (interest rate, period, total number of periods, present value etc.) to find the interest paid in the period. Add a minus sign to make the result positive.

Step3: Calculate principal reduction

In cell D16, use the PPMT function with relevant arguments (interest rate, period, total number of periods, present value etc.) to find the amount of loan balance reduction in the period. Add a minus sign to make the result positive.

Step4: Autofill

Use relative and absolute references correctly so that the calculations in cells C16 and D16 can be autofilled down for all 120 months.

Answer:

Follow the steps above to calculate the beginning balance, interest, and balance - reduction elements for each of the 120 payments in the amortization table. The specific values will depend on the actual values in cell C6 and the input parameters for the IPMT and PPMT functions (such as interest rate, number of periods, etc.).