PV in Excel: Discount a Payment Stream or Future Lump Sum

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

rate is interest per period, nper the number of periods, and pmt the constant periodic cash flow. fv is a terminal future cash flow, defaulting to zero. type is zero or omitted for period-end payments and one for period-beginning payments.

The reference marks pmt required but allows its omission if fv is included. Use an explicit zero for pmt in a lump-sum example. Cash paid is negative and cash received positive from a single chosen perspective. The output is the balancing cash flow at time zero.

The valuation date is distinct from the dates of the periodic payments. With type zero, the first payment occurs one period later. With type one, the first occurs immediately and the last regular payment is at the beginning of the final period. The terminal fv remains a value at the end of nper periods.

Put 1% in B2, 12 in B3 and −100 in B4. In B6 enter =PV(B2,B3,B4,0,0). The independent target is 1125.50774735.

For a transparent check, list period numbers 1 through 12 in D2:D13. In E2 enter =100/(1+$B$2)^D2 and fill down. Summing E2:E13 targets the same positive present value.

The undiscounted payments total 1200. Their present value is lower because the payments occur in the future and the assumed discount rate is positive. This is a model calculation, not a statement that a particular loan or purchase is suitable.

=PV(1%,12,-100,0,1) has independent target 1136.76282482. The payment stream has moved one period earlier, increasing its present value by a factor of 1.01.

To check it, use period numbers zero through eleven in the direct-discount table. The first payment is undiscounted because it occurs at time zero. Do not move a separate terminal future amount earlier merely because type changes the regular-payment timing.

=PV(1%,12,0,1000,0) has independent target −887.44922527. The future receipt is positive, while the balancing amount paid now is negative.

Its magnitude is 1000/(1.01^12). If the future cash flow were an outgoing 1000 instead, using fv=−1000 would return a positive present-value amount. A sign is part of the cash-flow convention, not evidence that discounting failed.

For a combined model, present values add: independently discount each constant-payment stream and the terminal flow, preserving their signs. This linearity is useful for auditing decomposition, but it does not make PV suitable for arbitrary changing payments without a separate schedule.

Microsoft’s example discounts positive monthly receipts of 500 over twenty years using a nominal annual rate of 8% divided by twelve. =PV(8%/12,20*12,500,0,0) has independent target −59777.14585119.

The negative sign represents the balancing amount paid at inception from the recipient’s perspective. The number of periods is 240, not twenty. Using a rounded monthly rate of 0.67% instead of the exact 8%/12 changes the result.

At zero rate, the documented annuity equation becomes pmt*nper+pv+fv=0. Twelve outgoing payments of 100 and no terminal flow therefore target a present value of 1200.

First check rate units, nper units and the sign of each cash flow. The reference’s common annual-rate division examples assume the specified periodic convention; an effective annual rate requires an appropriate conversion rather than automatic division.

PV assumes constant rate and constant regular payments. Fees, irregular dates and changing cash flows are not inferred from the inputs. Exact invalid-argument error displays and unusual rate domains were not native-tested; this guide uses ordinary positive rates and integer schedules.

The fresh Microsoft equation image confirms the shared annuity balance relation. The examples here are independently discounted calculations, not captured Excel output or verification of a financial product’s actual terms.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft PV reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.