by Svetlana Cheusheva , updated on March 13, 2023
What is PV in Excel? It's a function to calculate present value. This tutorial explains its syntax, shows how to build a correct PV formula for a series of cash flows and a single payment, describes what pitfalls you may encounter and how to overcome them.
Suppose you are thinking about buying an insurance annuity to secure a steady cash flow during your retirement years. Or maybe you consider putting some money in a saving account with a decent annual interest. Whatever it is, you are wondering - is that a good deal? To know it for sure, you need to find the present value of an investment. For this, Microsoft Excel provides the PV function, which stands for "present value".
PV is an Excel financial function that returns the present value of an annuity, loan or investment based on a constant interest rate. It can be used for a series of periodic cash flows or a single lump-sum payment.
The PV function is available in all versions Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.
The syntax is as follows:
PV(rate, nper, pmt, [fv], [type])For the Excel PV function to work correctly in your worksheets, please take into account these usage notes:
To get a general idea of how to use the PV function in Excel, let's construct a present value formula in its simplest form.
Suppose you are making regular contributions to build up your savings for retirement. You deposit $500 per period at a 7% interest rate and will do 50 such payments at equal intervals.
To find the present value of the annuity, set up your worksheet in this way:
The formula to calculate the PV of the investment is:
If you compare the results of the ordinal annuity (payments are made at the end of the period) and annuity due (payments are made at the beginning of the period), you'll notice that, in the latter case, the present value is higher.
PV of regular annuity:
PV of annuity due:
And here are a few more things to take notice of:
You can also look at the problem from another angle. To find the interest rate at which PV is 0, use the IRR function.
The following examples will give you the insight of how the Excel PV function works in different scenarios, so you could adjust the basic formula for your specific task.
Let's say you bought an annuity in which a regular payment of $200 is to be made to the insurance company at the start of every month for the next 10 years. The annuity earns a 9% annual interest compounded monthly. The question is - how much is this annuity worth now?
To begin with, enter all the data in separate cells:
In this case, the interest rate (rate) and payment (pmt) are for different periods. To do PV right, we need to make a couple of conversions:
To convert an annual interest rate to a periodic rate, divide the annual rate by the number of periods per year:
rate = annual interest rate / no. of periods per year
To get the total number of periods, multiply the annuity term in years by the number of periods per year:
nper = no. of years * no. of periods per year
Since we have a monthly annuity, we can divide and multiply by 12 or by cell B6 in which this number is entered.
The complete PV formula in B8 is:
=PV(B2/B6, B3*B6, B4,, B5)
In a similar manner, you can calculate the present value of a weekly, quarterly or semiannual annuity. For this, simply change the number of periods per year in the corresponding cell:
In this example, we are going to find the present value of an investment that will pay $50,000 in 5 years, with an annual interest rate of 7%. The goal is to find out how much money we need to invest today to reach the target amount at the end of the investment period.
As usual, we input the annuity data in separate cells:
Assuming the interest rate is compounded annually, the present value formula is as simple as this:
Please pay attention that the pmt argument is omitted in this case because it's supposed to be a single lump-sum investment without additional periodic payments.
As shown in the screenshot below, the result of the PV formula is negative, because it's an outflow, i.e. the money you'd invest now to earn the target amount in the future.
But what if we have several proposals from various investment firms and we want to compare the effect of different compounding periods?
In this case, we type the number of compounding periods per year in cells E2:E6 like shown in the image below. Then, we enter the below formula in F2 and drag it down through F6:
The constant data such as the interest rate ($B$2), annuity term ($B$3), future value ($B$4), and type ($B$5) must be supplied as absolute references, so that the formula copies correctly to the below cells.
Taking a closer look at the results, you may notice an inverse relationship between the calculated PV (absolute value ignoring the sign) and the number of compounding periods. The best deal for us is weekly compounding - by investing the smallest amount of money now, we will get the same $50,000 in 5 years.
Besides PV, in finance there is one more term, called NPV, that discounts future cash flows by an expected rate of return to estimate their current value. Though these two terms have a lot in common, they differ in an important way.
Present value (PV) - refers to future cash inflows in a given period.
Net present value (NPV) – is the difference between the present value of cash inflows and the present value of cash outflows. In other words, NPV takes into account the initial investment, making the present value a net figure.
In Microsoft Excel, there are two main differences between the PV and NPV functions:
That's how to how calculate PV in Excel. I thank you for reading and hope to see you on our blog next week!
