LAB $4$: LOAN REPAYMENT AND AMORTIZATION $($ASSIGNMENT$)$

NAME

Note: you will need to complete both written work on this assignment in addition to work

on your Excel template file. Be sure you turn in both after finishing your lab for full credit.

Problem $1.(15$ points, In Excel$)$ Be sure you have followed along and completed the

work in the Excel template during the examples covered in lab.

Problem $2.$ Assume you are an accountant working with two clients Alice and Bob. They

are both buying used cars and are asking for your help with finalizing amortization.

a$)(5$ points, In Excel$)$ Alice, with an excellent credit score of over $800,$ negotiated a loan

with $6\mathrm{.}8\%$ annual interest, compounding monthly $($the approximate summer $2023$ average

rate, according to Experian$).$ Assuming her loan was for $$25,000$ over $72$ months, build an

amortization table for Alice$$s loan in the Problem $2$a$)$ tab of your Excel template.

b$)(5$ points, In Excel$)$ Unfortunately, Bob has a credit history with some missed payments

and has a subprime credit score of about $550,$ meaning he was only able to secure a loan for

$18\mathrm{.}6\%$ annual interest, compounding monthly $($again$,$ the approximate summer $2023$ average

rate, according to Experian$).$ Assuming he had the same initial balance and loan length

as Alice, build an amortization table for Bob$$s loan in the Problem $2$b$)$ tab of your Excel

template.

c$)(2$ points$)$ How much more did Bob pay than Alice over the life of their loans? $($Hint:

be sure you filled out the entries in Column J for parts a$)$ and b$)$ as we can use this here!$)$

Problem $3.$ According to Realtor.com, the current median sell price for a home in Min$-$

neapolis is about $$325,000.$

a$)(4$ points, In Excel$)$ According to Google, the current average annual rate in Minnesota

for a loan of roughly this size with a $30$ year term and an excellent credit score is $7\mathrm{.}8\%.$ Build

an amortization table for the full $30$ year term of the mortgage with monthly payment as

determined in part a$).$

b$)(2$ points$)$ How many months until someone$$s monthly payment with this mortgage pays

more per month towards the principal than they do towards the interest?

c$)(3$ points$)$ What percentage of the total money paid over the life of the mortgage was

paid towards interest?

d$)(4$ points$)$ The $28\%$ Rule states that you should spend no more than $28\%$ of your

$($pre$-$tax$)$ household income on a mortgage. What is the minimum yearly household income

$($again$,$ pre$-$tax$)$ required to satisfy this rule and afford the mortgage from part a$)?$ Include

both the yearly income and the calculation you used to find this.