WGU - C268 Spreadsheets - ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Useful formula guide exam with ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
correct answers ||\\||\\
1. Calculate the payment amount for the loan in cell C15.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Reference the cells containing the appropriate loan ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
information as the arguments for the function you use. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Cells C20-C67 in the "Payment" column are populated
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
with the payment amount from cell C15.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
PMT=(c13/12,c12,c11)
Payment
Calculate, in cell D20, the interest amount for period 1 by ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
multiplying the balance in period 0 (cell F19) by the loan ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
interest rate (cell C13) divided by 12. Dividing the interest ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
rate by 12 results in the monthly interest rate. This
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
formula is reusable. The interest for a given period is ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
always the monthly interest rate times the balance from ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the previous period.
||\\||\\ ||\\||\\
=F19*$C$13/12
F19 time absolute values C13 divided by 12.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
,Copy the interest amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "Interest" column of the amortization table. [2 points]
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Drag D19 down to D67 to complete column ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Calculate, in cell E20, the principal amount for period 1. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
The principal amount is the difference between the
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
payment amount (cell C20) and the interest amount (cell ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
D20) for period 1. Construct your formula in such a way
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
that it can be reused to complete the "principal" column
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\
=C20-D20
Copy the principal amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "principal" column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Drag E20 down to E67 to complete column ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Calculate, in cell F20, the balance for period 1. The ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
balance is the difference between the balance for period 0 ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
(cell F19) and the principal amount for period 1 (cell E20).
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
This formula is reusable. The balance is always calculated
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
as the difference between the balance from the previous
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
period and the principal amount for the current period. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=C20-D20
, Copy the balance amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "Balance" column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
drag F20 down to F67 to complete column ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Calculate, in cell G12, the total amount paid by ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
multiplying the payment amount (cell C15) by the term of ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the loan (cell C12). ||\\||\\ ||\\||\\ ||\\||\\
=C15*C12
Calculate the total interest paid in cell G13. The total ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
interest paid is the sum of all interest paid in the "Interest" ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=SUM(D20:D67)
Add D20 range through D67 ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
To verify that the total interest calculation from the
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
amortization table is correct, calculate the total interest ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
paid in cell G14. This is the difference between the Total
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Amount Paid over the course of the loan and the original ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Loan Amount. Notice the negative sign associated with
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the original Loan Amount.
||\\||\\ ||\\||\\ ||\\||\\
Useful formula guide exam with ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
correct answers ||\\||\\
1. Calculate the payment amount for the loan in cell C15.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Reference the cells containing the appropriate loan ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
information as the arguments for the function you use. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Cells C20-C67 in the "Payment" column are populated
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
with the payment amount from cell C15.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
PMT=(c13/12,c12,c11)
Payment
Calculate, in cell D20, the interest amount for period 1 by ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
multiplying the balance in period 0 (cell F19) by the loan ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
interest rate (cell C13) divided by 12. Dividing the interest ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
rate by 12 results in the monthly interest rate. This
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
formula is reusable. The interest for a given period is ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
always the monthly interest rate times the balance from ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the previous period.
||\\||\\ ||\\||\\
=F19*$C$13/12
F19 time absolute values C13 divided by 12.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
,Copy the interest amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "Interest" column of the amortization table. [2 points]
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Drag D19 down to D67 to complete column ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Calculate, in cell E20, the principal amount for period 1. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
The principal amount is the difference between the
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
payment amount (cell C20) and the interest amount (cell ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
D20) for period 1. Construct your formula in such a way
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
that it can be reused to complete the "principal" column
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\
=C20-D20
Copy the principal amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "principal" column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Drag E20 down to E67 to complete column ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Calculate, in cell F20, the balance for period 1. The ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
balance is the difference between the balance for period 0 ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
(cell F19) and the principal amount for period 1 (cell E20).
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
This formula is reusable. The balance is always calculated
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
as the difference between the balance from the previous
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
period and the principal amount for the current period. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=C20-D20
, Copy the balance amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "Balance" column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
drag F20 down to F67 to complete column ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Calculate, in cell G12, the total amount paid by ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
multiplying the payment amount (cell C15) by the term of ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the loan (cell C12). ||\\||\\ ||\\||\\ ||\\||\\
=C15*C12
Calculate the total interest paid in cell G13. The total ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
interest paid is the sum of all interest paid in the "Interest" ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=SUM(D20:D67)
Add D20 range through D67 ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
To verify that the total interest calculation from the
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
amortization table is correct, calculate the total interest ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
paid in cell G14. This is the difference between the Total
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Amount Paid over the course of the loan and the original ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Loan Amount. Notice the negative sign associated with
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the original Loan Amount.
||\\||\\ ||\\||\\ ||\\||\\
