Gutierrez Summer 2017 ABF 350 Excel Amortization Assignment
EXCEL Assignment Instructions: Use the spreadsheet ExcelAssignment_
Amoritz_StartData_Summer17.xlsx found under Content in D2L to begin this
assignment. Do a “save as” to a drive you have control of (like the C: drive, not
an “Open”. Save your spreadsheet as YourlastnameYourinitialsectionnumber
__Amoritz.xls (e.g., SmithJ03_Amoritz.xlsx. Note: DO NOT use and # or & in the
name. This assignment is worth 50 points
A.- Fill in on the Loan Amortization Sheet: Enter: Purchase price = $305,000 dollars, Down Payment = 18% of Purchase
price (calculate the down payment), Credit Rating = 735, Loan Term = 20 years
(then calculate per term – the number of periods in the loan), Application Date =
2/10/2017, PMI=1.16%. Enter a formula to update today’s date.
B.- Your Information – Formatted Customer Name is Your Name Last 5 of Loan Number is the last 5 numbers of Your Student ID Number.
Format the cell as follows: L-00000 (use custom formatting). For Example: L19898
C.- Functions: Make Sure You Use Absolute Reference for the Periods and the
Interest Rate Per Month to be used In your Formulas and whenever possible
for the other formulas. Also, make sure you use the End of the Month for
the payments. Add a 1.6% Assessment Fee (of the purchase price), calculate a formula to
determine if the down payment is less than $50,000. (Use the IF function). Calculate the Loan Amount by subtracting the Down Payment and adding back
in the Assessment Fee. Use Excel VLOOKUP function to obtain the interest rate. Use the Interest Rate
selected by the function where the Interest rate is based on the Credit Rating.
Then calculate the per period interest rate. For the PMI Insurance per year use the percent PMI times the purchase price of
the home. Then calculate the per period cost. For Period Payment use the PMT function (remember you already calculated
the interest rate per period). For the Payment Plus PMI, add the Period Payment to the PMI Period Payment. Fill-in the periods (how many months it will take to complete the loan) using the
auto fill (1 through n) in the first column of the amortization table. For the following formulas, fill in the first two rows before you complete
the amortization table. Use the Present Value (PV) function to determine the Beginning Balance of the
first period (although we know it's the loan amount for the first period. After the first period the beginning balance becomes the ending balance of the
previous period. -1- Gutierrez Summer 2017 Use the Principal Payment (PPMT) function to determine the amount of money
for each payment that is going to the Principal.
Use the Interest Payment (IPMT) function to determine the amount of money for
each payment that is being paid in interest. Do not add in PMI, it is insurance
not interest.
Use either the CUMIPMT and CUMPRINC or the SUM functions to determine
the Total Interest and Total Principal up to that period (this is a running total).
Calculate the ending balance based on the beginning balance and the amount
paid toward the loan this period.
For the Payment with PMI column, use an IF statement to determine when the
buyer should stop paying PMI Insurance. The payment becomes the regular
payment when the balance of the loan is less than 80% of the value of the home
(purchase price) otherwise it is the payment with PMI. This contains the
payment for the period with or without PMI.
Fill the rest of the Amortization table for all the Periods (months) of the loan
using Absolute and Relative References. NOTE: Don’t worry about the colors
of the functions, as some will be red (negative) and some will be black (positive),
the important thing is that the ending balance should be zero (0) and the Total
Principal should equal the loan amount. E – Formatting: Format all titles so that the CUSTOMER INFORMATION, EXTERNAL DATA,
and LOAN INFORMATION titles are centered across the correct number of cells
(merge & center when necessary). Add colors to enhance the sheet. Format the titles on the bottom tables so that the titles "wrap around" the cells
and are centered both horizontally and vertically within the cells. Give all currency amounts a dollar value with two decimals Give all percentage amounts a percentage value with two decimal places. Make sure all data and words show in the columns (the column is wide enough
to hold the data).
F - Evaluate Changes in Interest Rate. Copy the original loan sheet to two additional sheets. Rename the one sheet “CR555” and the other “CR785). Change the credit rating to 555 on the first new sheet. Change the credit rating to 780 on the second new sheet. Make a comparison between each of the three sheets. Explain in a text box (on
the original sheet) how your credit rating affects the loan and your payments. -2- Gutierrez Summer 2017 EXTRA CREDIT: PIVOT Chart (5 points: Extra Credit): Create a Pivot Chart on a new sheet (name it “Pivot Chart”) from the data on
the Pivot Table Sheet, where the total amount of the purchases are summarized
by Customer. Display ONLY Customers "DREW, LAMAI, MONTY, PEREZ, QUEEN & VINET".
Do not delete the other customers; use the filter in the Pivot Chart to limit the
Customers. Name the Chart Total Purchases for Select Customers. Place the customers on the X axis, title the axis “Customer”, Place the dollar
amounts on the Y axis, title the axis “Sales”. Select the best type of chart to display the data. Add a text box with your name
into the chart. Attach the assignment to the Digital Drop Box by midnight of the due date.
Electronic only! -3-

• 1497942844-ExcelAssignment_amoritz_startdata_Summer17.xlsx
• 1497942847-Excel_amoriz_Summer17.docx

SuperTutor

(15)

Status NEW Posted 20 Jun 2017 07:06 AM My Price 20.00

-----------

Attachments

file 1497942849-Solutions file.docx preview (56 words )

S-----------olu-----------tio-----------ns -----------fil-----------e -----------Hel-----------lo -----------Sir-----------/Ma-----------dam----------- T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------on�-----------�th-----------is -----------web-----------sit-----------e. ----------- H-----------YPE-----------RLI-----------NK -----------&qu-----------ot;-----------htt-----------p:/-----------/wo-----------rkb-----------ank-----------247-----------.co-----------m/&-----------quo-----------t; -----------\t -----------&qu-----------ot;-----------_bl-----------ank-----------&qu-----------ot;----------- -----------Tha-----------nk -----------you----------- -----------

Not Rated(0)

• Buy Answer