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
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.