MATH $111$

Names:

Assignment $7$

General Directions:

You will be assigned a random partner for this assignment. Each partner will need to complete and upload the data file, and turn in their written answers..

You will need your laptop to complete the assignment.

In Canvas, open Assignment $7.$ There will be an Excel file which you need to download and save to your storage location. Open the file in Excel.

In this assignment, you are exploring the cost of financing a home. You will be comparing a $30$ year mortgage and a $15$ year mortgage. You should assume that the closing costs of the loans are the same.

There is a class sign$-$up sheet with values that you will need for the assignment. Place your names on any single blank line $($both names on the same line$)$ and record your values below:

Line # House Price: $$237,000$ Down Payment $\%$

$15$ yr APR: $5\mathrm{.}75\%30$ yr APR: $6\mathrm{.}5\%$

Problem $1-$ Complete the $15$ yr worksheet

Enter your values for the House Sale Price, Down Payment $\%,$ the APR in the blue boxes. $($the down payment percentage and the APR do not need to be converted to decimals.$)$

In cell H$2,$ enter a formula to compute the Amount of your Down Payment.

In cell H$3,$ enter a formula to compute the Amount of your Loan.

In cell $H4,$ enter a formula to compute the rate per compounding period.

In cell H$5,$ enter a formula to compute the total number of payments.

In cell H$6,$ use the $=$PMT function to compute the loan payment. Place a negative sign in front of PMT

In cell E$13,$ you need to enter the loan amount, but instead of typing the value, use a cell reference. In cell E$13,$ type $=H3$

In cell C$14,$ enter a formula to compute the interest for that month.

Interest $=$ Balance from previous month $$ rate

Note: Since the rate never changes, it should be specified absolutely

In cell D$14,$ enter a formula to compute the amount of your payment which will be applied to your principle. This is the payment minus the interest for the month. Note: we want to specify the payment amount absolutely since it never changes. Enter the formula:

$=$$$H$$$6-C14$

In cell E$14,$ enter a formula to compute the new Balance. This is the Balance from the previous month $($ce E$13)$ minus the Amount of the payment applied to the principle $($cell D$14).$

Select Cells C$14$ to E$14$ and copy into the remaining cells $($down to row $193).$

In cell $H8,$ use the $=$sum function to compute the total interest.

In cell $H9,$ compute the total amount of the payments by multiplying the payment amount by total number of payments.

rt the values from the following cells:

$H6=$

$H8=$

$H9=$