Microsoft Office Tutorials and References
In Depth Information
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
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.