Excel PV Formula Not Working? Common Causes and Fixes

The Excel PV formula usually seems wrong because its interest rate and payment periods do not use the same time unit, its cash-flow signs are reversed, or a cell contains text instead of a number. Check those inputs first, then verify whether payments happen at the beginning or end of each period and whether Excel is recalculating the workbook.

What the PV function calculates

PV stands for present value: the value today of a future amount or a series of future cash flows, discounted at a specified rate. Excel’s PV function is useful for estimating the value of equal, regularly spaced payments, such as monthly loan payments or annual contributions. It does not automatically work out a loan payment, and it does not calculate the value of irregularly timed cash flows.

The function’s argument order is rate, nper, pmt, fv, and type. The first three arguments are needed; the last two are optional. Rate is the interest rate for one payment period, nper is the total number of periods, and pmt is the payment made each period. The optional fv is the balance or target amount remaining after the final payment. Type identifies when each payment occurs: 0 means at the end of a period, while 1 means at the beginning. If type is omitted, Excel assumes 0.

A frequent source of confusion is that PV returns a cash-flow value, not necessarily a positive number. Excel’s financial functions use a cash-flow sign convention: money received and money paid out should generally have opposite signs. For a loan, the money received upfront can be positive while the repayments are negative. If all cash flows are entered with the same sign, the result may have a sign that looks backwards even though Excel has calculated consistently.

Check that rate and periods match

The most common reason for a surprising result is mixing annual and monthly values. If payments are monthly, rate must be the rate per month and nper must be the number of months. Entering an annual rate with a count of monthly payments discounts each payment at the annual rate, which can make the calculated present value much too low.

For example, assume B1 contains an annual nominal rate of 6%, B2 contains a three-year term, and B3 contains a monthly payment of -$500. The payment sign is negative because it represents money paid out. With monthly payments and no final balance, the formula is:

=PV(B1/12,B2*12,B3)

It discounts at 0.5% per month for 36 months and returns approximately $16,435. The result is positive because the payment stream is opposite in sign to the present value. If you instead used B1 as the rate and B2 as nper, Excel would interpret 6% as the rate per payment period and three as the number of payments—not as a 6% annual rate over three years of monthly payments.

For an annual payment schedule, use an annual rate, a count of years, and an annual payment. For a weekly schedule, convert the rate and number of periods to weekly terms in a way that matches the rate definition. Do not simply divide every annual rate by 52 without checking whether it is a nominal rate or an effective annual rate; those are not interchangeable. For calculations that need to follow a particular contract, use the rate and compounding convention specified in that contract.

Use the correct payment signs

PV can return a negative value without an error. That is often a sign-convention issue, not a calculation failure. When the periodic payments are entered as negative outflows, PV will usually be positive, representing the amount received now that is equivalent to those payments. Reverse the signs of all cash flows and the PV sign reverses too, while the magnitude stays the same.

Decide what perspective the worksheet represents before changing signs. A borrower who receives a loan and then makes payments has an initial inflow and later outflows. A lender sees the opposite. Either perspective can be modeled, but the inflow and outflow signs need to be consistent. Avoid “fixing” a negative result by adding a minus sign without understanding the cash flows; that can hide a reversed input or make other calculations inconsistent.

Also check the sign of fv if you include it. For example, a loan with a balloon payment at the end has a final payment or remaining balance that belongs in the model. Entering that future value with a sign inconsistent with the periodic payments can change the result in an unexpected way. PV is a mathematical model of the cash flows provided; it cannot infer whether a payment represents money received, money paid, or a balance still owed.

Confirm payment timing and optional arguments

If payments happen at the start of each period, use type 1. This applies to an annuity due, such as rent paid at the beginning of each month. If payments happen at the end, use type 0 or leave type blank. For the same rate, payment amount, and number of periods, beginning-of-period payments have a higher present value because each payment is received or paid sooner.

Using the same layout as the monthly example—annual rate in B1, years in B2, and monthly payment in B3—the beginning-of-month version is:

=PV(B1/12,B2*12,B3,0,1)

It returns approximately $16,517 for the example values, about 0.5% more than the end-of-month result because the payments occur one monthly period earlier. If the difference between your result and a calculator is modest, check the timing assumption before assuming Excel is broken.

Leaving fv out makes Excel assume a final value of zero. That is appropriate for many fully repaid loans, but not for every arrangement. If a balance remains after the last regular payment, or if an investment has a specified future value, include that amount in the model with a sign consistent with the other cash flows. A blank cell used as an optional argument behaves differently from deliberately choosing a nonzero final value, so inspect the actual cell references and values.

Check for text, wrong references, and formula-entry errors

Excel may not recognize an input as a number if it was imported as text, contains a leading apostrophe, or includes extra characters such as a currency symbol typed into the cell. A cell that displays 6% might be numeric, but formatting alone does not prove that the underlying value is numeric. Select the cell and inspect the formula bar; if a supposedly numeric input is left-aligned or has a warning indicator, check its source and convert it to a number when appropriate.

Check that the formula points to the intended rate, term, and payment cells. A copied formula may have shifted references.
Confirm that a rate of 6% is stored as 6% (0.06), not as the number 6. Entering 6 as the rate means 600% per period.
Make sure nper is a positive count of periods, not a number of years when payments are more frequent than yearly.
Look for a leading apostrophe or a space before the equals sign. Either can make an entry display as text instead of calculating.
Check that the formula’s commas match your Excel regional settings. Some installations use semicolons as argument separators; if Excel rejects a formula copied from elsewhere, use the separator your installation expects.

On Excel for Microsoft 365 for Windows, a formula displayed literally in the cell may also mean Show Formulas is enabled, rather than that PV itself is failing. Press Ctrl+` to toggle formula display, or check the Formulas tab. On Mac, the function and financial logic are the same, though keyboard shortcuts and regional separator settings can differ. In Excel for the web, check the formula bar and cell format if the expression appears as text.

Look at calculation settings and result formatting

If the inputs change but the displayed answer does not, the workbook may be set to manual calculation. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options > Automatic. Excel for Mac also provides calculation options on the Formulas tab, though the surrounding interface can vary by release. In Excel for the web, check the calculation options available on the Formulas tab. After switching to automatic, allow the workbook to recalculate; a large workbook may take a moment.

Formatting can disguise a valid result. A cell formatted as a date, percentage, or rounded currency may display a value in a way that looks incorrect. Change the result cell to General or Number temporarily, then apply an appropriate currency or accounting format. Increase the displayed decimal places if you are comparing the value with another tool. Formatting changes how a value appears, not the underlying PV calculation.

Check for a formula error as well as a plausible-looking wrong answer. A #VALUE! error often points to an argument Excel cannot interpret as numeric. A #NAME? error can indicate a misspelled function name or an unrecognized name in the formula. A formula rejected at entry may have a separator or bracket problem. Correct the underlying input or formula rather than replacing an error with a hard-coded number.

Know when PV is not the right tool

PV assumes regular periods and equal payments when pmt is used. If cash flows are irregular—different amounts or dates that do not occur at fixed intervals—the basic PV function may not represent the schedule you have. For dated, irregular cash flows, Excel has the XNPV function, which discounts a series of values using their dates and a 365-day year convention. Use a function whose assumptions match the cash-flow pattern rather than forcing irregular payments into equal periods.

PV also does not determine an appropriate interest rate, account for fees that are not included in the cash flows, or decide how a lender defines compounding. It only applies the rate and payment assumptions supplied. For example, a quoted annual percentage rate, an effective annual yield, and a nominal rate compounded monthly can have different meanings. Check the relevant agreement or source for the intended rate basis. This is spreadsheet guidance, not financial, tax, investment, or legal advice; verify real loan terms with the lender or a qualified professional.

A practical troubleshooting sequence

When PV seems wrong, work through the inputs in a fixed order instead of repeatedly changing the formula. That makes it easier to isolate whether the issue is timing, units, signs, data type, or workbook behavior.

Write down the payment frequency, interest-rate basis, total number of payments, and payment timing.
Convert the rate and period count to the same frequency. For monthly payments, use a monthly rate and total months.
Set inflows and outflows to opposite signs, choosing either the borrower’s or lender’s perspective consistently.
Confirm whether payments are at the start or end of each period and set type accordingly.
Inspect the referenced cells for text, wrong values, shifted references, or an unexpected final balance.
Check that Excel is calculating automatically and that the result cell’s number format is not misleading.
Compare with a small independent calculation or a reliable lender illustration, using exactly the same assumptions.

For a useful comparison, match all assumptions rather than comparing only the final number. A calculator may default to monthly payments, end-of-month timing, no balloon balance, or a different rate convention. A difference does not necessarily mean Excel is wrong; it may mean the two calculations describe different cash-flow schedules.

Frequently Asked Questions

Why does Excel PV return a negative number?
PV may be negative because its sign follows the cash-flow signs you entered. If payments are positive, Excel can return a negative present value. Use opposite signs for money received and money paid, based on whose perspective the model represents.

Why is my PV result much smaller than expected?
Check whether you entered an annual rate with monthly payments, or used years instead of the total number of monthly periods. A rate applied once per payment period can drastically change the result if its time unit is wrong.

Should I divide the interest rate by 12 in Excel PV?
If the input is a nominal annual rate and payments are monthly, dividing that annual rate by 12 gives a monthly rate, and the number of periods should be months. If the rate is an effective annual rate or the contract specifies another convention, confirm the correct conversion before using it.

What does type 1 mean in the PV formula?
Type 1 means each payment occurs at the beginning of its period. Type 0, or leaving type blank, means payments occur at the end. Rent and other payments due in advance may use type 1, while many loan payments are modeled at period-end.

Why does Excel show the PV formula instead of an answer?
The entry may be stored as text, may begin with an apostrophe, or may have a space before the equals sign. Show Formulas may also be turned on. Check the cell contents and formula-display setting, then confirm that the workbook is set to calculate.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.