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

install the Analysis ToolPak add-in.

RATE

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

RATE(nper,pmt,pv,fv,type,guess)

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.

=RATE(48,117.43,-5000)*12