WGUiC268iSpreadsheetsiQuestionsiandiAnswersiAlreadyiG
radediA+
CalculateitheipaymentiamountiforitheiloaniinicelliC15.iReferenceitheicellsicontainingithe
appropriateiloaniinformationiasitheiargumentsiforitheifunctioniyouiuse.iCellsiC20-C67iinithe
"Payment"icolumniareipopulatediwithitheipaymentiamountifromicelliC15.i[34iPoints]
✔✔=PMT(C13/12,C12,C11)
Calculate,iinicelliD20,itheiinterestiamountiforiperiodi1ibyimultiplyingitheibalanceiiniperiodi0i(celliF
19)ibyitheiloaniinterestiratei(celliC13)idividedibyi12.iDividingitheiinterestirateibyi12iresultsiinithei
monthlyiinterestirate.iThisiformulaiisireusable.iTheiinterestiforiaigiveniperiodiisialwaysitheimonthl
yiinterestirateitimesitheibalanceifromitheipreviousiperiod.
✔✔=F19*$C$13/12
Calculate,iinicelliE20,itheiprincipaliamountiforiperiodi1.iTheiprincipaliamountiisitheidifferenceibet
weenitheipaymentiamounti(celliC20)ianditheiinterestiamounti(celliD20)iforiperiodi1.
Constructiyouriformulaiinisuchiaiwayithatiiticanibeireuseditoicompleteithei"principal"icolumniofit
heiamortizationitable.i ✔✔=C20-D20
Calculate,iinicelliF20,itheibalanceiforiperiodi1.iTheibalanceiisitheidifferenceibetweenitheibalanceif
oriperiodi0i(celliF19)ianditheiprincipaliamountiforiperiodi1i(celliE20).iThisiformulaiisireusable.iThei
balanceiisialwaysicalculatediasitheidifferenceibetweenitheibalanceifromitheipreviousiperiodiandit
heiprincipaliamountiforitheicurrentiperiod.i ✔✔=F19-E20
Calculate,iinicelliG12,itheitotaliamountipaidibyimultiplyingitheipaymentiamounti(celliC15)ibyitheit
ermiofitheiloani(celliC12).i ✔✔=C15*C12
CalculateitheitotaliinterestipaidiinicelliG13.iTheitotaliinterestipaidiisitheisumiofialliinterestipaidiinit
hei"Interest"icolumniofitheiamortizationitable.i ✔✔=SUM(D20:D67)
Checkitoiseeiifitheitotaliinteresticalculationiinitheiamortizationitableiisicorrect.iTheitotaliintere
stipaidiisialsoiequalitoitheidifferenceibetweenitheitotaliamountipaidioveritheicourseiof
theiloanianditheioriginaliloaniamount.iInsertiaiformulaiintoicelliG14itoicalculateitheidifference
1i|iP a g e
, betweenitheitotaliamountipaidianditheioriginaliloaniamount.iNoticeitheinegativeisigniassociatedi
withitheioriginaliloaniamount.iThisivalueishouldiequalitheitotaliinteresticalculatediusingitheiamorti
zationitable.i ✔✔=G12-ABS(C11)
Assumeiyouihaveimadeitheifirsti36ipaymentsioniyouriloan.iYouiwantitoitradeitheicariiniforia
newicar.iYouibelieveithatiyouicaniselliyouricarifori$4000.iWillithisicoveritheibalanceiremaining
onitheicariiniperiodi36?iAnswerieitheri"Yes"iori"No"iinicelliG15ifromitheidrop-downimenu.
✔✔No
UseitheiHLOOKUPifunctionitoicompleteithei"HourlyiWage"icolumniofitablei1.iUseithei"Employee
"icolumniofitablei1iasitheilookup_valueiandithei"EmployeeiWageiInformation"iaboveitablei1iasiyo
urireferenceitable.i ✔✔=HLOOKUP(D16,$E$11:$H$12,2,FALSE)
UseitheiANDifunctionitoicompleteithei"TimeiBonus?"icolumniofitablei1.iAniemployeeiearnsiaitimei
bonusiifitheiproject'si"HoursiWorked"iareifewerithanithei"EstimatediHours"iandiifitheiworki"Qual
ity"iisigreaterithani1.i ✔✔=AND(E16<C16,H16>1)
UseitheiORifunctionitoicompleteithei"OutcomeiBonus?"icolumniofitablei1.iAniemployeeiearnsiani
outcomeibonusiifitheidifficultyiofiaijobiisigreaterithani3ioriifitheiqualityiofitheiriworkiisiequalitoi3.
i ✔✔=OR(G16>3,H16=3)
UseitheiIFifunctionitoicompleteithei"TimeiBonusi$"icolumniofitablei1.iIfianiemployeeiearnsiaitimei
bonusi(i.e.,itheicorrespondingicelliinithei"TimeiBonus?"icolumniisiTRUE),itheni"TimeiBonusi$"iisit
hei"JobiPay"iforithatiprojectitimesitheibonusipercentageiinicelliM11.iOtherwisei"TimeiBonusi$"ii
si0.i ✔✔=IF(I16,K16*$M$11,0)
UseitheiIFifunctionitoicompleteithei"OutcomeiBonusi$"icolumniofitablei1.iIfianiemployeeiearnsiani
outcomeibonusi(i.e.,itheicorrespondingicelliinithei"OutcomeiBonus?"icolumniisiTRUE),itheni"Outc
omeiBonusi$"iisithei"JobiPay"iforithatiprojectitimesitheioutcomeibonusipercentageiinicelliM12;iot
herwise,i"OutcomeiBonusi$"iisi0.i ✔✔=IF(J16,K16*$M$12,0)
UseitheiIFifunctionitoicompleteithei"Comments"icolumniofitablei1.iDisplayi"GoodiJob"iifiboth
thei"HoursiWorked"iareilessithanioriequalitoithei"EstimatediHours"iforiaiprojectiandithe
2i|iP a g e
radediA+
CalculateitheipaymentiamountiforitheiloaniinicelliC15.iReferenceitheicellsicontainingithe
appropriateiloaniinformationiasitheiargumentsiforitheifunctioniyouiuse.iCellsiC20-C67iinithe
"Payment"icolumniareipopulatediwithitheipaymentiamountifromicelliC15.i[34iPoints]
✔✔=PMT(C13/12,C12,C11)
Calculate,iinicelliD20,itheiinterestiamountiforiperiodi1ibyimultiplyingitheibalanceiiniperiodi0i(celliF
19)ibyitheiloaniinterestiratei(celliC13)idividedibyi12.iDividingitheiinterestirateibyi12iresultsiinithei
monthlyiinterestirate.iThisiformulaiisireusable.iTheiinterestiforiaigiveniperiodiisialwaysitheimonthl
yiinterestirateitimesitheibalanceifromitheipreviousiperiod.
✔✔=F19*$C$13/12
Calculate,iinicelliE20,itheiprincipaliamountiforiperiodi1.iTheiprincipaliamountiisitheidifferenceibet
weenitheipaymentiamounti(celliC20)ianditheiinterestiamounti(celliD20)iforiperiodi1.
Constructiyouriformulaiinisuchiaiwayithatiiticanibeireuseditoicompleteithei"principal"icolumniofit
heiamortizationitable.i ✔✔=C20-D20
Calculate,iinicelliF20,itheibalanceiforiperiodi1.iTheibalanceiisitheidifferenceibetweenitheibalanceif
oriperiodi0i(celliF19)ianditheiprincipaliamountiforiperiodi1i(celliE20).iThisiformulaiisireusable.iThei
balanceiisialwaysicalculatediasitheidifferenceibetweenitheibalanceifromitheipreviousiperiodiandit
heiprincipaliamountiforitheicurrentiperiod.i ✔✔=F19-E20
Calculate,iinicelliG12,itheitotaliamountipaidibyimultiplyingitheipaymentiamounti(celliC15)ibyitheit
ermiofitheiloani(celliC12).i ✔✔=C15*C12
CalculateitheitotaliinterestipaidiinicelliG13.iTheitotaliinterestipaidiisitheisumiofialliinterestipaidiinit
hei"Interest"icolumniofitheiamortizationitable.i ✔✔=SUM(D20:D67)
Checkitoiseeiifitheitotaliinteresticalculationiinitheiamortizationitableiisicorrect.iTheitotaliintere
stipaidiisialsoiequalitoitheidifferenceibetweenitheitotaliamountipaidioveritheicourseiof
theiloanianditheioriginaliloaniamount.iInsertiaiformulaiintoicelliG14itoicalculateitheidifference
1i|iP a g e
, betweenitheitotaliamountipaidianditheioriginaliloaniamount.iNoticeitheinegativeisigniassociatedi
withitheioriginaliloaniamount.iThisivalueishouldiequalitheitotaliinteresticalculatediusingitheiamorti
zationitable.i ✔✔=G12-ABS(C11)
Assumeiyouihaveimadeitheifirsti36ipaymentsioniyouriloan.iYouiwantitoitradeitheicariiniforia
newicar.iYouibelieveithatiyouicaniselliyouricarifori$4000.iWillithisicoveritheibalanceiremaining
onitheicariiniperiodi36?iAnswerieitheri"Yes"iori"No"iinicelliG15ifromitheidrop-downimenu.
✔✔No
UseitheiHLOOKUPifunctionitoicompleteithei"HourlyiWage"icolumniofitablei1.iUseithei"Employee
"icolumniofitablei1iasitheilookup_valueiandithei"EmployeeiWageiInformation"iaboveitablei1iasiyo
urireferenceitable.i ✔✔=HLOOKUP(D16,$E$11:$H$12,2,FALSE)
UseitheiANDifunctionitoicompleteithei"TimeiBonus?"icolumniofitablei1.iAniemployeeiearnsiaitimei
bonusiifitheiproject'si"HoursiWorked"iareifewerithanithei"EstimatediHours"iandiifitheiworki"Qual
ity"iisigreaterithani1.i ✔✔=AND(E16<C16,H16>1)
UseitheiORifunctionitoicompleteithei"OutcomeiBonus?"icolumniofitablei1.iAniemployeeiearnsiani
outcomeibonusiifitheidifficultyiofiaijobiisigreaterithani3ioriifitheiqualityiofitheiriworkiisiequalitoi3.
i ✔✔=OR(G16>3,H16=3)
UseitheiIFifunctionitoicompleteithei"TimeiBonusi$"icolumniofitablei1.iIfianiemployeeiearnsiaitimei
bonusi(i.e.,itheicorrespondingicelliinithei"TimeiBonus?"icolumniisiTRUE),itheni"TimeiBonusi$"iisit
hei"JobiPay"iforithatiprojectitimesitheibonusipercentageiinicelliM11.iOtherwisei"TimeiBonusi$"ii
si0.i ✔✔=IF(I16,K16*$M$11,0)
UseitheiIFifunctionitoicompleteithei"OutcomeiBonusi$"icolumniofitablei1.iIfianiemployeeiearnsiani
outcomeibonusi(i.e.,itheicorrespondingicelliinithei"OutcomeiBonus?"icolumniisiTRUE),itheni"Outc
omeiBonusi$"iisithei"JobiPay"iforithatiprojectitimesitheioutcomeibonusipercentageiinicelliM12;iot
herwise,i"OutcomeiBonusi$"iisi0.i ✔✔=IF(J16,K16*$M$12,0)
UseitheiIFifunctionitoicompleteithei"Comments"icolumniofitablei1.iDisplayi"GoodiJob"iifiboth
thei"HoursiWorked"iareilessithanioriequalitoithei"EstimatediHours"iforiaiprojectiandithe
2i|iP a g e
