How to Use the Excel PV Function to Calculate Present Value

Key Takeaways 

• The Excel PV function returns the present value of an investment or loan based on a constant interest rate and periodic payments. 
• The full syntax is =PV(rate, nper, pmt, [fv], [type]), where fv and type are optional arguments that handle lump sums and annuity timing. 
• Always match your rate and nper to the same time period (e.g., divide an annual rate by 12 when working with monthly payments). 
• PV works for annuities, loans, pensions and any scenario where you need to determine what a series of future cash flows is worth today. 

The PV function in Excel calculates the present value of a future series of payments or a single lump sum, discounted at a constant interest rate. For anyone working in finance, accounting or banking, you should be fluent in the time value of money. Knowing exactly what it means to discount something or to get the future value of a particular investment vehicle is necessary to do the job. 

Excel can be an extremely useful tool for these calculations. It can perform complex calculations and has several formulas for just about any role within finance and banking. Among these, the PV function is one of the most essential for time-value-of-money analysis. Whether you need to calculate the present value of an annuity, a loan or a future pension payout, this function handles it all. 

PV Function Syntax and Arguments 

The full syntax for the PV function in Excel is: 

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

The table below defines each of the five arguments: 

Here are a few important remarks to keep in mind when using this function: 

• Sign conventions matter. Cash you pay out (investments, deposits) should be entered as negative numbers. Cash you receive is positive. If your result seems backward, check your signs. 
• If you omit the fv argument, Excel assumes it is zero, meaning there is no lump sum remaining after the final payment. 
• If you omit the type argument, Excel assumes zero (payments at the end of each period, which is an ordinary annuity). 
• Your rate and nper must always use the same time unit. If payments are monthly, divide an annual rate by 12 and multiply years by 12. 

Understanding Each Argument 

rate is the interest rate per period, not necessarily per year. If you have an annual rate but monthly payments, divide the annual rate by 12 to get the monthly rate. 

nper is the total number of payment periods over the life of the annuity or loan. For a 12-year loan with monthly payments, nper would be 144 (12 x 12). 

pmt is the amount paid each period. Enter it as a negative number if it represents money you are paying out, such as a loan payment or investment contribution. 

fv is the future value, or the cash balance you want after the last payment. For most annuity calculations you can leave this at zero or omit it entirely. It becomes essential when calculating the present value of a lump sum, such as a pension payout. 

type controls the timing of payments. Use zero (or omit) for an ordinary annuity where payments occur at the end of each period. Use one for an annuity due where payments occur at the beginning of each period, such as rent or lease payments. 

Practical PV Function Examples 

The basic annuity formula in Excel for present value is =PV(RATE,NPER,PMT). Let's walk through several examples to see how this works in practice. 

Example 1: Present Value of an Annual Annuity 

If you were trying to figure out the present value of a future annuity that has an interest rate of 5% for 12 years with an annual payment of $1,000, follow these steps: 

1. In cell B1, enter the annual interest rate: 0.05 
2. In cell B2, enter the number of periods: 12 
3. In cell B3, enter the payment amount: -1000 (negative because it is cash paid out) 
4. In cell B4, enter the formula: =PV(B1,B2,B3) 
5. The result is $8,863.25, which represents the present value of that stream of payments. 

You could also enter this directly as =PV(.05,12,1000). Note that if you enter pmt as a positive number, Excel returns the result as a negative number due to its sign conventions. 

Ordinary Annuity vs. Annuity Due 

An ordinary annuity assumes payments occur at the end of each period (type=0), which is the default. An annuity due assumes payments occur at the beginning of each period (type=1). Lease payments and rent are common examples of annuities due. 

The difference in timing changes the present value. Using the same example above (5% rate, 12 years, $1,000 annual payment): 

• Ordinary annuity (type=0): =PV(0.05, 12, -1000, 0, 0) returns $8,863.25 
• Annuity due (type=1): =PV(0.05, 12, -1000, 0, 1) returns $9,306.41 

Because each payment arrives one period earlier in an annuity due, the present value is higher. When setting up your spreadsheet, always confirm whether payments are made at the beginning or end of the period and set the type argument accordingly. For more hands-on walkthroughs, browse additional Excel tutorials covering formulas, formatting and data analysis. 

Adjusting for Monthly or Quarterly Payments 

For this formula, it is important to note that the "NPER" value is the number of periods that the interest rate is for, not necessarily the number of years. This means that if you get a payment each month, you would have to multiply the number of years by 12 in order to get the number of months. Because the interest rate is an annual rate, you would also have to make this a monthly rate by dividing it by 12. 

So if the same problem above was a monthly payment of $1,000 for 12 years at a 5% interest rate, here is how to set it up step by step: 

1. In cell B1, enter the monthly rate: =0.05/12 (this equals approximately 0.004167) 
2. In cell B2, enter the total number of monthly periods: =12*12 (this equals 144) 
3. In cell B3, enter the monthly payment: -1000 
4. In cell B4, enter the formula: =PV(B1,B2,B3) 

You could also enter this directly as =PV(.05/12,12*12,1000), or simplify it into =PV(.004167,144,1000). 

The most common mistake with the PV function is mismatching your rate and nper periods. If payments are monthly, both the rate and nper must reflect monthly values. If payments are quarterly, divide the annual rate by four and multiply years by four. Keeping rate and nper in the same time unit is essential for an accurate result. 

Beyond PV: Related Excel Finance Functions 

While the PV function is the starting point for present value analysis, Excel offers a full suite of companion functions that form the core annuity toolkit: 

• FV (Future Value): Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Use it when you know the present value and want to project growth. 
• PMT (Payment): Calculates the payment amount per period for a loan or annuity when you already have the present value, number of periods and interest rate. 
• NPER (Number of Periods): Finds how many periods are needed to pay off a loan or reach an investment goal given a rate, payment and present value. 
• RATE: Determines the interest rate per period when you know the number of periods, payment and present value. 

These five functions work together. If you know any four of the five annuity variables (pv, fv, pmt, nper, rate), you can solve for the missing one. 

You can also use PV to find the present value of a single lump sum, such as a pension payout, a calculation that frequently appears when interpreting financial statements. Set pmt to zero and enter the future amount in the fv argument. For example, =PV(0.05, 20, 0, -500000) tells you what a $500,000 pension received in 20 years is worth today at a 5% discount rate. 

For more on these functions, explore Pryor Learning's guide to Excel formulas and Excel finance formulas to continue building your spreadsheet skills.