Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

Figure 2-36

To complete this task:

1. Open the
Soup1
workbook located in the Tutorial.02\Cases folder included with

your Data Files, and then save the workbook as
Soup2
in the same folder.

2. Enter your name and the current date in the Documentation sheet.

3. Switch to the Loan worksheet, and then in the range B4:B8, enter the following

loan details:

• Loan Amount = –125,000

• Years = 5

• Periods per Year = 12

• Interest Rate = 6.5% (annually)

In cell B7, enter a formula to calculate the total number of payment periods.

4. In cell B9, enter a formula using the PMT function to calculate the total monthly pay-

ment required to pay off the loan. Assume that payments are made at the beginning of

each period,
not
at the end, which is the default. (
Hint
: Use the
fv
and
type
arguments.)

5. In the range F5:F64, enter the numbers 1 through 60 using Auto Fill. Each number

indicates the payment period in the payment schedule.

6. Ken would like his payment schedule to include the dates on which the payments are

due. In cell G5, enter the date 4/1/2006. This is the due date for the first payment. In

cell G6, enter the date 5/1/2006. This is the due date for the second payment. Use the

Auto Fill to enter the rest of the due dates into the range G7:G64.

7. In cell H5, enter a formula using the PPMT function to calculate the amount of the

first month’s payment devoted to reducing the principal of the loan. The details of the

loan should reference the appropriate cells in the B4:B8 range of the worksheet using

absolute references. The period number should reference the value in cell F5 using a

relative reference. Be sure to indicate in the function that the payments are made at

the beginning, not the end, of the month.

Explore