PV

Returns the present value of a stream of future payments with a final lump sum.

Syntax:

PV(rate, numperiods, payment, futurevalue, type)

rate: the (fixed) interest rate per period.

numperiods: the total number of payment periods in the term.

payment: the payment made each period. If futurevalue is given, this may omitted (defaults to 0).

futurevalue: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0; with a bond this would be the redemption value.

type: when payments are made (optional - defaults to 0):

0 - at the end of each period.

1 - at the start of each period (including a payment at the start of the term).

The value of money is time-dependent; for example, $100 today would be worth $110 in a year if invested at a 10% interest rate.

PV returns the value today, of a payment being made each period for numperiods periods, with an additional lump sum payment (futurevalue) at the end of the term, at fixed rate interest, compounded each period.

The examples below clarify how the function may be used.

Example:

PV(5%, 15, 1000, 0, 0)

returns -10,379.66 in currency units. You have the opportunity to buy an annuity, which would pay you 1000 at the end of each year for 15 years. You assume a constant interest rate of 5%. On this basis, the annuity is worth 10,379.66 today; if it is priced higher than this you might not wish to buy it. The result is negative, because you must pay for the annuity.

PV(5%/12, 3*12, -100, 0, 0)

returns 3,336.57 in currency units. You are considering a loan of 3,500, which would mean you paying back 100 at the end of each month for 3 years. You assume a constant interest rate of 5%. On this basis, the loan is only worth 3,336.57, so might be a poor deal. The monthly payment is negative because you pay it, and the result positive because the loan is paid to you.

PV(7%/2, 3*2, 100, 1000, 0)

returns -1,346.36 in currency units. A 3 year bond is issued, paying 100 currency units semiannual coupons and 1000 currency units on maturity. You require an annual rate of return of 7% (compounded semiannually) bearing in mind any risk, and hence you value the bond on issue at 1,346.36. The result is shown as negative, as you would have to pay for the bond.

Application:

Calculating the Present Value of a Real Estate Investment

Let's imagine you are considering purchasing a commercial property that is currently leased to a stable tenant. You want to determine if the asking price is reasonable based on the income the property will generate over the next 10 years, plus its estimated resale value. This is a perfect use case for the PV (Present Value) function.

The Scenario:

Property Type: Commercial Office Building
Lease Term: 10 years
Annual Rental Income: The tenant has agreed to pay $50,000 per year for the next 10 years. This payment is received at the end of each year.
Estimated Resale Value (Future Lump Sum): At the end of the 10-year period, you estimate the property can be sold for $600,000.
Your Required Rate of Return (Discount Rate): You want to earn a 6% annual return on your investment. This is your personal benchmark to account for inflation, risk, and what you could earn from alternative investments.

The Goal:

To find the Present Value (PV) of this investment. This number represents the maximum you should be willing to pay for the property today to achieve your desired 6% return.

PV Function Breakdown:

The PV function would look something like this:

PV(rate, nper, pmt, [fv], [type])

rate: The interest rate per period. In our case, it's our annual required rate of return, which is 6% or 0.06.
nper: The total number of payment periods. Here, it's 10 years.
pmt: The payment made each period. This is the annual rental income of $50,000.
fv: The future value, or a cash balance you want to attain after the last payment is made. This is the estimated resale value of $600,000.
type (optional): When payments are due. We will assume payments are made at the end of each period, which is the default, so we can omit this.

Table of Cash Flows:

Here's a table illustrating the cash flows we are discounting back to the present. The PV function essentially performs a series of calculations to discount each of these values.

	Year	Annual Rental Income (PMT)	Future Value (FV - Resale)	Total Cash Flow
	A	B	C	D
1	1	$50,000.00	-	$50,000.00
2	2	$50,000.00	-	$50,000.00
3	3	$50,000.00	-	$50,000.00
4	4	$50,000.00	-	$50,000.00
5	5	$50,000.00	-	$50,000.00
6	6	$50,000.00	-	$50,000.00
7	7	$50,000.00	-	$50,000.00
8	8	$50,000.00	-	$50,000.00
9	9	$50,000.00	-	$50,000.00
10	10	$50,000.00	$600,000.00	$650,000.00

Calculating the Present Value:

The PV function calculates two parts and then adds them together:

Present Value of the Annuity (Annual Payments): This discounts the series of \$50,000 payments back to the present.
- PV(0.06, 10, 50000) ≈ -$368,004.35
Present Value of the Lump Sum (Future Resale): This discounts the single \$600,000 future value back to the present.
- PV(0.06, 10, 0, 600000) ≈ -$335,036.87

The Result:

Using the full PV function:

PV(0.06, 10, 50000, 600000)

The result would be approximately -$703,041.22.

Result of PV(0.06, 10, 50000):

-$368,004.35

Result of PV(0.06, 10, 0, 600000):

-$335,036.87

Result of PV(0.06, 10, 50000, 600000):

-$703,041.22