Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

Explore

8. In cell I5, enter a formula using the IPMT function to calculate the amount of the first

month’s payment that is used for paying the interest on the loan.

9. In cell J5, enter a formula that calculates the amount of the principal remaining to be

paid. Ken would like this expressed as a positive value. To calculate this value, construct

a formula that is equal to the
negative
of the value in cell B4 (the amount of the loan)

minus the running total of the principal payments. To calculate a running total of the

principal payments, use the formula =SUM($H$5:H5). Note that this formula uses both

an absolute reference and a relative reference, much like the running total example in

the tutorial.

10. Using Auto Fill, copy the formulas in the range H5:J5 to the range H5:J64. (
Hint
: The

value displayed in cell J64 should be $0.00, indicating that the loan is completely paid

off. Also, the interest payment for the last month should be $13.11.)

11. In cell B12, enter a formula to calculate the total amount of payments made to the

principal in the first 12 months of the schedule. In cell C12, enter a formula to calcu-

late the total amount of the interest payments. In cell D12, enter a formula to calcu-

late the amount of the remaining principal. Once again, Ken wants this expressed as

a positive value, so the formula must subtract the value in cell B12 from the
negative

of the value in cell B4.

12. Repeat Step 11 for the range B13:D13, calculating the totals for the first 24 months. In

the range B14:D14, calculate the totals for the first 36 months. In the range B15:D15,

calculate the 48-month totals. In the range B16:D16, calculate the 60-month totals.

13. In the range B19:B22, enter a formula to calculate the amount of money Ken would

save in interest payments if he paid off the loan after one year, two years, three years,

and four years.

14. Preview the worksheet before printing it. Open the Page Setup dialog box, change the

page orientation of the worksheet to landscape orientation, and then select the option

so the worksheet will print on one page. Preview the worksheet again and then print it.

15. Save your changes to the workbook and then close it.

Explore

Explore

Internet Assignments

The purpose of the Internet Assignments is to challenge you to find information on the

Internet that you can use to work effectively with this software. The actual assignments are

updated and maintained on the Course Technology Web site. Log on to the Internet and

use your Web browser to go to the Student Online Companion for New Perspectives

Office 2003 at
www.course.com/np/office2003
.
Click the Internet Assignments link, and

then navigate to the assignments for this tutorial.

Research

Use the Internet to find

and work with data

related to the topics pre-

sented in this tutorial.

SAM Assessment and Training

If your instructor has chosen to use the full online version of SAM 2003 Assessment and

Training, you can go beyond the “just-in-time” training provided on the CD that accompa-

nies this text. Simply log in to your SAM account (
http://sam2003.course.com)
to launch

any assigned training activities or exams that relate to the skills covered in this tutorial.

Assess