Page 394 - Excel 2013 All-in-One For Dummies

376 The PV, NPV, and FV Functions
✦ NPER is the total number of payment periods in the life of the annu-
ity. You calculate this number by taking the Term (the amount of time that interest is paid) and multiplying it by the Period (the point in time when interest is paid or earned) so that a loan with a 3-year term with 12 monthly interest payments has 3 x 12, or 36 payment periods.
When using financial functions, keep in mind that the fv, pv, and pmt arguments can be positive or negative, depending on whether you’re receiving the money (as in the case of an investment) or paying out the money (as in the case of
a loan). Also keep in mind that you want to express the rate argument in the same units as the nper argument, so that if you make monthly payments on a loan and you express the nper as the total number of monthly payments, as in 360 (30 x 12) for a 30-year mortgage, you need to express the annual interest rate in monthly terms as well. For example, if you pay an annual interest rate of 7.5 percent on the loan, you express the rate argument as 0.075/12 so that it is monthly as well.
The PV, NPV, and FV Functions
The PV (Present Value), NPV (Net Present Value), and FV (Future Value) functions all found on the Financial button’s drop-down menu on the Ribbon’s Formulas tab (Alt+MI) enable you to determine the profitability of an investment.
Calculating the Present Value
The PV, or Present Value, function returns the present value of an investment, which is the total amount that a series of future payments is worth presently. The syntax of the PV function is as follows:
=PV(rate,nper,pmt,[fv],[type])
The fv and type arguments are optional arguments in the function (indicated by the square brackets). The fv argument is the future value or cash balance that you want to have after making your last payment. If you omit the fv argument, Excel assumes a future value of zero (0). The type argument indi- cates whether the payment is made at the beginning or end of the period: Enter 0 (or omit the type argument) when the payment is made at the end of the period, and use 1 when it is made at the beginning of the period.
Figure 4-1 contains several examples using the PV function. All three PV functions use the same annual percentage rate of 1.25 percent and term of 10 years. Because payments are made monthly, each function converts these annual figures into monthly ones. For example, in the PV function in cell E3, the annual interest rate in cell A3 is converted into a monthly rate by dividing by 12 (A3/12). The annual term in cell B3 is converted into equivalent monthly periods by multiplying by 12 (B3 x 12).