PV Formula in Excel: How to Use It

Formulas & functions · Updated November 22, 2026

The PV function in Excel calculates the present value of a loan or investment based on a constant interest rate. You enter it as =PV(rate, nper, pmt, [fv], [type]), where rate is the interest rate per period, nper is the number of periods, and pmt is the payment made each period.

What Is Present Value?

Present value answers a simple question: how much is a future sum of money worth today? Because money can earn interest, $1,000 received five years from now is worth less than $1,000 in your pocket today. The PV function discounts future cash flows back to today’s dollars using a specified interest rate. This concept underpins loan pricing, bond valuation, and investment analysis.

Excel’s PV function assumes cash flows occur at regular intervals (monthly, quarterly, yearly) and that the interest rate remains constant. If your cash flows are irregular or the rate changes, you would need a different approach, such as the XNPV function or a manual discounted cash flow model.

PV Function Syntax and Arguments

The full syntax is =PV(rate, nper, pmt, [fv], [type]). Here is what each argument means:

  • rate (required): The interest rate per period. If you have an annual rate of 6% and payments are monthly, use 6%/12 = 0.5%.
  • nper (required): The total number of payment periods. For a 5-year monthly loan, nper = 60.
  • pmt (required): The payment made each period. It cannot change over the life of the investment. If omitted, you must include fv.
  • fv (optional): The future value, or the cash balance you want after the last payment. Defaults to 0 (for a loan, that means it’s fully paid off).
  • type (optional): 0 (default) means payments are due at the end of the period; 1 means payments are due at the beginning.

Note: The pmt and fv arguments should be entered as negative numbers if they represent cash outflows (like payments you make), because Excel treats money you receive as positive and money you pay out as negative.

How to Use PV in a Real Example

Suppose you want to know the present value of a 3-year loan where you pay $200 at the end of each month, and the annual interest rate is 5%. Set up your spreadsheet like this:

  • A1: 5% (annual rate)
  • A2: 36 (months)
  • A3: -200 (monthly payment, negative because it’s an outflow)

Then enter in A4: =PV(A1/12, A2, A3)

Excel returns approximately $6,672.48. That means the series of 36 monthly payments of $200 is equivalent to borrowing about $6,672 today at 5% annual interest. If you instead wanted to know how much you need to invest today to have $10,000 in 10 years at 4% annual interest (with no periodic payments), you would use =PV(4%, 10, 0, 10000), which returns -$6,755.64 (negative because you’d need to pay out that amount now).

Common Mistakes and How to Avoid Them

Several errors trip up new users:

  • Forgetting to divide the annual rate by the number of periods per year. If you make monthly payments, always use rate/12. For quarterly, rate/4.
  • Mismatching nper with the payment frequency. If you pay monthly for 5 years, nper = 60, not 5.
  • Using positive numbers for payments. Excel returns a negative PV when pmt is positive, which confuses many. Enter pmt as negative to get a positive present value.
  • Ignoring the type argument. If payments are due at the beginning of the period (like rent), set type=1. This changes the result because each payment is discounted for one fewer period.
  • Using PV when you need NPV. PV works for constant periodic cash flows. If cash flows vary by period, use =NPV(rate, value1, [value2], ...) instead.

PV vs. NPV vs. FV

Excel has three related functions you might confuse:

  • PV – present value of a constant series of cash flows (or a single future value). Good for loans, annuities, and simple investments.
  • NPV – net present value of irregular cash flows. You supply a rate and a range of values; Excel discounts each one. NPV assumes the first cash flow occurs one period from now, not immediately.
  • FV – future value. It answers the opposite question: what will a series of payments or a lump sum be worth at a future date? For example, =FV(5%/12, 120, -200) tells you the balance after 10 years of saving $200 monthly.

In short, PV brings future money back to today, FV pushes today’s money forward, and NPV handles uneven cash flows.

PV with Different Payment Types

The type argument matters more than most people think. Consider a 12-month lease with $500 monthly payments and a 6% annual interest rate. If you pay at the end of each month, =PV(6%/12, 12, -500) gives about $5,808. If you pay at the beginning of each month, =PV(6%/12, 12, -500, 0, 1) gives about $5,837. The beginning-of-period payments are worth more today because you keep the money longer before paying. For loans, most banks use end-of-period payments (type=0). For leases or rent, beginning-of-period is common. Always check your contract.

Frequently Asked Questions

Why does PV return a negative number?
Because Excel follows the cash flow sign convention: money you pay out is negative, money you receive is positive. If you enter pmt as a positive number, PV returns a negative present value, meaning you would need to pay that amount today to receive the future payments. To get a positive result, enter pmt as negative.

Can I use PV for a loan with a balloon payment?
Yes. Use the fv argument for the balloon payment. For example, a $20,000 car loan with $300 monthly payments for 48 months and a $5,000 balloon at the end would be =PV(6%/12, 48, -300, -5000). The fv is negative because it’s a payment you make at the end.

Does PV account for inflation?
No. PV uses the nominal interest rate you provide. If you want to account for inflation, you need to use a real interest rate (roughly nominal rate minus inflation rate) or adjust the cash flows for inflation manually. Excel has no built-in inflation adjustment.

What’s the difference between PV and XNPV?
PV assumes regular, equally spaced periods. XNPV allows you to specify exact dates for each cash flow, which is useful for irregular timing. XNPV also uses a different formula (it discounts each cash flow based on the actual number of days). Use XNPV when your cash flows are not monthly, quarterly, or yearly.

Can I use PV in Google Sheets?
Yes, the PV function works identically in Google Sheets. The syntax and arguments are the same. The only difference is that in Google Sheets, you might need to use semicolons as argument separators if your locale uses them, but in most English locales, commas work fine.

Final Tips for Accurate Use

Always double-check the rate period match. If you’re using monthly payments, the rate must be monthly. If you have an annual percentage rate (APR) but payments are monthly, divide by 12. For quarterly, divide by 4. Also, remember that PV returns a dollar amount, not a percentage. If you need to compare investment options, compute the PV for each and choose the higher one (assuming you’re receiving money). For loans, a lower PV means you’re borrowing less, which is better for you. Finally, if you’re dealing with complex financial scenarios, consider using the RATE, NPER, or PMT functions to solve for other unknowns, or consult a financial professional for advice.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.