To find the present value of a future amount in Excel, use the PV function with no periodic payments: =PV(rate, nper, 0, -future_value). For example, $10,000 due in five years discounted at 6% per year has a present value of about $7,472.58; the negative sign on the future value makes Excel return the present value as a positive number.
What present value of a future amount means
Present value answers a basic time-value-of-money question: how much money today is equivalent to a specified amount received later, given an assumed rate of return or discount rate? Money available now can potentially earn interest, so a future dollar is generally worth less today than a dollar in hand. The discount rate expresses how much less, while the number of periods captures how long the money remains invested or how long you wait to receive it.
This calculation can help compare amounts occurring at different dates, estimate how much to set aside for a future target, or check a financial model. It is only as meaningful as its assumptions. The result is not a promise that an account will earn the selected rate, and it does not account for taxes, fees, inflation, or risk unless you build those considerations into the assumptions.
How Excel’s PV function works
Excel’s PV function has five arguments: rate, nper, pmt, fv, and type. Rate is the interest or discount rate for each period; nper is the number of periods; pmt is a regular payment made each period; fv is the balance or cash flow remaining after the last payment; and type indicates whether regular payments occur at the beginning or end of a period. For a single future amount with no regular payments, set pmt to zero. The optional type argument is unnecessary in that case.
The key is to keep the rate and period count in matching units. If the rate is annual, nper should count years. If the rate is monthly, nper should count months. Entering a monthly rate with a year count, or an annual rate with a month count, changes the assumed time period and produces a misleading answer.
Worked example: $10,000 in five years
Suppose a payment of $10,000 will arrive exactly five years from now, and you want to discount it at 6% per year. With the annual rate in B2, the number of years in B3, and the future amount in B4, enter:
=PV(B2,B3,0,-B4)
With B2 set to 6%, B3 set to 5, and B4 set to 10000, the result is approximately $7,472.58. In mathematical terms, that is $10,000 divided by 1.06 raised to the fifth power. The formula says that about $7,472.58 today, growing at 6% annually for five years, would amount to $10,000 before considering other costs or assumptions.
Excel uses a cash-flow sign convention: money you pay out and money you receive should have opposite signs. Here, the future amount is entered as negative, so PV appears as positive. If you enter a positive future amount instead, Excel returns a negative present value. The magnitude is the same; the sign describes the direction of the cash flow.
Enter the values directly or use cell references
For a quick one-off calculation, you can place the values directly in the function. For a reusable worksheet, separate input cells are usually clearer: a rate cell, a periods cell, and a future-value cell make it easier to check assumptions or run scenarios. Label each input with its unit, such as annual rate, years, or dollars, so the period basis is not left to guesswork.
If B2 contains 6%, B3 contains 5 years, and B4 contains 10000, an equivalent direct discounting formula is:
=B4/(1+B2)^B3
That returns approximately $7,472.58 as well. This expression makes the discounting arithmetic visible, while PV is useful when a calculation includes periodic payments as well as a final amount. In Microsoft 365 for Windows, the same formula syntax works in Excel for Mac and Excel for the web. Some regional settings use semicolons rather than commas as argument separators, but the standard Excel syntax shown here uses commas.
Choose a rate and period that match the cash flow
The spreadsheet cannot decide which discount rate is appropriate. Use a rate that matches the question you are modeling and the timing of the amount. A quoted borrowing rate, an assumed investment return, and a rate used to compare uncertain future cash flows are not automatically interchangeable. For a real loan, savings product, or contract, check the actual terms with the lender or provider; for a consequential decision, consult a qualified professional. This article explains spreadsheet mechanics, not financial, tax, investment, or legal advice.
Also distinguish a nominal annual rate from an effective annual rate. If an annual rate is quoted as a nominal rate compounded monthly, dividing it by 12 gives a monthly periodic rate under that convention. If the given figure is an effective annual rate, dividing by 12 does not preserve the same annual growth. Convert it to a monthly rate consistent with the stated compounding method before using monthly periods.
Timing matters, too. PV assumes regular, equally spaced periods. A payment due at the end of five full annual periods fits an annual rate and five periods. If the date is in five years and three months, or cash flows occur on irregular calendar dates, a simple PV setup does not capture the exact day count. A date-based cash-flow analysis may be more suitable for irregular timing.
Monthly compounding and other timing examples
For a future amount due in three years with monthly compounding, express the rate per month and count 36 monthly periods. If B2 contains a nominal annual rate of 4.8%, B3 contains the number of years, and B4 contains the future amount, use:
=PV(B2/12,B3*12,0,-B4)
With B2 set to 4.8%, B3 set to 3, and B4 set to 10000, this setup discounts the amount using a monthly rate of 0.4% for 36 months. The result is approximately $8,657.50. This example assumes the stated 4.8% is a nominal annual rate compounded monthly and that there are no intervening payments. If the rate is instead an effective annual rate, convert it consistently rather than assuming the same calculation applies.
More generally, for quarterly periods, use a quarterly rate and the number of quarters; for weekly periods, use a weekly rate and the number of weeks. The important principle is not a particular division rule but consistency between how the rate is defined and how the number of periods is counted.
PV with payments versus a single future value
PV is not limited to a single future amount. The pmt argument lets it calculate the present value of a stream of equal payments, and the fv argument can represent an additional amount at the end. For example, a loan model may include regular installments and a final balloon balance. In that case, specify both payment and final-balance cash flows with appropriate signs, and ensure the rate and payment frequency agree.
For the question “What is the present value of this one future amount?”, however, pmt should be zero. Do not put the future amount in the pmt argument just because it is money. A payment argument means the same payment repeats in each period; the future value argument represents the ending amount. Confusing the two can dramatically change the result.
Common errors and how to check the result
- Wrong period units: Check that rate and nper refer to the same interval. Five annual periods require an annual rate, while 60 monthly periods require a monthly rate.
- Unexpected negative answer: Review the cash-flow signs. A positive future cash flow normally produces a negative PV, and a negative future cash flow produces a positive PV.
- Putting a one-time amount in pmt: Use zero for pmt when there are no repeated payments, and put the ending amount in fv.
- Entering 6 instead of 6%: A rate entered as 6 means 600% in Excel. Enter 6% or 0.06 for a 6% rate.
- Assuming the result is a forecast: PV is a calculation from assumptions, not a prediction of a market return or a guaranteed value.
A useful reasonableness check is to compare the present value with the future amount. With a positive discount rate and a positive number of periods, a single future amount should generally have a lower present value. A higher rate or a longer wait should lower the result; a lower rate or shorter wait should raise it. If the result moves the opposite way, inspect the rate, signs, and period count.
When to use a different approach
Use PV when cash flows are evenly spaced and the rate is constant for every period. If you only need a single discounting calculation, the direct division formula is easy to audit. If you need an annuity or loan calculation with repeated equal payments, PV can model those payments by setting pmt accordingly. For cash flows on specific, uneven dates, use a method designed for date-based cash flows rather than forcing them into equal periods. The method should follow the timing in the real situation, not just the layout that is easiest to enter.
Frequently Asked Questions
What is the Excel formula for present value of a future amount?
Use =PV(rate,nper,0,-future_value) when there are no periodic payments. Match the rate unit to the period count, and use the negative future-value sign if you want the present value displayed as positive.
Why does Excel PV return a negative number?
Excel follows cash-flow signs. If you enter the future amount as positive, PV is shown as negative to represent the opposite side of the cash flow. Reverse the sign of the future amount if you want a positive PV; do not interpret the sign alone as an error.
Should I divide the annual rate by 12?
Only when the rate is a nominal annual rate compounded monthly and the model uses monthly periods. An effective annual rate needs a different conversion. Follow the rate definition and the product or contract terms.
What should I enter for pmt when I only have one future amount?
Enter zero. The pmt argument is for a repeated, equal payment each period. Put the one-time amount due at the end of the modeled periods in the fv argument.
Can Excel PV handle a payment on an irregular date?
PV assumes equally spaced periods and a consistent periodic rate. For irregular dates or exact-day timing, a date-based cash-flow method is generally a better fit than treating the dates as uniform periods.
Related
- Excel Current Year: How to Get It with NOW and TODAY
- Excel NOW Format: Show the Current Date and Time Correctly
- Excel NOW Date: How the Current Date and Time Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS in Excel: Sum Values by Multiple Criteria ExplainedSUMIFS in Excel adds values only when they meet one or more conditions. For example, with sales in…
- VLOOKUP in Excel: How to Apply It and Avoid ErrorsTo apply VLOOKUP in Excel, enter =VLOOKUP(E2,A2:C6,3,FALSE) in a blank cell when the value to find is in…
- Excel Text Join With a New Line: Formulas and TipsTo join text with a new line in Excel, use TEXTJOIN with CHAR(10) as the delimiter. With text…
- Excel: Merge Date and Time Cells Into One Cell With FormulaTo combine a date in A2 and a time in B2 into one Excel date-and-time value, enter =A2+B2…
- VLOOKUP Excel Using Two Spreadsheets: Steps and TipsYes. VLOOKUP can find a value in one Excel workbook and return related information from another; the workbooks…
- Create a Unique Signature in OutlookTo create a unique signature in Outlook, open the signature editor for your version, write a concise sign-off…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.