Microsoft Office Tutorials and References
In Depth Information
9. Rework the analysis, assuming that the cost of the Premier plan has risen to $7,000
and the cost of the Standard plan has risen to $6,100.
10. Print the revised Payroll worksheet.
11. Save your changes to the workbook, and then close it.
Case Problem 4
Data File needed for this Case Problem: Soup1.xls
The Soup Shop Ken Novak is the owner of a diner in Upton, Ohio, named The Soup
Shop. Business has been very good lately, so Ken is considering taking out a loan to cover
the cost of upgrading and expanding the diner. Ken wants your help in creating an Excel
workbook that provides detailed information about the loan. He would like the workbook
to calculate the monthly payment needed for a five-year, $125,000 loan at 6.5% interest.
Ken believes that the expansion will increase business, so he also wants to know how
much he would save on interest payments by paying off the loan after one, two, three, or
Go beyond what you
learned in Session 2.2.
Use the PMT, PPMT, and
IPMT functions to create
a payment schedule
for a small business.
To do this type of calculation, you need to know what part of each monthly payment is
used to reduce the size of the loan (also referred to as payments toward the principal) and
what part is used for paying interest on the loan. Excel provides two functions to calculate
these values, both of which are similar to the PMT function used to calculate the total
monthly payment. To calculate how much of a monthly payment is used to pay off the
principal, you use the PPMT function, which has the following syntax:
=PPMT( rate , period , nper , pv [, fv =0] [, type =0])
where rate is the interest rate period, period is the payment period you want to examine
(such as the first period, the second period, and so forth), nper is the total number of pay-
ment periods, pv is the amount of the loan, fv is the future value of the loan (assumed to
be zero), and type indicates whether the payment is due at the beginning ( type =1) or at
the end ( type =0) of the month. The function to calculate how much of the monthly pay-
ment is used for paying the interest is the IPMT function, which has a similar syntax:
=IPMT( rate , period , nper , pv [, fv =0] [, type =0])
As with the PMT function, the value of the pv argument should be negative when you are
working with loans—as you are in this case.
Ken wants you to use these two functions to create a payment schedule that indicates for
each of the 60 months of the loan, how much of the monthly payment is being used to
pay off the loan and how much is being used to pay interest on the loan. You can then use
this schedule to discover how much Ken could save in interest charges by paying off the
loan early. Figure 2-36 shows the worksheet as it will appear at the end of this exercise.