Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

3.
Click cell
B6
, click the
AutoSum
button on the Standard toolbar, and then press the

Enter
key. Excel inserts the formula
=SUM(B4:B5)
into cell B6 to calculate the total average

monthly income.

4.
Click cell
B20
, click the
AutoSum
button on the Standard toolbar, and then press the

Enter
key. Excel inserts the formula
=SUM(B9:B19)
into cell B20 to calculate the total aver-

age monthly expenses.

5.
Click cell
B22
, type
=B6-B20
to calculate the average monthly net income, and then press

the
Enter
key.

Now that you’ve entered the average monthly income and expense values for

Amanda’s budget and have widened the columns, you can enter the conditions for the

loan. When you enter the amount of the loan, you will enter it as a negative value rather

than as a positive value. The reason that you enter it as a negative value is because the

loan is the amount owed to the lending institution; therefore, it is an expense. As you’ll

see later, Excel’s Financial functions require loans to be entered as negative values

because they represent negative cash flow. You will enter the labels and the conditions in

columns D and E.

To enter the conditions of the loan in the worksheet:

1.
Click cell
D3
, type
Loan Conditions
, and then press the
Enter
key to move to the next row

where you will enter the Loan Amount label and the loan amount as a negative value.

2.
Type
Loan Amount
in cell D4, press the
Tab
key, and then enter
-175,000
in cell E4.

Next you will enter the length of the loan in years.

3.
Type
Length of Loan
in cell D5, press the
Tab
key, and then enter
30
in cell E5.

Now you will enter the annual interest rate, which is 5.5%.

4.
Type
Annual Interest Rate
in cell D6, press the
Tab
key, and then enter
5.5%
in cell E6.

Note that Excel may enter a zero, which doesn’t change the value of the percentage.

Next, you will enter the conditions under which the loan is to be repaid. In this case, you

will assume that payments are due monthly.

5.
Click cell
D8
, type
Payment Conditions
, and then press the
Enter
key.

You will enter the number of payments to be made each year, which is 12.

6.
Type
Payments per Year
in cell D9, press the
Tab
key, type
12
in cell E9, and then press

the
Enter
key.

Next you will enter the formula to calculate the total number of payments required to pay

back the loan, which is the length of the loan (found in cell E5) multiplied by the payments

per year (found in cell E9).

7.
Type
Total Payments
in cell D10, press the
Tab
key, type
=E5*E9
in cell E10, and then

press the
Enter
key.

8.
Type
Payment Amount
in cell D11, and then press the
Tab
key.

Before you continue, you will widen the columns so information is clearly visible.

9.
Increase the width of column D to
18
characters (
131
pixels) and the width of column E to

10
characters (
75
pixels). Figure 2-25 shows the Loan Analysis worksheet with the values,

loan conditions, and payment conditions entered.