PDURATION function

PDURATION returns the number of periods required for an investment to grow from present value to a target future value at a constant interest rate.

=PDURATION(rate, pv, fv)

Generate a PDURATION formula

Describe what you need. The generator will reach for PDURATION where PDURATION is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How PDURATION reads its arguments
raterequiredpvrequiredfvrequiredPDURATION →
ArgumentRequiredDescription
rateRequiredThe interest rate per period as a decimal (e.g., 0.02 for 2% monthly); must be greater than -1 and cannot equal 0 if pv differs from fv.
pvRequiredThe present value or starting amount; must have the same sign as fv (both positive or both negative) and cannot be zero.
fvRequiredThe target or future value; must have the same sign as pv and must differ from pv for a meaningful result.

Returns

A number representing the count of periods (typically months or years, depending on the rate period).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate months for account credit to reach upgrade threshold

CustomerPlanMRRAccount CreditMonthly GrowthTarget Credit
Acme CorpPro50010000.5%1200
=PDURATION(0.005, 1000, 1200)

Result: 36.55

Acme Corp has $1,000 in account credit earning 0.5% monthly interest from referral rewards. PDURATION calculates it takes 36.55 months for the balance to grow to $1,200. This helps forecast when the customer qualifies for a premium tier upgrade bonus.

2. Determine months until MRR reaches expansion revenue goal

CustomerPlanMRRMonthly Growth RateTarget Monthly Revenue
Gamma LtdEnterprise20001%2200
=PDURATION(0.01, 2000, 2200)

Result: 9.58

Gamma Ltd's Enterprise MRR is growing at 1% monthly due to feature adoption and usage increases. PDURATION shows the MRR reaches the $2,200 target in 9.58 months. This timeline helps sales teams forecast expansion revenue and plan resources accordingly.

3. Calculate quarters for customer balance growth projection

CustomerPlanMRRAccount BalanceQuarterly GrowthTarget Balance
Beta IncBasic2005001.5%750
=PDURATION(0.015, 500, 750)

Result: 27.23

Beta Inc's account balance grows at 1.5% per quarter from seasonal usage patterns. PDURATION calculates 27.23 quarters (about 6.8 years) until the $500 balance reaches $750, useful for long-term financial planning and predictive cash flow models.

Common errors

Which PDURATION error are you seeing?
PDURATION returned an error#NUM!
Ensure both values have the same sign: either both positive or both negative. Use ABS() if needed to normalize values.
#VALUE!
Remove quotes and ensure all three arguments are numeric values, or use VALUE() to convert text numbers to numeric values.
#NUM!
Use a rate greater than -1; typical rates are small positive decimals. If modeling zero-growth scenarios, use NPER instead.
ErrorWhy it happensHow to fix it
#NUM!The pv and fv arguments have opposite signs (e.g., pv is positive and fv is negative).Ensure both values have the same sign: either both positive or both negative. Use ABS() if needed to normalize values.
#VALUE!One or more arguments are text values or non-numeric (e.g., rate is '0.02' as text instead of the number 0.02).Remove quotes and ensure all three arguments are numeric values, or use VALUE() to convert text numbers to numeric values.
#NUM!The rate argument is less than or equal to -1 (e.g., rate = -1), which makes the logarithm calculation undefined.Use a rate greater than -1; typical rates are small positive decimals. If modeling zero-growth scenarios, use NPER instead.

Tips and when to use something else

  • Use PDURATION only for single lump-sum investments with compound interest; for periodic deposits (monthly savings), use NPER instead.
  • PDURATION assumes a constant interest rate—real-world accounts often have variable rates, so results should be treated as projections rather than guarantees.
  • Ensure the rate period matches the result period: a monthly growth rate gives results in months, a quarterly rate gives results in quarters.
  • PDURATION returns decimals; use INT() to round down to whole periods or ROUND(result, 0) for standard rounding.

Related functions

NPERNPER returns the number of payment periods needed to pay off a loan or reach an investment goal at a fixed interest rate.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.RATERATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.PMTPMT calculates the constant periodic payment needed to repay a loan or investment over a fixed number of periods at a fixed interest rate.

Frequently asked questions

When should I use PDURATION versus NPER for subscription scenarios?
PDURATION is for lump-sum amounts (like account balances or credits) with compound interest. Use NPER if you have regular periodic deposits or payments (like monthly subscriptions) and want to know total periods needed.
Why does PDURATION give a decimal number instead of whole periods?
PDURATION calculates the precise mathematical time based on exponential growth. If you need whole periods only, use INT() to round down or ROUND() to round to the nearest integer.
Can PDURATION handle variable growth rates or customer churn?
No, PDURATION assumes a constant rate throughout the entire period. For variable rates or churn scenarios, build a table with dynamic calculations or use XIRR for more complex cash flow analysis.
What does a negative result from PDURATION mean?
A negative result indicates the target value (fv) cannot be reached from the starting value (pv) at the given positive interest rate. This happens when fv is smaller than pv—verify your starting and target values are correctly ordered.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17