To calculate present value in Excel, enter the PV function as =PV(rate,nper,pmt,[fv],[type]), replacing each argument with the rate per period, number of periods, payment, future value, and payment timing. For example, with a monthly rate of 0.5%, 36 monthly payments of $300, and payments at month-end, =PV(0.5%,36,-300,0,0) returns about $9,861.
PV is useful for translating a stream of future payments or a future balance into an equivalent value today. The result depends on the timing and signs of the cash flows, so matching the rate, period, and payment assumptions matters as much as entering the function correctly.
What Excel’s PV function calculates
PV stands for present value: the amount that a future sum or series of payments is worth now when discounted at a specified rate. A dollar available today can be invested or used immediately, while a dollar received later is discounted for the time and return involved. PV applies that time-value calculation to a regular series of equal payments, a single future amount, or both.
Excel’s PV function assumes a constant interest rate and regular, equal payment periods. It is therefore suited to questions such as the current value of a fixed loan payment stream or the amount that must be set aside now to reach a stated future balance. It does not automatically model changing rates, irregular payment dates, or variable cash flows.
Understand the arguments and syntax
The function has five arguments. The first three are required; the last two can be omitted when the assumptions they represent are zero or the default:
- rate: The interest or discount rate for one payment period.
- nper: The total number of payment periods.
- pmt: The fixed payment made each period. It must be consistent with the rate and number of periods.
- fv: The balance you want remaining after the final payment. If omitted, Excel treats it as zero.
- type: Payment timing: 0 means payments occur at the end of each period, while 1 means they occur at the beginning. If omitted, Excel uses 0.
For example, a monthly payment schedule needs a monthly rate and a count of months, not an annual rate and a count of years. PV uses the same period for all three: rate, nper, and pmt. The function returns one value, not a payment schedule.
Enter a PV formula in Excel
In Excel for Microsoft 365 on Windows, select a blank cell, type the formula beginning with an equals sign, and press Enter. Excel for Mac and Excel for the web use the same function and formula structure; on a Mac, press Return to enter the formula. If your regional settings use a different list separator, Excel may display semicolons instead of commas, but the formulas here use the standard English comma separator.
For a formula using cell references, put the periodic rate in B2, the number of periods in B3, the payment in B4, the future value in B5, and the timing value in B6. Enter the function in another cell as =PV(B2,B3,B4,B5,B6). Keeping assumptions in separate cells makes the calculation easier to review and update. Use percentage formatting for the rate cell if helpful; entering 0.5% is equivalent to entering 0.005.
To inspect the arguments while building a formula, type =PV( in a cell and follow Excel’s function prompt, or select the Insert Function button beside the formula bar. The prompts help identify each argument, but they do not decide whether your inputs use the right time period or cash-flow signs.
Worked example: value of monthly payments
Suppose a borrower expects to make 36 end-of-month payments of $300, and the illustrative monthly discount rate is 0.5%. The question is what those payments are worth at the start of the schedule. Put 0.5% in B2, 36 in B3, -300 in B4, 0 in B5, and 0 in B6. Here the payment is negative because it is cash paid out, while the present value returned for the borrower is cash received.
With the monthly rate in B2, 36 periods in B3, the outflow of 300 in B4, a zero ending balance in B5, and end-of-period timing in B6, enter =PV(B2,B3,B4,B5,B6). The result is approximately $9,861. A negative payment produces a positive present value under Excel’s cash-flow sign convention. If you enter 300 as a positive payment instead, the result is about -$9,861; the magnitude is the same, but the direction is reversed.
This is a mathematical illustration, not a quote for a real loan. Actual loan terms may use fees, different compounding conventions, variable rates, or payment dates that do not fit this simple model. Check actual terms with the lender or a qualified professional rather than treating a spreadsheet illustration as financial advice.
Match the rate and period carefully
A frequent source of wrong answers is mixing annual and monthly quantities. If payments are monthly, convert an annual rate to a monthly rate and use the number of months for nper. For a simple nominal annual rate divided into 12 monthly periods, the periodic rate is the annual rate divided by 12. A 6% nominal annual rate, for example, becomes 0.5% per month for this kind of calculation.
That conversion is not identical to every possible interest-rate convention. An effective annual rate includes the effect of compounding within the year. To convert an effective annual rate into an equivalent monthly rate, use the rate conversion appropriate to the contract or model; simply dividing by 12 may not preserve the same annual return. For financial decisions, verify how the quoted rate is defined.
Count periods according to actual payment frequency. A 3-year schedule with monthly payments has 36 periods; with quarterly payments it has 12. A payment entered as a monthly amount cannot be paired with an annual rate and a 3-period count unless the model specifically treats each period as a year and the payments are annual.
Payment timing and future value
The type argument distinguishes an ordinary annuity from an annuity due. With type 0, payments happen at the end of each period, which is the default. With type 1, payments happen at the beginning. Beginning-of-period payments are discounted for one fewer period, so their present value is generally larger in magnitude when the rate is positive and the other inputs are unchanged.
The fv argument lets you include a balance after the regular payments. For a savings plan, that may be a target amount still to be held at the end; for a loan, a remaining balance or balloon payment may be relevant. The sign should reflect whether that future amount is received or paid from the perspective used for the other cash flows. Setting fv to zero means the regular payment stream ends with no remaining balance; it does not mean the payments themselves are zero.
A single future amount can also be discounted with PV by setting the regular payment to zero. For instance, assume a 5% annual rate, 10 annual periods, no recurring payments, and a future receipt of $10,000. With the rate in B8, periods in B9, zero in B10 for payment, and -10000 in B11 for the future receipt, =PV(B8,B9,B10,B11) returns approximately $6,139.13. The future amount is negative so the present value is positive under the same cash-flow convention.
Common mistakes and how to diagnose them
- Unexpected negative result: Check whether the signs of payments and future value match the perspective you intend to model. Excel treats money received and money paid as opposite directions.
- Result is far too large or small: Verify that the rate is per period and that nper counts those same periods. A monthly payment paired with an annual rate is a common mismatch.
- Wrong beginning-versus-end assumption: Confirm the type value. Leaving it out makes payments end-of-period, not beginning-of-period.
- Payment count is off: Count each payment date, rather than just the number of calendar years. Include the final scheduled payment if the model includes it.
- Formula is displayed as text: Check that the cell is not formatted as Text and that the entry begins with an equals sign. Change the cell format if necessary, then re-enter the formula.
It can help to label assumption cells with units, such as monthly rate or number of monthly payments. A formula can be syntactically valid and still give a misleading answer if an input has the wrong unit or describes a different cash-flow perspective.
PV compared with FV and NPV
PV and FV answer opposite time-value questions. PV discounts a future amount or payment stream back to today; FV projects present cash flows forward. Both use regular periods and a constant rate in their basic forms, but their results answer different questions. Choose PV when the unknown is today’s equivalent value, and FV when the unknown is a future balance.
Excel’s NPV function is intended for a series of cash flows that can vary from period to period, rather than the equal periodic payment pattern used by PV. In common NPV setups, the first cash flow in the supplied range is treated as occurring at the end of the first period; a cash flow at time zero is handled separately. That timing distinction matters when comparing an NPV result with PV, and irregularly dated cash flows may call for a function designed for dates.
PV is not a substitute for a loan amortization table. It gives the value of the defined stream, but it does not show each period’s interest, principal reduction, or remaining balance. A schedule needs additional calculations and assumptions, particularly when fees, rate changes, or extra payments are involved.
Frequently Asked Questions
What does PV mean in Excel?
PV means present value. It calculates the value today of a future amount, a regular series of equal payments, or a combination of payments and a future balance using a constant rate per period.
Why does Excel’s PV formula return a negative number?
Excel uses cash-flow signs. If payments are entered as positive cash received, PV may be negative to represent the opposite direction. Reverse the signs only when that matches the cash-flow perspective you want; do not change them merely to hide a minus sign.
Should I enter an annual or monthly interest rate?
Use a rate that matches the payment period. Monthly payments normally require a monthly rate and a count of months. Check whether an annual rate is nominal or effective before converting it.
What does type 0 or type 1 mean in PV?
Type 0 means payments occur at each period’s end and is Excel’s default. Type 1 means payments occur at the beginning of each period, as with payments due immediately at the start of a schedule.
Can PV calculate the present value of one future payment?
Yes. Set pmt to zero, enter the number of periods and rate, and supply the future amount in fv with a sign opposite to the present value you want returned. Be sure the periods and rate use the same time unit.
Related
- Snowflake CSV vs CSV for Excel: Which Should You Choose?
- Simple Project Timeline in Excel: Build a Clear Schedule
- Simple Checklist Template Excel: Build a Useful One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel ABS Function: How to Return a Positive ValueTo turn a negative number into a positive one in Excel, use the ABS function: it returns the…
- INDIRECT Excel Function: Examples, Uses, and LimitsIn Excel, INDIRECT converts text into a live cell or range reference; if A2 contains B5, =INDIRECT(A2) returns…
- CAGR Calculation in Excel: Formula, Steps, and ExamplesTo calculate CAGR in Excel, divide the ending value by the starting value, raise the result to the…
- Excel Greater Than Formula: Compare Numbers and ValuesTo test whether a number is greater than another value in Excel, use the greater-than operator >. For…
- How to Add Page Numbers in Word Bottom Right CornerTo add page numbers at the bottom right in Word for Microsoft 365 on Windows, select Insert >…
- Excel COUNTIF Wrong Result? Fixes & CausesIf your Excel COUNTIF is returning a wrong result, the most common causes are mismatched data types (text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.