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
Alle Vorteile der Zusammenfassungen von Stuvia auf einen Blick:
Garantiert gute Qualität durch Reviews
Stuvia Verkäufer haben mehr als 700.000 Zusammenfassungen beurteilt. Deshalb weißt du dass du das beste Dokument kaufst.
Schnell und einfach kaufen
Man bezahlt schnell und einfach mit iDeal, Kreditkarte oder Stuvia-Kredit für die Zusammenfassungen. Man braucht keine Mitgliedschaft.
Konzentration auf den Kern der Sache
Deine Mitstudenten schreiben die Zusammenfassungen. Deshalb enthalten die Zusammenfassungen immer aktuelle, zuverlässige und up-to-date Informationen. Damit kommst du schnell zum Kern der Sache.
Häufig gestellte Fragen
Was bekomme ich, wenn ich dieses Dokument kaufe?
Du erhältst eine PDF-Datei, die sofort nach dem Kauf verfügbar ist. Das gekaufte Dokument ist jederzeit, überall und unbegrenzt über dein Profil zugänglich.
Zufriedenheitsgarantie: Wie funktioniert das?
Unsere Zufriedenheitsgarantie sorgt dafür, dass du immer eine Lernunterlage findest, die zu dir passt. Du füllst ein Formular aus und unser Kundendienstteam kümmert sich um den Rest.
Wem kaufe ich diese Zusammenfassung ab?
Stuvia ist ein Marktplatz, du kaufst dieses Dokument also nicht von uns, sondern vom Verkäufer learndirect. Stuvia erleichtert die Zahlung an den Verkäufer.
Werde ich an ein Abonnement gebunden sein?
Nein, du kaufst diese Zusammenfassung nur für 10,34 €. Du bist nach deinem Kauf an nichts gebunden.
