$$ In this project you are going to be creating a workbook that will calculate loan payments based upon information entered by the user.

$$ Starting in cell A$1$ created a table that allows a user to enter the information appearing in the next section. Title the table Loan Calculator.

$$ Enter the following rows in column A$,$ with the corresponding formulas in B$,$ if not a user defined amount.

o Purchase Price $($user defined$)$

o Down Payment $($user defined$)$

o Loan Amount $($Purchase Price $$ Down Payment$)$

o Interest Rate $($user defined$)$

o Number of Payments $($user defined$)$

o Monthly Payment $($use PMT; convert APR to monthly rate$)$

o Total Cost $($create a formula to add total of payment and down payment$)$

o Finance Charge $($calculate the total interest cost$)$

$$ In column D$,$ create a Two Input Data Table that will calculate the monthly payment based on a changing interest rate and different payments. Just Above the Data Table put the heading $$Payment Comparison Chart$.$ The Interest Rate should range from $5\%$ to $12\%$ with $1\%$ increments and run across the top. The number of payments should range from $12$ to $72,$ with $12$ payment increments and run along the side.

$$ Below these two tables $($around row $12)$ create a section of the spreadsheet that will build an amortization table. Title this $$Amortization Table$$ You will need the following columns: Payment Number, Payment Amount, Interest, Principal, Loan Balance.

$$ Leave a blank row between the heading and the start of your number for Payment Number. Be sure to include enough rows for the $72$ month loan.

$$ In the blank row put a cell reference in the Loan Balance column to reference to the Loan Amount in the Loan Calculator table.

$$ Use cell references and formulas to complete the amortization table. Make sure to format the calculation of your interest amount to $2$ decimal places. Remember, the Principal Amount$=$Payment$-$Interest; Loan Balance $=$ Previous Loan Balance$-$Principal. Also make sure you change the payment so that it shows as a positive number.

$$ In column F$,$ or the next open column in your amortization table, Place an if statement that returns a $1$ if the Loan Balance is less than $0,$ or a $0$ if FALSE starting in the row with your first payment number. Format this column so the text is white.

$$ Place a conditional formatting on the entire amortization table $($starting with the first payment number row$)$ using the formula value condition. The formula value will be $=$$F$14($or whatever the row is that you first payment number appears in$)=1.$ The conditional formatting is to change the text to White.

$$ Place conditional formula on the Payment comparison data table, so that if the number is equal to the monthly payment in the Loan calculator table, it will turn the text green and bolded.

$$ Bold all row and column headings. Change the cell color to Blue for the Loan Calculator title with White Text, Change the cell color to Green for the Payment Comparison Chart title with White Text, Change the cell color to Red for the Amortization Table Title with White Text. Resize the titles to $20$ and bold them, as well as center them over the tables.

$$ In the Loan Calculator, Place $$Thick Box$$ borders around the cells that are not user defined.

$$ Apply formatting so that numbers are formatted properly as currency, percents, etc. All currency should be $2$ decimal places. Percentages should have $0$ decimal places.

$$ Apply Protection to the worksheet, so the only cells that can be changed are the $$User defined$$ cells.

$$ Print each table as a separate item after filling in the following info: Purchase Price $=$ $$20,000\mathrm{.}00,$ Down Payment $=$ $$4000\mathrm{.}00,60$ payments and an interest rate of $7\%.$