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
Voordelen van het kopen van samenvattingen bij Stuvia op een rij:
Verzekerd van kwaliteit door reviews
Stuvia-klanten hebben meer dan 700.000 samenvattingen beoordeeld. Zo weet je zeker dat je de beste documenten koopt!
Snel en makkelijk kopen
Je betaalt supersnel en eenmalig met iDeal, creditcard of Stuvia-tegoed voor de samenvatting. Zonder lidmaatschap.
Focus op de essentie
Samenvattingen worden geschreven voor en door anderen. Daarom zijn de samenvattingen altijd betrouwbaar en actueel. Zo kom je snel tot de kern!
Veelgestelde vragen
Wat krijg ik als ik dit document koop?
Je krijgt een PDF, die direct beschikbaar is na je aankoop. Het gekochte document is altijd, overal en oneindig toegankelijk via je profiel.
Tevredenheidsgarantie: hoe werkt dat?
Onze tevredenheidsgarantie zorgt ervoor dat je altijd een studiedocument vindt dat goed bij je past. Je vult een formulier in en onze klantenservice regelt de rest.
Van wie koop ik deze samenvatting?
Stuvia is een marktplaats, je koop dit document dus niet van ons, maar van verkoper learndirect. Stuvia faciliteert de betaling aan de verkoper.
Zit ik meteen vast aan een abonnement?
Nee, je koopt alleen deze samenvatting voor €10,11. Je zit daarna nergens aan vast.
