In Excel, calculate present value with the PV function: provide the interest rate per period, the number of periods, the payment each period, any future value, and whether payments occur at the beginning or end of each period. With those inputs in B2:B6 in that order, enter =PV(B2,B3,B4,B5,B6). The result is the amount today that is financially equivalent to the future payments or value, given the rate you supplied.
What present value means
Present value, often shortened to PV, expresses future money in today’s terms. The idea is that money available now can earn a return, so a dollar promised in the future is generally worth less today. PV reverses the process of compounding: it discounts future cash flows back to the present using a selected rate and time period.
This calculation is useful for understanding the value of a stream of equal payments, such as loan instalments or regular receipts, and for finding today’s equivalent of a single future amount. It does not predict what an investment will actually earn. Its result depends on the rate, timing, and cash flows entered, so changing an assumption changes the answer.
Excel’s PV function handles payments that are equal in amount and evenly spaced. If cash flows vary from period to period, a different function or a period-by-period calculation is usually more suitable.
PV function arguments and their order
PV has five arguments. The first three are required; the last two can be omitted when the future value is zero and payments occur at the end of each period:
- Rate: the interest or discount rate for one period, such as a monthly rate for monthly payments.
- Nper: the total number of payment periods, not necessarily the number of years.
- Pmt: the fixed payment made or received in each period. Leave this at zero when there are no regular payments.
- Fv: the balance or cash flow remaining after the final payment. For a fully paid loan, this is commonly zero.
- Type: use 0 for payments at the end of each period, or 1 for payments at the beginning. If omitted, Excel assumes 0.
Keep the rate and period length consistent. An annual rate paired with a monthly period count does not describe a monthly-payment loan correctly. For monthly payments, use a monthly rate and the total number of months.
Set up the worksheet
A small input table makes the calculation easier to inspect and update. Put a label in column A and its corresponding value in column B. For example, enter the periodic rate in B2, total periods in B3, regular payment in B4, future value in B5, and payment timing in B6. Format rates as percentages and monetary inputs as currency if that helps you read the sheet; formatting changes how a number appears, not the underlying calculation.
With the periodic rate in B2, periods in B3, payment in B4, future value in B5, and timing code in B6, calculate present value in another cell with =PV(B2,B3,B4,B5,B6). A result cell can be formatted with Home > Number to choose a number or currency format. On Excel for Mac, the corresponding formatting controls are also on the Home tab, though the ribbon layout can differ slightly. Excel for the web has similar formula and number-format controls in its ribbon.
For an end-of-period calculation with no remaining balance, set B5 to zero and B6 to zero. You can also omit those optional arguments, but entering them explicitly makes the assumptions visible to someone reviewing the workbook.
Example: present value of regular payments
Suppose you will receive $1,000 at the end of each year for five years, and you use a 5% annual discount rate. This is an ordinary annuity because each payment arrives at the end of a period. Enter 5% in B2, 5 in B3, 1,000 in B4, 0 in B5, and 0 in B6. With those values in B2:B6, enter =PV(B2,B3,B4,B5,B6). Excel returns approximately -$4,329.48.
The negative sign is Excel’s cash-flow convention, not evidence that the calculation failed. The positive payments are treated as money coming in; the PV is the equal amount going out today that balances them. If you want the present value displayed as a positive asset amount, enter the payments as negative cash flows instead. With the same rate, period count, future value, and timing, but -1,000 in B4, the formula returns approximately $4,329.48.
This result means that, under the assumed 5% rate and end-of-year timing, $4,329.48 today is equivalent to five annual receipts of $1,000. It does not mean those payments are guaranteed or that 5% is the correct rate for every decision.
Example: a monthly loan payment
Imagine a loan with 12 monthly payments of $100 and a monthly interest rate of 1%, with no balance left after the final payment. Put 1% in B2, 12 in B3, -100 in B4, 0 in B5, and 0 in B6. The negative payment marks money paid out by the borrower. With these inputs in B2:B6, enter =PV(B2,B3,B4,B5,B6). The result is approximately $1,125.51.
That is the present value of the payment stream under the stated assumptions. It is not automatically the amount a lender will advance: fees, a different payment date, a final balloon balance, or other contract terms can affect the actual loan. For real borrowing decisions, check the lender’s stated terms and consult a qualified professional if you need financial advice.
PV can also be used in the opposite direction from a payment calculator. If the loan amount and rate are known and you want the equal periodic payment, Excel’s PMT function is designed for that question. With the periodic rate in B2, number of periods in B3, present loan amount in B4, future balance in B5, and timing code in B6, enter =PMT(B2,B3,B4,B5,B6). The sign of the result follows the same cash-flow convention.
Match the rate and number of periods
Converting an annual rate correctly is one of the most important parts of a PV calculation. If a stated nominal annual rate is 6% and payments are monthly, a common model uses 6% divided by 12 as the monthly rate and the number of years multiplied by 12 as the period count. This assumes the quoted rate is a nominal annual rate convertible monthly; check the rate definition in the relevant contract or source.
With the nominal annual rate in B2, the monthly rate is calculated in B3 using =B2/12. With the number of years in B4, the total number of monthly periods is calculated in B5 using =B4*12. Reference B3 for the rate and B5 for nper in the PV calculation. If the quoted annual percentage is an effective annual rate instead, dividing by 12 does not preserve that effective annual return. The equivalent monthly rate is the twelfth root of one plus the annual effective rate, minus one.
Also make the time unit clear in cell labels. A rate of 1% per month and a period count of 12 describe one year of monthly periods; a rate of 1% per year and a period count of 12 describe twelve years. Excel cannot identify a mismatch from the numbers alone.
Payment timing and cash-flow signs
The type argument changes the assumed date of every regular payment. With type 0, the first payment occurs one full period from now. With type 1, the first payment occurs immediately, at the beginning of the first period. Because earlier payments have less time to be discounted, a stream of beginning-of-period payments has a larger present value in absolute terms than the same stream of end-of-period payments at a positive rate.
For example, with annual payments of $1,000 for five years at 5%, changing only the timing from 0 to 1 increases the present value’s magnitude by 5%, because each payment is shifted one period earlier. Use the timing that matches the actual cash-flow dates; selecting 1 just to obtain a preferred answer misstates the schedule.
Excel financial functions generally use opposing signs for money paid and money received. If a loan amount is received today, later repayments are usually entered with the opposite sign. If a future benefit is entered as a positive amount, the amount paid today may appear negative. A consistent sign convention makes results easier to interpret and helps avoid mistaking a negative result for an error.
PV versus NPV for uneven cash flows
PV is for a fixed payment repeated at regular intervals, with an optional final value. It is not the right shortcut for a sequence of different future cash flows. For evenly spaced but varying future cash flows, Excel’s NPV function discounts each listed value at a constant rate. NPV treats the first value supplied as occurring one period in the future, not today.
With a periodic discount rate in B2, future cash flows for periods 1 through 5 in B3:B7, and an initial cash flow at time zero in B8, calculate their net present value with =NPV(B2,B3:B7)+B8. Add the time-zero amount separately because it is not discounted by NPV. The signs of the cash flows should reflect money received and paid.
When dates are irregular, XNPV may be a better fit because it discounts cash flows using their actual dates. XNPV is available in Microsoft 365 and Excel 2021 or later. Choose the method that matches the schedule rather than forcing irregular flows into PV’s equal-period assumptions.
Common problems and checks
- Unexpected negative answer: check which inputs represent money paid and money received. The sign often reflects cash-flow direction.
- Result is far too large or small: confirm that the rate is per period and nper counts those same periods. Monthly payments usually require months, not years.
- Wrong payment date assumption: check whether the first payment is due immediately or at the end of the first period, then set type to 1 or 0 accordingly.
- Final balance is not zero: include the expected future value if the last payment leaves a balance or a balloon payment remains.
- Uneven payment amounts: use NPV for regularly spaced varying cash flows, or XNPV for cash flows with actual, irregular dates.
When the rate is zero, discounting has no effect: the present value is simply the total of the payments plus the future value, with signs determined by the cash-flow convention. A quick check is to compare the output with that total when you deliberately set the rate to zero. Avoid rounding input values prematurely; apply number formatting for display, and round the underlying calculation only if the task explicitly requires it.
Frequently Asked Questions
What does PV stand for in Excel?
PV stands for present value. Excel’s PV function calculates the current equivalent of regular payments and/or a future balance using a constant rate and regular periods.
Why does Excel’s PV formula return a negative number?
Excel represents money paid and money received with opposite signs. If your payments are positive, PV may be negative to represent the amount paid today. Reverse the signs of the cash flows if you want the result shown from the other perspective.
Should I use 0 or 1 for the type argument?
Use 0 when payments happen at the end of each period, which is the default, and 1 when they happen at the beginning. Match the actual schedule, such as whether rent is due at the start of a month.
Can PV calculate a single future amount?
Yes. Set the regular payment to zero and enter the future amount in the future-value argument. Use a rate and period count that match the amount’s timing, and remember that the PV and future amount normally have opposite signs.
What is the difference between PV and NPV?
PV is suited to equal periodic payments and an optional final balance. NPV discounts a range of future cash flows that can differ in amount, assuming regular intervals; a time-zero cash flow must be added separately.
Related
- Excel Vertical Text: How to Rotate Text in Cells
- Excel Very Hidden Sheets: What They Are and How to Use Them
- Excel AVERAGE Tips and Tricks for Everyday Spreadsheet Tasks
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Web: Change Conversation ViewTo change conversation view in Outlook on the web, select Settings (the gear icon) > Mail > Layout,…
- Google Sheets UNIQUE Function: Remove Duplicate ValuesTo list distinct values in Google Sheets, put your source data in a range and enter the UNIQUE…
- Excel File Opens to a Blank Gray Screen? FixesIf an Excel file opens to a blank gray screen, first check whether the workbook is hidden or…
- How to Get Rid of a Dropdown List in Excel QuicklyTo remove a dropdown list in Excel, select the cell or range, go to Data > Data Validation,…
- Excel Shortcut Desk Mat: What It Is and How to ChooseAn Excel shortcut desk mat is a large mouse-and-keyboard surface printed with keyboard commands for Microsoft Excel. It…
- How to Clear Cache in Outlook for WindowsTo clear Outlook’s local cache in Windows, first identify which Outlook you use: in classic Outlook, clear the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.