Financial Functions in Excel

Let’s consider a loan with monthly payments, an annual interest rate of 6%, 10-year duration, a present value of $250,000 (amount borrowed) and a future value of 0 (that's what you hope to achieve when you pay off a loan).

We make monthly payments, so we use 6%/12 = 0.5% for Rate and 10*12 = 120 for Nper (total number of periods). If we make annual payments on the same loan, we use 6% for Rate and 10 for Nper.

PMT

Select cell A2 and insert the PMT function.

=PMT(rate, nper, pv, [fv],[type])

Here, last two arguments are optional. For loans the FV can be omitted (the future value of a loan equals 0, however, it's included here for clarification).

If Type is omitted, it is assumed that payments are due at the end of the period.

Result. The monthly payment equals $2775.51.

RATE

If Rate is the only unknown variable, we can use the RATE function to calculate the interest rate.

=RATE(nper, pmt, pv, [fv], [type])