With the annual interest rate in A2, the number of annual payments in B2, and each end-of-year payment entered as a negative amount in C2, calculate the present value in Excel with =PV(A2,B2,C2,0,0). For a 5% rate, five annual payments of $1,000, and no separate future value, the result is $4,329.48.
What the Present Value of an Annuity Means
The present value (PV) of an annuity is the value today of a series of equal payments scheduled at regular intervals. It answers a practical question: how much would those future payments be worth now, given an assumed interest or discount rate? A dollar received later is generally valued less than a dollar received today because money available now could earn a return, and because the timing and certainty of future payments matter.
Excel’s PV function handles the standard case of level payments, a constant rate per period, and a fixed number of periods. It can be used to value a payment stream, compare a lump sum with installments, or check the principal amount implied by regular loan payments. The result depends on the rate and timing assumptions entered; it is a mathematical estimate, not a guarantee of what a real contract or investment is worth.
Excel’s PV Function Syntax
PV takes five arguments: rate, nper, pmt, fv, and type. In the example above, A2 is the rate for one payment period, B2 is the total number of periods, and C2 is the payment in each period. The fourth argument, fv, is an optional remaining balance or target value at the end. The fifth argument, type, specifies when payments occur. The two zeroes in the example say there is no additional ending value and payments occur at period-end.
Use a rate and a number of periods that refer to the same interval. If payments are monthly, use a monthly rate and the total number of months. If payments are annual, use an annual rate and the number of years. Enter the annual percentage as a percentage or decimal: for example, enter 5% or 0.05, not 5. Excel interprets 5 as 500%.
The function returns a present value, but its sign follows Excel’s cash-flow convention. Money paid out and money received should have opposite signs. If you enter a positive payment as pmt, PV will normally be negative; if you enter the payment as a negative amount, PV will normally be positive. Neither sign is a calculation error: it identifies the direction of the cash flow from the chosen perspective.
Worked Example: Five Annual Payments
Suppose an agreement pays $1,000 at the end of each year for five years. Assume a 5% annual discount rate, no extra final payment, and the perspective of someone receiving the payments. Put 5% in A2, 5 in B2, and -1000 in C2. With those inputs in A2:C2, the formula is =PV(A2,B2,C2,0,0). It returns about $4,329.48.
The result is below the $5,000 total of the five payments because later payments are discounted more heavily than payments received sooner. As a check, the calculation adds the discounted value of each payment: $1,000 divided by 1.05, plus $1,000 divided by 1.05 squared, continuing through year five. Rounded to cents, those values total $4,329.48. Small differences in displayed results can come from cell formatting, but the underlying calculation keeps additional precision.
To show a positive PV while treating the payments as positive cash inflows, another approach is to negate the function result. For example, with the same assumptions stored in A2:C2, use =-PV(A2,B2,C2,0,0); the displayed result is also $4,329.48. Choose one sign convention and keep it consistent when comparing cash flows.
Ordinary Annuity Versus Annuity Due
An ordinary annuity pays at the end of each period. A typical example is a payment made at the end of each month. An annuity due pays at the beginning of each period, such as rent paid at the start of a month. In PV, type 0 means period-end payments, and type 1 means period-beginning payments. If type is omitted, Excel treats it as 0.
Because each payment in an annuity due arrives one period earlier, its present value is higher when the discount rate is positive. Keeping the same example assumptions in A2:C2, but changing the last argument to 1, use =PV(A2,B2,C2,0,1). The result is about $4,545.95, which is $4,329.48 multiplied by 1.05. This timing choice can materially affect a result, so check the payment dates rather than choosing a type based on habit.
Some arrangements have an initial payment today and then further payments later. Confirm whether the initial amount is part of the stated payment stream before using PV. A payment made immediately is not discounted like one arriving at the end of the first period, and counting it both separately and as an annuity payment would overstate the total.
Monthly Payments, Rates, and Period Counts
For monthly payments, convert both the rate and the period count to monthly terms. A common approximation is to divide a stated nominal annual rate by 12 and multiply the number of years by 12. For example, with an annual nominal rate in A2, years in B2, and a monthly payment in C2, this formula uses those assumptions and a period-end payment schedule: =PV(A2/12,B2*12,C2,0,0).
Be careful about what the quoted annual rate represents. Dividing by 12 is appropriate for a nominal annual rate compounded monthly; it is not automatically the right conversion for an effective annual rate. To convert an effective annual rate into its equivalent monthly rate, the relationship is different. Check the rate definition and compounding terms in the relevant contract or source rather than treating all annual percentages as interchangeable.
The period count must also match the actual schedule. A five-year loan with monthly payments usually has 60 payments, not five. If payments are quarterly for six years, the count is 24. Irregular first or last periods, skipped payments, and changing rates are not represented exactly by a single PV calculation with uniform periods.
Including a Future Value or Balloon Payment
The fv argument lets you include a cash flow or balance at the end of the final period, in addition to the regular pmt amounts. It may be useful when payments leave a balloon balance due at the end of a loan, or when valuing a stream that has a separate terminal amount. Use the same cash-flow sign convention for fv and pmt: amounts received and amounts paid should have opposite signs.
For instance, with rate in A2, periods in B2, regular payment in C2, and a final $500 receipt represented as -500 in D2 from the payer’s perspective, this formula includes that final receipt as well as the regular payments: =PV(A2,B2,C2,D2,0). The number returned is the value today of both components under the entered rate and timing assumptions. If there is no separate ending amount, use zero for fv.
Do not enter the total of all regular payments as fv. The pmt argument already accounts for those repeated amounts. The future-value argument is for a distinct amount remaining or received at the end, not a duplicate of the annuity stream.
Common Errors and How to Check the Result
- Mixing annual and monthly inputs: Pair a monthly rate with the total monthly payment count, or an annual rate with annual periods.
- Using the wrong payment timing: Select type 0 for end-of-period payments and type 1 for beginning-of-period payments.
- Being surprised by a negative answer: Check whether pmt and any fv amount have signs consistent with the cash-flow perspective. A negative PV can be entirely correct.
- Entering a percentage incorrectly: Use 5% or 0.05 for five percent; entering 5 means 500%.
- Rounding inputs too early: Keep the full rate and payment precision in the cells, then set the displayed number of decimal places if needed.
A useful reasonableness check is to compare PV with the undiscounted sum of payments. For positive discount rates and positive future receipts, the present value of an ordinary annuity will generally be less than the total nominal payments. A beginning-of-period stream will be higher than an otherwise identical end-of-period stream. If the result violates the expected relationship, revisit signs, units, number of periods, and timing.
When PV Is Not the Right Excel Tool
PV is designed for equal payments at regular intervals and a constant rate per period. If amounts vary from one date to another, first identify a method that fits the cash-flow pattern. Excel’s NPV function discounts a series of future periodic cash flows and assumes the first listed cash flow occurs one period after the valuation date. A cash flow occurring immediately is usually handled separately rather than included as though it occurred at period-end one.
When the cash flows occur on actual, uneven dates, Excel’s XNPV function can discount them using their dates. Those functions have their own input conventions, so do not substitute them without checking when the first cash flow occurs and how dates are interpreted. If interest rates change over the payment schedule, a single-rate PV may also be an oversimplification; discounting each payment using appropriate period-specific rates may be needed.
PV also differs from FV, which answers what a current amount or payment stream may grow to by a future date. It is not the same as simply adding all payments, and it does not automatically account for fees, taxes, missed payments, inflation, or contract-specific clauses. For real borrowing or investment decisions, verify the actual terms with the lender or a qualified professional. This is spreadsheet guidance, not financial, tax, investment, or legal advice.
Frequently Asked Questions
What is the Excel formula for the present value of an annuity?
Use Excel’s PV function: =PV(rate,nper,pmt,fv,type), replacing the arguments with cell references or values. Match rate and nper to the payment interval, and set type to 0 for end-of-period or 1 for beginning-of-period payments.
Should the payment amount be positive or negative in Excel PV?
It depends on the cash-flow perspective. Excel normally returns the opposite sign from the payment. Enter a negative payment to receive a positive PV, or enter a positive payment and interpret a negative PV as the corresponding outflow.
What is the difference between type 0 and type 1?
Type 0 means payments occur at the end of each period, the ordinary annuity convention and Excel’s default. Type 1 means payments occur at the beginning of each period, making the present value higher when the discount rate is positive.
How do I calculate PV for monthly annuity payments?
Use the monthly rate and the total number of monthly payments. For a nominal annual rate compounded monthly, that commonly means annual rate divided by 12 and years multiplied by 12; confirm the rate’s stated compounding basis before applying that conversion.
Why does Excel return a negative present value?
PV uses cash-flow signs to distinguish money paid from money received. A positive pmt commonly produces a negative PV because the two are treated as opposite directions. The magnitude can still be right; reverse the sign of one cash flow if you want the result displayed from the other perspective.
Related
- Excel Sum of Squares: SUMSQ, Examples, and Common Uses
- Excel SUM and Hidden Cells: What Gets Included in Totals?
- Excel SUM and Multiply: How to Combine Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table of Contents: Create a Clickable Sheet IndexExcel does not have a dedicated command that automatically creates a table of contents for every worksheet. The…
- Excel Table Name: Find, Change, and Use Names in FormulasAn Excel table name is the workbook-level identifier assigned to a formatted Excel table, such as Table1 or…
- Filter Cells by Color in Excel: Steps and TipsTo filter cells by color in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- Excel SUM Function: Does It Count Filtered Values?Yes. In Excel for Microsoft 365, SUM adds every numeric value in its referenced range, including values in…
- Excel Data Validation If-Then Lists: How They WorkYes. In Excel for Microsoft 365 on Windows, an if-then list is usually a dependent dropdown: the choice…
- Compare Rows in Two Excel Sheets: Formulas and MethodsTo compare rows in two Excel sheets, compare corresponding cells when the rows are in the same order,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.