Excel PV Function
PV is one of the most important financial functions in Excel which calculates (a) the present value of a finite stream of equidistant equal cash flows at a constant interest rate over a specific period or (b) present value of a single cash flow at a specific time in future at constant interest rate. By default, the function assumes the cash flows to be an annuity i.e. periodic cash flows are assumed to occur at the period end. However, it can be used to calculate present value of annuity due i.e. stream of cash flows which occur at the start of the period.
Following are a few scenarios in which PV function is useful:
- An accountant can use it to calculate the present value of minimum lease payments
- A fixed income analyst can use it to calculate price of a bond
- An equity analyst can use it to calculate stock price under the dividend discount model
- You can use it to find your outstanding principal balance on your mortgage or lease
PV function has the following syntax:
=PV(rate, nper, pmt, [fv], [type])
The arguments in square brackets are optional while all other are mandatory.
Rate is the periodic interest rate applicable to the cash flow(s). If each cash flow occurs after a year, rate is equal to the annual percentage rate and if each cash flows occur m times per year, interest rate to be entered should be APR/m.
NPER is the number of compounding periods i.e. total number of payments in the cash flow stream. If there are m compounding period per year and n number of years, NPER = m × n. PMT is the amount of equally spread-out equal cash flows which we are looking to discount to their present value. PMT is 0 where we are looking to discount a single cash flow in which case we need to enter a value in [fv] argument.
[FV] is an optional function which represent any cash flow that occur at the end of the cash flow stream. By default, it is assumed to be zero. However, we can enter a value where our cash flow stream has a onetime balloon payment at the end of the stream in addition to the equal stream of cash flows. Alternatively, where PMT is zero, FV is the single cash flows that we want to discount to time 0.
[type] is also an optional argument that takes two values, either 0 or 1. If we don’t enter any value, Excel considers it to be 0 by the default which means that the cash flows occur at the end of the period. Where the cash flows occur at the start of the period, we need to enter 1. [type] is relevant only to the PMT argument because the value entered in [fv] argument is on specific point of time and the question of at the start of the period or end of the period doesn’t arise in that case.
You are an accountant and your company has entered a 5-year lease for 10 delivery trucks. Monthly payments for the lease are $15,000 due in advance and the relevant interest rate is 10% per annum.
In the screenshot below, please follow how the PV function is used to calculate the present value of minimum lease payments.
Please note that (a) because payment period is a month, we have used the monthly interest rate; (b) monthly payment values are entered with a negative sign because th direction of monthly payments is exact opposite of the direction of initial lease cash flow; (c) no value is entered for [fv] because the lease has no terminal payment at the end of lease term; and (d) a value of 1 is entered because the cash flows occur in advance i.e. at the start of each month.
Now, let’s assume the lessor agreed that you will pay lease rentals at the end of each month and he also agreed to transfer the trucks to you at the end of lease term for $60,000 in onetime payment. Present value of lease payments in this situation will be calculated as follows:
Please note that (a) we entered PMT and FV with negative sign because these are outflows, ie. direction of these cash flows is opposite to the direction of PV; and (b) we set TYPE to 0 because now the cash flows occur at the end of each month. Even if we skip to enter any value in TYPE argument, Excel will treat it as 0, the default value.
We can use PV function to calculate present value of a single cash flow. Let’s assume you want to invest in a zero-coupon bond that yields 6% based on semiannual compounding and will pay $10,000 at maturity which is 2 years from now. A zero-coupon bond pays no periodic interest payment instead all its return results from the difference in its initial offer price and the maturity value. Please follow the calculation below to see how we have used the PV function to determine the price we should pay for the bond:
Because we are calculating present value of a single sum i.e. the maturity value we are going to receive after 2 years, we have entered PMT as 0. Even if we leave that argument blank, i.e. use PV(D8,D9,,-D11), the function will still work. The TYPE argument is irrelevant here because it applies to PMT which is 0. While PMT function is 0, it is important to enter the correct NPER.