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.
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
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).
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
install the Analysis ToolPak add-in.
The RATE function returns the periodic interest rate of a loan, given the number of payment periods, the period-
ic payment amount, and the loan amount. The syntax for the RATE function is
The following formula calculates the annual interest rate for a 48-month loan for $5,000 that has a monthly pay-
ment amount of $117.43.