Microsoft Office Tutorials and References

In Depth Information

**Mastering Excel’s Built-in Functions**

You can use this function to calculate your monthly payment for a car loan or home

mortgage. The first three arguments are required. If the interest rate (rate) is expressed in annual

terms, you must divide by 12 to calculate a monthly payment. Likewise, you need to express

the number of periods (nper) in months—so a 30-year loan is 360 months. Finally, the

present value (pv) is the loan amount. Because you’re the borrower, it needs to be expressed as

a negative number. You can omit the two final arguments: future value (fv) is the amount

you’ll owe when the loan is paid off, or 0. And the type argument calculates whether you

make payments at the beginning or end of the period. (The latter is the default if the

argument is omitted.)

Here is the complete formula for a five-year loan of $20,000 at a stated interest rate of 5%:

And here are two variations on that basic formula, using different functions to solve for a

different variable:

●
NPER(rate,pmt,pv,fv,type) Use this formula to calculate how many payments you

need to make to fully pay off a credit card balance at a given payment. You can enter

the minimum required payment for the pmt argument and then increase the amount

to see how quickly extra payments accelerate the payoff. You can perform this what-if

analysis in the Function Arguments dialog box, or you can set up input cells for each

variable you want to play with and use references to those cells in the formula.

●
RATE(nper,pmt,pv,fv,type) If a loan requires an up-front payment of fees or points,

add that amount to the principal amount and plug that into the pv argument. The

result tells you how much those fees add to the effective interest rate compared to

the stated rate for the loan.

To calculate how much of a specific payment is interest and how much is principal,

use the IPMT and PPMT functions, respectively. The syntax for the IPMT function is

IPMT(rate,per,nper,pv,fv,type). Enter the payment number (per) to calculate the portion of

that payment that is interest. Use the same syntax with the PPMT function (or subtract the

result of the IPMT calculation from the result of the PMT calculation) to figure the principal.