Question:In cell C5, enter the function =PMT to calcul

ate the monthly payment on a

loan of $96,500 (cell C2) at 2.9% (cell C3) for 72 months (cell C4). Format it

as shown in Figure E1. Note that you will have to divide the rate by 12

because the formula takes years and not months.

Perform the following

tasks:

[1]

Select the entire Excel workbook and change the font size to 12.

[2]

Change the Column Widths of: A to 10.00; B, C to 18.00; D to 17 and E to

16.00.

[3]

Change the Row He

ights of

1 and 6 to 30.00 and E79 to 20.00.

[4]

In cell A1, enter the workbook title, Car

Loan Schedule, Merge and Center

cell

.

[5]

A1 across the Range A1:E1. Change the font type to Verdana, the font size to

18, bold, the font color to Blue Accent 1 -

Darker 50%, and the background

to Blue Accent 1 -

Lighter 60%.

[6]

Enter the following text labels: In cell B2 type Principal; cell B3 type Rate;

cell B4 type Months; cell B5 type Payment; cell D5 type per month; cell A6

type Month; cell B6 type Beginning Balance; cell C6 type Ending Balance; cell

D6 type Total Paid; cell E6 type Interest. Note: bold the

text labels. Change

the font color to Blue Accent 1 -

Darker 50%.

[7]

In cell E2, enter the function =Now() to display the current date and format

as shown in Figure 1. Note: The date will be different from that in Figure 1.

[8]

In cell C2, enter the loan amount of $96,500. Format the cell to currency

format with no decimal places.

[9]

In cell C3, enter the Interest Rate of 2.9%. Format the cell as shown in

Figure 1.

[10]

In cell C4, enter the number 72 for the number of Years.

[11]

In cell C5, enter the function =PMT to calcul

ate the monthly payment on a

loan of $96,500 (cell C2) at 2.9% (cell C3) for 72 months (cell C4). Format it

as shown in Figure E1. Note that you will have to divide the rate by 12

because the formula takes years and not months.

[12]

Use the fill series (under H

ome

Editing

Fill) to fill the range A7:A78

with the numbers 1 through 72. Make sure that you click on Columns and the

Stop Value is 72.

CGS2100C: Computer Fundamentals and Applications

Final Project

4

[13]

In cell B7, enter the formula =C2 to reference the Loan Amount, which is

the beginning balance for Month 1.

[14]

In cell C7, enter the function =FV to determine the ending balance for year

1. Note that you will need to do =

-FV to make it positive. Also make sure you

have your absolute and relative reference correct. Also note that since we are

doing months the nper in the fo

rmula will be 1.

[15]

In cell D7, enter the formula =

-$C$5 to determine the Annual Amount Paid

on the loan.

[16]

In cell E7, enter the formula =D7 -

(B7

- C7) to calculate the amount of

Interest paid for the month.

[17]

In cell B8, enter the formula =C7 to obtain the Beg

inning Balance for month

2.

[18]

Copy this formula to the range B9:B78.

[19]

Copy the function =FV entered in cell C7 to the range C8:C78.

[20]

Copy the formula in cell D7 to the range D8:D78.

[21]

Copy the formula in cell E7 to the range E8:E78. If all is done properly, the

value in cell C78 should be zero.

[22]

Use the function =SUM in cells D79 and E79 to sum the payment and

interest amounts.

[23]

Put a double border on cells D78 and E78 and increase the font of D79 and

E79 to 14

[24]

In cell A81, enter your first and last n

ame. In the cells directly beneath your

name, enter your course CRN number, assignment name (Excel Module 4 -

[25]

Analyzing and Charting Financial Data), date, and instructor's name (Prof.

Sapijaszko).

[26]

Rename the Sheet 1 tab to Car Loan.

[27]

Save the workbook usin

g the file name (your last name -

Excel Module 4 -

Financial Functions).