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.
Search JabSto ::




Custom Search