PV Function

Calculates what future payments are worth today.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A present value

Want to practice PV in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

PV discounts a series of equal payments — or a single future amount — back to today's value at a given interest rate.

Use it to find how much you can borrow for a payment you can afford, or what a future sum is worth now.

Syntax

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

Arguments

ArgumentRequired?What it does
rateYesThe interest rate per period (annual rate ÷ 12 for monthly payments).
nperYesThe total number of payment periods.
pmtYesThe payment each period. Use 0 for a single future amount.
fvNoA single amount at the end. Omitted = 0.
typeNoWhen payments fall: 0 or omitted = end of each period, 1 = start of each period.

Examples

Example 1 — How much can we borrow?

Northstar can afford $2,000 a month for five years at 7%. What loan does that support?

B5fx=PV(B1/12,B2*12,B3)
AB
1Annual rate7%
2Years5
3Monthly payment-$2,000
4
5Loan size$101,003.99

Example 2 — A future sum in today's money

A customer will pay $100,000 in five years. At 5% a year, what's that worth today?

=PV(5%,5,0,-100000) Explain

Result: $78,352.62.

Common mistakes

  • Rate and periods in different units. Keep them matched: for monthly payments divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with monthly periods gives wildly wrong answers.
  • Uneven cash flows. PV needs equal payments. For flows that change each year use NPV or XNPV.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.