Chapter 15: Using Formulas for Financial Analysis
15
The PV function accepts five arguments:
Rate Also known as the discount rate, the rate argument is the return you think you
could make on your money over the discount period. It is the biggest factor in determin-
ing the present value, and it can also be the hardest to determine. If you’re conservative,
you might pick a lower rate—something you’re sure you can achieve. If you were to use the
money to pay off a loan with a fixed rate, the discount rate would be easy to determine.
nper The nper is the period of time to discount the future value. In this example, the
nper is 1 year and is entered in cell C3. The rate and the period must be in the same units.
That is, if you enter an annual rate, nper must be expressed as years. If you use a monthly
rate, nper must be expressed as months.
pmt The pmt argument is the regular payments received over the discount period. When
there is only one payment, as in this example, that amount is the future value and the
payment amount is zero. The pmt argument must also the match the nper argument. If
your nper is 10 and you enter pmt, PV assumes that you’ll get that payment amount 10
times over the discount period. The next example shows a present value calculation with
payments.
FV The future value amount is the amount you will receive at the end of the discount
period. Excel’s financial functions work on a cash flow basis. That means the future value
and present value have opposite signs. For this example, the future value was made nega-
tive so that the formula result would return a positive number.
type The type argument can be 0 if the payments are received at the end of the period or
1 if the payments are received at the beginning of the period. This argument has no effect
on this example because our payment amount is 0. The type argument can be omitted in
which case it is assumed to be 0.
Calculating the present value of future payments
Another use of PV is to calculate the present value of a series of equal future payments. If,
for example, you owed $5,000 of rent for an office over the next 10 years, you can use PV to
calculate how much you would be willing to pay to get out of the lease. Figure 15.20 shows
the present value calculation for that scenario.
=PV(C4,C3,-C2,0,1)If your landlord thought he could make 3% on the money, he might be willing to accept
$43,930 instead of ten $5,000 payments over the next 10 years. The type argument is set to
1 in this example because rents are usually made at the beginning of the period.
When used on payments, the PV function is actually taking the present value of each pay-
ment individually and adding up all the results. Figure 15.20 shows the calculation broken
out by payment. The first payment’s present value is the same as the payment amount
because it’s due now. The Year 2 payment is due one year from now, and it is discounted to
$4,854. The last payment, due nine years from now, is discounted to $3,832. All of the pres-
ent value calculations are added up. Fortunately, PV does all the heavy lifting for you.