Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

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

four years.

Challenge

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.