The PDURATION function in Google Sheets is a handy financial tool for calculating how long it takes an investment to grow from its present value to a desired future value at a fixed interest rate.
If you’re wondering how to find the number of periods required for your money to reach a certain target, the PDURATION function is what you need.
Let’s walk through how this function works, with real examples and common errors explained.
What Does the PDURATION Function Do in Google Sheets?
The PDURATION function calculates the number of periods (typically years or months) needed for an investment to reach a specific future value based on a constant interest rate.
Use case:
You know the interest rate, the present value (PV), and the future value (FV), and you want to know how long it will take the investment to grow from PV to FV. Use PDURATION for that.
If you instead want to find the interest rate given PV, FV, and the number of periods, use the RRI function instead.
Syntax of the PDURATION Function
PDURATION(rate, present_value, future_value)Arguments
- rate – The interest rate per period (e.g., per year or per month).
- present_value – The current value of the investment.
- future_value – The target or desired value of the investment.
Example 1: Calculate Duration in Years
Scenario:
You invest $10,000 and want it to grow to $15,000 at an annual interest rate of 6%.
=PDURATION(0.06, 10000, 15000)Result:
The formula returns approximately 6.96 years.
Example 2: Calculate Duration in Months
Scenario:
You invest $2,000 and want it to grow to $3,500 at an annual interest rate of 8%. But this time, you want the duration in months.
Step-by-Step Instructions
Input values:
| A | B |
|---|---|
| Rate | 8% |
| PV | 2000 |
| FV | 3500 |
Now divide the annual interest rate by 12 to convert it to a monthly rate:
=PDURATION(B1/12, B2, B3)Result:84.22 months
Alternative to the PDURATION Function in Google Sheets
You can also use a logarithmic formula to calculate the same result manually.
Formula for Years
=(LOG(B3) - LOG(B2)) / LOG(1 + B1)Formula for Months
=(LOG(B3) - LOG(B2)) / LOG(1 + B1/12)This alternative gives you more flexibility if you’re working outside the bounds of the built-in financial functions.
PDURATION Function Errors and Fixes
#NUM! Error
This occurs when:
- The rate, present value, or future value is missing, zero, or less than zero.
#VALUE! Error
This happens when:
- One or more inputs are non-numeric (e.g., text instead of numbers).
- Incorrect formatting of cell references or manual input.
Conclusion
The PDURATION function in Google Sheets is a simple yet powerful way to calculate how long an investment needs to grow to reach a target amount, given a fixed interest rate.
Whether you’re planning long-term investments or working on financial models, PDURATION can help you estimate growth timelines accurately—no manual math required.
