WGU C268 Study Guide for EXCEL ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
PA exam with correct answers
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
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)
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.
||\\||\\ ||\\||\\
f
=F19*C$13/12
Copy the Interest amount calcualtion down to complete
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "interest" column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
,Paste down 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.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Copy and paste down. ||\\||\\ ||\\||\\ ||\\||\\
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. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=F19-E20
Copy the balance amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the balance column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
, Copy and paste down. ||\\||\\ ||\\||\\ ||\\||\\
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)
Check to see if the total interest calculation in the ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
amortization table is correct. The total interest paid is also ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
equal to the difference between the total amount paid
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
over the course of the loan and the original loan amount.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Insert a formula into cell G14 to calculate the difference
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
between the total amount paid and the original loan ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
amount. Notice the negative sign associated with the ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
original loan amount. This value should equal the total ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
interest calculated using the amortization table. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=G12+C11
Assume you have made the first 36 payments on your ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
loan. You want to trade the car in for a new car. You
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
PA exam with correct answers
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
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)
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.
||\\||\\ ||\\||\\
f
=F19*C$13/12
Copy the Interest amount calcualtion down to complete
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the "interest" column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
,Paste down 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.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Copy and paste down. ||\\||\\ ||\\||\\ ||\\||\\
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. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=F19-E20
Copy the balance amount calculation down to complete ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
the balance column of the amortization table.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
, Copy and paste down. ||\\||\\ ||\\||\\ ||\\||\\
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)
Check to see if the total interest calculation in the ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
amortization table is correct. The total interest paid is also ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
equal to the difference between the total amount paid
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
over the course of the loan and the original loan amount.
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
Insert a formula into cell G14 to calculate the difference
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
between the total amount paid and the original loan ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
amount. Notice the negative sign associated with the ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
original loan amount. This value should equal the total ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
interest calculated using the amortization table. ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
=G12+C11
Assume you have made the first 36 payments on your ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
loan. You want to trade the car in for a new car. You
||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\ ||\\||\\
