WGU C268 Study Guide for EXCEL PA Newest Exam
Preparation Newest With Complete Questions And
Correct Detailed -Answer-: s| Brand New Version
Section 1: Loan Amortization Table
1. PMT Function for Loan Payment
Question: 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.
-Answer-: : =PMT(C13/12,C12,C11)
Explanation: The PMT function calculates the periodic payment for a loan.
• C13/12: Converts the annual interest rate (in C13) to a monthly rate by
dividing by 12.
• C12: Number of payment periods (total months of the loan).
• C11: Present value or loan amount (negative because it's money going out).
The result is a monthly payment amount that will be the same for each
period.
2. Interest Calculation for Period 1
Question: 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.
-Answer-: : =F19*C$13/12
Explanation: This calculates the interest portion of the payment for the first
period:
• F19: Previous balance (period 0)
• C$13/12: Monthly interest rate (absolute column reference so it doesn't
change when copied down)
The formula multiplies the previous balance by the monthly interest rate to
find the interest due for that period.
3. Principal Calculation for Period 1
Question: 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.
-Answer-: : =C20-D20
Explanation: The principal payment is the portion of the total payment that
reduces the loan balance:
• C20: Total payment for period 1
• D20: Interest portion for period 1
Subtracting interest from the total payment gives the amount applied to
principal.
4. Balance Calculation for Period 1
Question: 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.
-Answer-: : =F19-E20
Explanation: This calculates the remaining loan balance after each payment:
• F19: Previous period's balance
• E20: Principal paid in current period
Subtracting the principal payment from the previous balance gives the new
balance.
5. Total Amount Paid Over Loan Term
Question: Calculate, in cell G12, the total amount paid by multiplying the
payment amount (cell C15) by the term of the loan (cell C12).
-Answer-: : =C15*C12
Explanation: This calculates the total of all payments made over the life of the
loan:
• C15: Monthly payment amount
• C12: Total number of payments (months)
Multiplying gives the total amount paid, which includes both principal and
interest.
6. Total Interest Paid Using SUM
Question: 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.
-Answer-: : =SUM(D20:D67)
Explanation: This sums all the interest payments from the amortization table:
, • D20:D67: Range containing interest amounts for each period
The SUM function adds all these values to find the total interest paid over
the loan term.
7. Verify Total Interest Calculation
Question: 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.
-Answer-: : =G12+C11
Explanation: This verifies the interest calculation using an alternative method:
• G12: Total amount paid (positive value)
• C11: Original loan amount (negative value because it's money received)
Adding them (e.g., 10000 + (-8000)) gives the total interest (2000 in this
example).
8. Car Trade-In Decision
Question: Assume you have made the first 36 payments on your loan. You want
to trade the car in for a new car. You believe that you can sell your car for $4000.
Will this cover the balance remaining on the car in period 36? -Answer-: either
"Yes" or "No" in cell G15 from the drop-down menu.
-Answer-: : No
Explanation: This requires comparing the sale price ($4000) with the remaining
loan balance in period 36 (cell F55, which is the 36th payment period). If the
balance in F55 is more than $4000, the -Answer-: is "No" because the sale won't
cover the full remaining balance.
Preparation Newest With Complete Questions And
Correct Detailed -Answer-: s| Brand New Version
Section 1: Loan Amortization Table
1. PMT Function for Loan Payment
Question: 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.
-Answer-: : =PMT(C13/12,C12,C11)
Explanation: The PMT function calculates the periodic payment for a loan.
• C13/12: Converts the annual interest rate (in C13) to a monthly rate by
dividing by 12.
• C12: Number of payment periods (total months of the loan).
• C11: Present value or loan amount (negative because it's money going out).
The result is a monthly payment amount that will be the same for each
period.
2. Interest Calculation for Period 1
Question: 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.
-Answer-: : =F19*C$13/12
Explanation: This calculates the interest portion of the payment for the first
period:
• F19: Previous balance (period 0)
• C$13/12: Monthly interest rate (absolute column reference so it doesn't
change when copied down)
The formula multiplies the previous balance by the monthly interest rate to
find the interest due for that period.
3. Principal Calculation for Period 1
Question: 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.
-Answer-: : =C20-D20
Explanation: The principal payment is the portion of the total payment that
reduces the loan balance:
• C20: Total payment for period 1
• D20: Interest portion for period 1
Subtracting interest from the total payment gives the amount applied to
principal.
4. Balance Calculation for Period 1
Question: 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.
-Answer-: : =F19-E20
Explanation: This calculates the remaining loan balance after each payment:
• F19: Previous period's balance
• E20: Principal paid in current period
Subtracting the principal payment from the previous balance gives the new
balance.
5. Total Amount Paid Over Loan Term
Question: Calculate, in cell G12, the total amount paid by multiplying the
payment amount (cell C15) by the term of the loan (cell C12).
-Answer-: : =C15*C12
Explanation: This calculates the total of all payments made over the life of the
loan:
• C15: Monthly payment amount
• C12: Total number of payments (months)
Multiplying gives the total amount paid, which includes both principal and
interest.
6. Total Interest Paid Using SUM
Question: 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.
-Answer-: : =SUM(D20:D67)
Explanation: This sums all the interest payments from the amortization table:
, • D20:D67: Range containing interest amounts for each period
The SUM function adds all these values to find the total interest paid over
the loan term.
7. Verify Total Interest Calculation
Question: 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.
-Answer-: : =G12+C11
Explanation: This verifies the interest calculation using an alternative method:
• G12: Total amount paid (positive value)
• C11: Original loan amount (negative value because it's money received)
Adding them (e.g., 10000 + (-8000)) gives the total interest (2000 in this
example).
8. Car Trade-In Decision
Question: Assume you have made the first 36 payments on your loan. You want
to trade the car in for a new car. You believe that you can sell your car for $4000.
Will this cover the balance remaining on the car in period 36? -Answer-: either
"Yes" or "No" in cell G15 from the drop-down menu.
-Answer-: : No
Explanation: This requires comparing the sale price ($4000) with the remaining
loan balance in period 36 (cell F55, which is the 36th payment period). If the
balance in F55 is more than $4000, the -Answer-: is "No" because the sale won't
cover the full remaining balance.
