WGU - C268 Spreadsheets - Useful formula guide
1. 1. Calculate the payment amount for the
loan in cell C15. Reference the cells con-
taining the appropriate loan information
as the arguments for the function you
use. Cells C20-C67 in the "Payment" col-
umn are populated with the payment
amou...
WGU - C268 Spreadsheets - Useful formula guide
1. 1. Calculate the payment amount for the PMT=(c13/12,c12,c11)
loan in cell C15. Reference the cells con- Payment
taining the appropriate loan information
as the arguments for the function you
use. Cells C20-C67 in the "Payment" col-
umn are populated with the payment
amount from cell C15.
2. Calculate, in cell D20, the interest =F19*$C$13/12
amount for period 1 by multiplying the F19 time absolute values C13 di-
balance in period 0 (cell F19) by the loan vided by 12.
interest rate (cell C13) divided by 12. Di-
viding 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.
3. Copy the interest amount calculation Drag D19 down to D67 to com-
down to complete the "Interest" column plete column
of the amortization table. [2 points]
4. Calculate, in cell E20, the principal =C20-D20
amount for period 1. The principal
amount is the difference between the
payment amount (cell C20) and the inter-
est amount (cell D20) for period 1. Con-
struct your formula in such a way that it
can be reused to complete the "princi-
pal" column of the amortization table.
5. Copy the principal amount calculation Drag E20 down to E67 to com-
down to complete the "principal" column plete column
of the amortization table.
6. Calculate, in cell F20, the balance for =C20-D20
period 1. The balance is the difference
between the balance for period 0 (cell
F19) and the principal amount for peri-
, WGU - C268 Spreadsheets - Useful formula guide
od 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.
7. Copy the balance amount calculation drag F20 down to F67 to com-
down to complete the "Balance" column plete column
of the amortization table.
8. Calculate, in cell G12, the total amount =C15*C12
paid by multiplying the payment amount
(cell C15) by the term of the loan (cell
C12).
9. Calculate the total interest paid in cell =SUM(D20:D67)
G13. The total interest paid is the sum of
all interest paid in the "Interest" column Add D20 range through D67
of the amortization table.
10. To verify that the total interest calcula- =G12--C11
tion from the amortization table is cor-
rect, calculate the total interest paid in two minus cancel out the minus
cell G14. This is the difference between from C11
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.
11. Assume you have made the first 36 pay- No
ments on your loan. You want to trade
the car in for a new car. You believe that G36 = 5,193.87 $4,000 is not
you can sell your car for $4000. Will this enough
cover the balance remaining on the car in
period 36? Answer either "Yes" or "No"
in cell G15 from the drop-down menu.
12. Use the HLOOKUP function to complete =hlookup(D16, $E$11:$H$12, 2,
the "Hourly Wage" column of table 1. 0)
Use the "Employee" from table 1 as the
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller learndirect. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $10.59. You're not tied to anything after your purchase.
