$\backslash$table$[[,,$Spreadsheet Assignment $-$ Home Loan,,$],[$Input cells:,Time periods,$360,[=($# of Years$)*($# of Payments Per Year$)],],[$Interest Rate,$0\mathrm{.}0033333,($Annual Rate$)/($# of Payments Per Year$)],],[$Cash Price $=$ PV$,400,000\mathrm{.}00,,,],[$Annuity Amount,$$1,909\mathrm{.}66,,,],[,,,,],[,$Schedule of Compount Interest $($First $2$ Years Only$).,],[,,,,,],[$Period$,$Beginning Balance,Payment Amount,Interest Portion,Principal Portion,Ending Balance$],[1,400,000\mathrm{.}00,1,909\mathrm{.}66,1,333\mathrm{.}33,576\mathrm{.}33,399,423\mathrm{.}67$

each part, only show $2$ years of payments $($which should fit on one page $$ landscape orientation, as long as

the page is formatted correctly$).$

Part A:

On January $1,2018,$ ABCDEF Mortgage Company granted Mega BearFan a home loan in the amount of $$400,000,$

at $4\%$ annual percentage rate $($APR$)$ interest, for $30$ years. Monthly payments of $$1,909\mathrm{.}66$ are to be made, at the

end of each month, starting January $31,2018($since the payment is due at month end, it an ordinary annuity$).$

Using this data, you are to do the following.

$1)$ Replicate the attached $$CH$14$ Project Spreadsheet$($with or without the colors$).$ You will use this same

spreadsheet to answer Part B and part C$,$ so you should design it such that the only numbers you have to retype are the $4$ input cell numbers $($highlighted in green$).$ ALL other cells in the spreadsheet $($those highlighted in

yellow$)$ should consist of formulas used to generate the numbers. You should format all cells to include

both a comma $(,)$ between thousands and $2$ places after the decimal to denote cents $($hundredths$).$ Also,

you will need to replicate the header and the footer.

$2)$ Compute the total interest cost for Mega BearFan for the years $2018$ and for $2019$ so that Mega can use

these numbers in the tax returns for those years. Make a formula to total the $12$ cells for $2018,$ and another

formula to total the cells for $2019.$

$3)$ Insert a heading on your spreadsheet with your name, CH$14$ Project, and Part A$,$ like how it is done in

the attached CH$14$ Project Spreadsheet. Also, please replicate the footer. In addition, label the tab

$$Part A$.$

Part B:

Make a copy of your $$Part A$$ tab and label the tab $$Part B$.$ Make changes to the spreadsheet that you

created in Part A to answer the following questions. Mega BearFan$$s local bank said they could lend the

$$400,000$ for a period of $15$ years at an APR of $4\mathrm{.}325\%.$ Compute the monthly payment on this loan. Compute

the monthly payment cell $($the $$Annuity Amount$)$ by using a payment command in that cell of your

spreadsheet. In Excel, the formula is $=$PMT$($Rate$,$Nper,Pv$,$Fv$,$Type$).$ This can be accessed by the following

sequence of commands: Formulas, Financial, $($and then scrolling down to$)$PMT$.$ Here are some hints for

completing your formula $($in Excel$)$:

$$ For the Rate, I typed C$4,$ which was the cell number where I had stored the interest rate in my spreadsheet

program.

$$ For Nper, I typed in C$3,$ which was the cell number where I had stored the number of payment periods that the

loan would last.

$$ The PV is the cost of the house, cell C$5$ in my spreadsheet. This must be keyed into the formula as a negative

number to have the $$Annuity Amount$$ be positive for the spreadsheet. So$,$ I typed $-$C$5$ into my formula.

$$ For FV$,$ I typed $0,$ because there is no future value in this example.

$$ Type is either $0$ or $1.$ The $0$ is for an ordinary annuity, and the $1$ is for annuity due.

Please again include $2$ years of payments and total interest expense for both years on your tab for Part B$.$ I will

confirm that you have used correct formulas in the grading process.

Part C:

Make another copy of your $$Part A$$ tab and label the tab $$Part C$.$ Make changes to the spreadsheet that

you created in Part A to address the following. $($Note: You should change the title within the spreadsheet as

well.$)$

Mega BearFan now wants a new car $$ in Missouri State Maroon, of course. The dealer said he could put Mega

BearFan into a new Toyota Prius $($in Missouri State Maroon$)$ for $$499\mathrm{.}99$ per month, for $5$ years, on a dealer

financing plan at an APR of $3\mathrm{.}25\%.$ Compute the selling price $($or present value$)$ of the car using an ordinary

annuity. Do this by typing a present value command in the spreadsheet cell labeled $$Cash Price $=$ PV$.$ The form of

the command in Excel is $=-$PV$($Rate$,$ Nper, Pmt$,$ Fv$,$ Type$).$ It is negative to get a positive selling price in the

spreadsheet. Think about the hints provided in Part B in completing Part C$.$

Please again include $2$ years of payments and total interest expense for both years on your tab for Part C$.$ I will confirm that you have used correct formulas in the grading process. Extra credit: while it is acceptable to input the values into the green cells, think about way s that you can use excel to calculate some of those values for you. if you can minimize the number of typed numbers $($by using excel formulas instead$),$ you can earn up to an additional $5$ points extra credit.