The partially completed workbook is attached below. The workbook is intended to calculate the car loan payments based on the price of the vehicle, the amount of the trade in value and the duration of the loan. The workbook will also break down payments between the amount that will go towards paying off the actual principle and amount of the payment that goes towards the interest payment. Notice that your early payments are going almost entirely to paying the interest of the loan. Conversely, the later payments are counted more towards paying the principal of the loan. The banks make sure that they make their money up front.

The partially completed workbook contains two worksheets that are attached to provide the information necessary to complete this project

Use cell references not cell values in all of the formulas. On the first worksheet:

A) Calculate the amount borrowed in Cell D14.

B) Develop the formula to calculate interest rate of the loan in cell G11. The formula must use the VLOOKUP function. It will base the Interest Rate on the value entered in cell G12 (G12 is the user entered duration of the loan in years) based on the table in the Information worksheet of this workbook.

C) In Cell G14, calculate the total number of loan payments (# Payment Periods).

D) In Cell C17, calculate the monthly payments based on paying at the beginning of the period Monthly payment

i. calculated using the PMT function

ii. using cell references not cell values

iii. shown as a positive number

iv. payment at beginning of payment period

E) In Cell C18, calculate the monthly payments based on paying at the end of the payment period Monthly payment

i. calculated using the PMT function

ii. using cell references not cell values

iii. shown as a positive number

iv. payment at end of payment period

F) In Cells D17 and D18 calculate the total amount of the loan based on the corresponding payment structure. (D17 total amount paid based on beginning of period payments D18 based on end of period payments.)

G) Loan payments are structured on a sliding scale of principle (what you borrowed) and interest (what you pay in order to borrow the money). The scale starts with more of the payment going towards interest than towards the principle borrowed.

The Cells C4 and C5 show the breakdown of how much of the payment is principle and how much is interest in the first payment.

The following cells will show the breakdown at the quarter, half, three quarters and last payment.

Cell D3 will calculate the payment number at the quarter point (25% point of paying back the loan). This is simply the total number of payments multiplied by 25%. E3 will then be the payment number at the half way point (50% point of paying back the loan). This is simply the total number of payments multiplied by 50%. F3 will then be the payment number at the three quarter point (75% point of paying back the loan). This is simply the total number of payments multiplied by 75%. Finally, G3 is the last payment. This will just be the total number of payments represents the value of the number of the last payment made.

Cells C4 and C5 have been prefilled with the function names (PPMT and IPMT) for the breakdown of principle and interest. Complete these formulas so they compute the values. Write the formulas so they can be copied to the corresponding cells (D4 to G5).

H) In cells C7 to G7 simply compute the totals of the principle and interest for each column to demonstrate that the combination of the two do indeed add up to the monthly payment (based on paying at the end of the period)

I) Format the first worksheet as follows; a. Display all dollar amounts with currency symbol and appropriate decimal places b. Merge and Center the label in B11 up to D11. Increase the font size and bold the label. c. Change the background of B11 to black and the text to white. d. Put a box outline in the range of B10 to D19. e. Merge and Center the label in F10 up to G10. Increase the font size and bold the label f. Change the background of F10 to grey and the text to red g. Put a box outline in the range of F10 to G14 and line all the boxes in that range h. Add your name to the prepared by in Cell B21 i. Highlight (change the background color) the cells in the range of B2 up to G2 and the range of B7 to G7 as shown in the image above. j. Put a box outline in the range of B2 to G7 and line all the boxes in that range k. Highlight (change the background colours ) of all the label rows so they look like the image above. (i.e. the dark red, red, blue and light orange cells). l. Change the font color of any cell that can be changed by the user to a blue color text. - Cells B14, C14, G12 and G13 m. Rename the worksheet labeled Sheet1 to Payments n. remove any extra (unused) worksheets from the workbook

D First Payment Breakdown 25% of Loan Paid Point 50% of Loan Paid Point 75% of Loan Paid Point Last Payment Breakdown 1 Breakdown of Payment: Payment Number Amount of the Principle Amount of the Interest Paid #NAME? #NAME? 5 6 Total 9 10 11 CAR PAYMENT CALCULATION TABLE 12 Terms of the Loan Interest Rate Years Payment Per Year # Payment Periods 13 Price of Vehicle Trade-In Value LOAN Amount 14 15 16 Monthly Payments Total Amount Paid Beginning of Pay Period End of Pay Period 18 Term of Loan (Years up to:) 2 3 | | Interest Rate 5.3% 4.8% 4.3% 4.1% 3.9% 3.6% 3.2% 6 D First Payment Breakdown 25% of Loan Paid Point 50% of Loan Paid Point 75% of Loan Paid Point Last Payment Breakdown 1 Breakdown of Payment: Payment Number Amount of the Principle Amount of the Interest Paid #NAME? #NAME? 5 6 Total 9 10 11 CAR PAYMENT CALCULATION TABLE 12 Terms of the Loan Interest Rate Years Payment Per Year # Payment Periods 13 Price of Vehicle Trade-In Value LOAN Amount 14 15 16 Monthly Payments Total Amount Paid Beginning of Pay Period End of Pay Period 18 Term of Loan (Years up to:) 2 3 | | Interest Rate 5.3% 4.8% 4.3% 4.1% 3.9% 3.6% 3.2% 6