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