Microsoft Office Tutorials and References
In Depth Information
The formula returns \$92.43 for the principal, which is about 78.7% of the total loan payment. If I change the
second argument to 48 (to calculate the principal amount for the last payment), the formula returns \$116.84, or
about 99.5% of the total loan payment.
To calculate the cumulative principal paid between any two payment periods, use the
CUMPRINC function. This function uses two additional arguments: start_period and
end_period. In Excel versions prior to Excel 2007, CUMPRINC is available only when
you install the Analysis ToolPak add-in.
IPMT
The IPMT function returns the interest part of a loan payment for a given period, assuming constant payment
amounts and a fixed interest rate. The syntax for the IPMT function is
IPMT(rate,per,nper,pv,fv,type)
The following formula returns the amount paid to interest for the first month of a \$5,000 loan with a 6% annual
percentage rate. The loan has a term of four years (48 months).
=IPMT(6%/12,1,48,-5000)
This formula returns an interest amount of \$25.00. By the last payment period for the loan, the interest payment
is only \$0.58.
To calculate the cumulative interest paid between any two payment periods, use the
CUMIPMT function. This function uses two additional arguments: start_period and
end_period. In Excel versions prior to Excel 2007, CUMIPMT is available only when you