Microsoft Office Tutorials and References

In Depth Information

**Figuring Loan Calculations**

Although the PMT function returns the constant periodic payback amount for

a loan, note that each payment actually consists of two portions. One portion

goes toward reducing the principal, and the other portion is the interest

payment. As if this weren’t already confusing enough!

You may notice some new terms when using this function: Pv, Fv, and Nper. In

financial terminology,
present value
(Pv) refers to the value of a transaction at

the present moment. When dealing with a loan, for example, the present value

is the amount you receive from the loan — in other words, the principal. The

term
future value
(Fv) refers to the value of a transaction at some point in the

future, such as the amount you’ll accumulate by saving $50 a month for five

years.
Nper
stands for the number of payment periods in the loan.

Calculating interest payments

The IPMT function tells you the interest payment for a given period. In each

payment period during a typical loan, the payment consists of a portion set

to reduce the principal of the loan, with the other portion of the payment

being the interest on the principal. The amount of interest varies payment by

payment. In a typical loan, the portion of the payment that is interest is

highest in the first period and is reduced in each successive period.

The IPMT function takes four inputs: the principal, the interest rate, the

number of payments for the loan, and the number of the payment you’re

interested in. For example, a loan may have 24 payments, and you’re

interested in how much interest is included in the 12th payment. For some types

of loans, the interest is tax deductible, so this information may literally be

worth something! Here are the steps to use the IMPT function:

1. Enter the following information in separate cells within a column on

the worksheet:

•Loanprincipal

•Annualinterestrate

•Numberofpaymentperiods

•Numberoftheactualperiodforwhichyouwanttocalculatethe

interest

You can add labels to adjacent cells to identify the values, if desired.

2. Position the cursor in the cell where you want the results to appear.

3. Enter
=IPMT(
to begin the function entry.