Merced College; Don Power

Lab 4: Data Entry and Formulas in Microsoft Excel NAME __________________

The purpose of this lab is to familiarize you with the basic features of Microsoft Excel.

The specific task is to develop a payment schedule for an auto loan. Suppose you are going to purchase a car and you have to finance $23,700. You expect to pay 7.5% interest, and the loan is to be paid off in 5 years. You will use Excel to find the monthly payment.

First, open Excel and make the following entries: (Note the buttons to increase or decrease decimals).

Now enter the following amounts and formulas. Formulas begin with =

After entering a formula, press ENTER (not an arrow key); the result will be converted to a number.

To review a formula, click on the cell and read the “formula” bar (above column C).

Explanation:

C3: Divide the interest rate (in cell C2) by 12 (This converts the annual rate to a monthly rate).

(We will explain the $ in front of the 2 later).

D3: For the first month’s interest, multiply the principal (in B3) by the rate (in C3).

E3 Round the interest amount to the nearest penny.

F2: The 200 is just a guess at the required monthly payment. We will experiment with this later.

F3: Copy the standard monthly payment (in F2) as the first month’s payment.

G3: The new balance is the old balance (in B3) plus interest (in E3) minus the payment (in F3).

Round the result to the nearest penny.

A4: We want a 2 (for month #2); add 1 to the previous month (in A3).

B4: The beginning balance for month 2 is the same as the ending balance for month 1 (in G3).

Click on each cell with a money amount and adjust the decimals displayed to show dollars and cents. Then highlight cells C3 through G3 by clicking (and holding) when the cursor is in the center of C3; then drag the cursor to the right. When they are all highlighted, release the mouse button.

Your screen should look like this:

Notice the “handle” at the lower right corner of the highlighted area.

Move the pointer to the handle (so that the cursor changes from a hollow + to a solid +);

then click and hold with the mouse and drag the handle down one block.

Result:

To review the formulas in cells C4 through G4, click on each cell and read the formula in the “formula” bar (above column C). You should have the following formulas and entries at this point:

Notice that when you drag a formula down one row, you change all the row references (except the row numbers that were marked with a $ symbol). Thus, without any extra typing, we now have the ending balance for the second month.

Now that the bottom row consists entirely of formulas, we can drag the entire down to the 5-year point (we will be at month 60, so we need to drag all our formulas down to row 62). Either:

a. Highlight cells A4 through G4 and drag them down as we did before; or

b. Click on the row number to highlight the entire row. Notice that the handle is now next to the row number in column A. Grab the handle here and drag down to row 62. (Row 62 will show month 60, since there are two rows of headings at the top of the worksheet)

Result: A 5-year payment schedule. Problem: After 5 years, our balance is still $19,937.66.

Go back to the top of the worksheet and change the payment amount in F2 to $600. When you press ENTER, the entire worksheet gets updated. Look at the final balance. Did the loan get paid off?

With a negative final balance, it appears that the loan got paid off early. Scroll up to the last positive balance; You should see that if you pay 600 per month, you will pay off the loan in the 46th month with a final payment of 301.87.

Suggestion: You can split the worksheet at about row 5 to see the top and bottom at the same time.

Click on any cell in row 5; Select WINDOW and then SPLIT from the EXCEL menus at the top of the screen.

Then use the scroll bars in the bottom half of the screen to get the last month in view:

You can now experiment with the payment amount in F2 to get the ending balance to reach 0 (or slightly negative - you want to pay off the loan, so you don't want a positive balance here) at the end of the last (60th) month.

Then adjust the final month's payment (highlighted below) to pay off the loan exactly: in this case, reduce the final payment by $0.03 to $474.87.

ASSIGNMENT

1.Problem #1: Use trial and error to find the smallest monthly payment amount (correct to the nearest penny) that will pay off a $25900 loan (at 6.6%) in the 60th month.

• Start with a blank worksheet, and with the new starting balance of $25900, the new interest rate of 6.6%, and a new guess at the monthly payment
• You will look for the monthly payment is the smallest amount that will make the balance in month #60 zero or negative.
• All money amounts, including your answer, should be correct to the nearest cent.

· Adjust the final month’s payment if necessary to make the final balance come out to exactly $0.00.

• Use a split worksheet.
• Type your name at the top of your worksheet (in cell H1).

DO NOT PRINT THE WORKSHEET IN EXCEL. Instead:

• Press the "Print Screen" button on the keyboard
• Open a word processing program such as Microsoft Word or wordpad
• Select "Edit ... Paste;" the Excel screen image will now appear in the word processor.
• Print the word processor document.

In ink, circle your initial starting balance, regular monthly payment and the final month’s payment.

2. Problem #2. Find the largest starting balance you can handle if you want to pay off a loan in 4 years at an 8.5% interest rate with monthly payments of $380.00. This time you will leave the monthly payment fixed at $380 and use trial and error with the starting balance instead.

• Start with a blank worksheet, with a guess for the starting balance, the new interest rate of 8.5%, and the new monthly payment of $380

• All money amounts, including your answer, should be correct to the nearest cent.
• Adjust the final month’s payment if necessary to make the final balance come out to exactly $0.00.
• Type your name at the top of your worksheet.
• Print the worksheet the same way you did in Problem 1.
• In ink, circle your initial starting balance, regular monthly payment and the final month’s payment.

Return to:  Merced College; Don Power               Updated 04/17/07 by Don Power