PV returns the present value of a series of future payments or a future lump sum, discounted at a given interest rate. In plain terms, it tells you what a stream of future cash flows is worth in today's money.
=PV(rate, nper, pmt, [fv], [type])What does PV do?
The PV function answers a simple but powerful question: what is a future stream of money worth right now? You give it an interest rate, a number of periods, and a regular payment (or a future lump sum), and Excel discounts everything back to today. That makes it the go-to tool for comparing offers, checking whether a deal is fair, and planning cash flow.
Why does this matter to office workers and students? Because money has a time value. A promise of 10,000 in five years is not the same as 10,000 today. PV lets you put loans, leases, savings plans and investment offers on the same footing so you can compare them honestly. It is also the function behind many financial models you will meet in business courses and on the job.
A typical situation: your manager is offered a five-year equipment lease with fixed monthly payments and asks whether it is cheaper to pay cash now. With PV you can discount those monthly payments at the company's borrowing rate and get a single number to compare against the cash price. No finance degree required, just the right inputs in the right order.
When to use PV
- Comparing a lump-sum offer today against payments spread over time
- Checking the true cost of a lease or loan in today's money
- Valuing a bond or annuity with regular fixed payments
- Deciding whether to take a pension now or later
- Setting a fair price for a future receivable
- Estimating how much to save today to reach a future target
PV syntax
=PV(rate, nper, pmt, [fv], [type])| Argument | Required? | What to enter |
|---|---|---|
rate | Required | The interest rate per period, as a decimal or percentage. For monthly payments, divide the annual rate by 12. Example: 6% per year becomes 0.06/12 for monthly periods. |
nper | Required | The total number of payment periods. For a 5-year monthly plan, use 5*12 (60 periods). Example: 60. |
pmt | Required | The payment made each period. It must be negative if it is money paid out, positive if received. If you omit it, you must supply fv. Example: -250 for 250 paid each month. |
fv | Optional | The future value or cash balance after the last payment. Defaults to 0. Use a negative number for money you will pay out at the end, positive for money you will receive. Example: -10000 for a 10,000 balloon payment. |
type | Optional | When payments occur: 0 or omitted for end of period, 1 for beginning of period. Example: 1 for rent paid in advance. |
What it returns: PV returns a single number: the present value of the future payments and/or future lump sum, expressed in today's money. The sign follows the cash-flow convention, so money you pay out comes back negative and money you receive comes back positive.
PV examples
1 Example 1: Present value of a single future amount
You are promised 10,000 in three years and the annual discount rate is 5%. The goal is to know what that promise is worth today. The formula =PV(B2,B3,0,B4) uses the rate in B2, the periods in B3, zero regular payments, and the future amount in B4 as a negative cash flow (money you will receive). PV discounts 10,000 back three years and returns about 8,638.38, meaning the promise is worth roughly 8,638 today.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Annual rate | 0.05 | 8638.375985 | ||
| 3 | Years | 3 | |||
| 4 | Future amount received | -10000 | |||
| 5 |
2 Example 2: Present value of a monthly payment stream
A supplier offers 60 monthly payments of 250 instead of a single upfront price. The annual discount rate is 6%, so the monthly rate is 6%/12. The goal is to find the equivalent cash price today. The formula =PV(B2/12,B3,B4) divides the annual rate by 12, uses 60 periods, and treats the 250 payment as negative because it is paid out. PV returns about 12,931.38, which is the amount you could fairly pay today for that payment stream.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Annual rate | 0.06 | 12931.39019 | ||
| 3 | Months | 60 | |||
| 4 | Monthly payment | -250 | |||
| 5 |
3 Example 3: Lease with a residual balloon payment
A five-year equipment lease costs 400 per month and ends with a 5,000 residual payment. The company's borrowing rate is 7% per year. The goal is to express the whole lease as one present value. The formula =PV(B2/12,B3,B4,B5) uses the monthly rate, 60 periods, the negative monthly payment, and the negative 5,000 residual as the future value. PV returns about 23,707.34, the cash-equivalent cost of the lease today.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Annual rate | 0.07 | 23727.82259 | ||
| 3 | Months | 60 | |||
| 4 | Monthly payment | -400 | |||
| 5 | Residual payment | -5000 | |||
| 6 |
4 Example 4: Rent paid at the beginning of each month
An office lease asks for 1,200 per month for 24 months, paid in advance, and the annual discount rate is 4.8%. The goal is to value the lease correctly when payments start immediately. The formula =PV(B2/12,B3,B4,0,1) uses the monthly rate, 24 periods, the negative payment, zero future value, and type 1 for beginning-of-period payments. PV returns about 27,382.67, slightly higher than end-of-period rent because each payment is made one month earlier.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Annual rate | 0.048 | 27518.23618 | ||
| 3 | Months | 24 | |||
| 4 | Monthly rent | -1200 | |||
| 5 | Payment timing | 1 | |||
| 6 |
5 Example 5: Comparing a cash offer with a payment plan
A client can pay 18,000 now or 1,000 per month for 20 months. The company's cost of capital is 9% per year. The goal is to see which option is cheaper in today's money. The formula =PV(B2/12,B3,B4) discounts the 20 monthly payments at the monthly rate and returns about 18,412.09. Because that is higher than the 18,000 cash price, paying now is the better deal.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Annual rate | 0.09 | 18508.01969 | ||
| 3 | Months | 20 | |||
| 4 | Monthly payment | -1000 | |||
| 5 | Cash price today | 18000 | |||
| 6 |
Common PV errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | One of the arguments is text instead of a number, for example a rate typed as '5%' with quotes or a payment cell containing words. | Use real numbers or cell references. Enter 0.05 or 5% without quotes, and make sure payment cells contain numbers. |
| #NUM! | The rate is -1 or lower, or nper is zero or negative, which makes the discounting formula impossible. | Check that rate is greater than -1 and nper is a positive number of periods. |
| Wrong sign result | Payments and future values were entered with the wrong sign, so PV returns a negative number when you expected a positive one. | Follow the cash-flow convention: money paid out is negative, money received is positive. Keep pmt and fv signs consistent. |
| Result looks too small or too large | The rate and nper are on different time bases, for example an annual rate with a monthly period count. | Match the time units: divide the annual rate by 12 for monthly periods and multiply years by 12 for nper. |
| #DIV/0! | The rate argument refers to an empty cell or zero when a payment stream is used, which can break the underlying calculation. | Fill in the rate cell, or use a small non-zero rate if the true rate is zero. |
Tips for using PV
- Keep rate and nper in the same time unit: monthly rate with monthly periods, annual with annual.
- Use negative numbers for money you pay out and positive for money you receive, and stay consistent.
- Set type to 1 for payments at the start of each period, such as rent in advance.
- Leave fv out when there is no final lump sum; it defaults to 0.
- Use cell references rather than hard-coded numbers so you can test different rates quickly.
- Compare the PV result with the cash price to decide whether a payment plan is worth it.
Frequently asked questions
What does the PV function do in Excel?
PV calculates the present value of a series of future payments or a future lump sum, discounted at a given interest rate. It tells you what future money is worth today.
Why does PV return a negative number?
Excel uses a cash-flow sign convention. If you enter payments as positive, PV returns a negative present value because the money flows the other way. Enter payments as negative to get a positive present value.
What is the difference between PV and NPV?
PV handles a constant payment (pmt) over regular periods. NPV handles a series of different cash flows in separate cells. Use PV for level payments and NPV for uneven cash flows.
Should I use type 0 or 1 in PV?
Use 0 or omit it when payments occur at the end of each period. Use 1 when payments occur at the beginning, such as rent paid in advance.
How do I use an annual rate with monthly payments?
Divide the annual rate by 12 and multiply the number of years by 12. For example, 6% per year over 5 years becomes rate 0.06/12 and nper 60.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. PV reference