Microsoft Office Tutorials and References
In Depth Information
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
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.
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
then navigate to the assignments for this tutorial.
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.