Excel PDURATION Function

PDURATION is an Excel function that calculates the time needed for an amount of money invested at a specific rate to grow to a specified amount.

PDURATION is different than NPER function because PDURATION finds time it takes a single sum at =0 to growth to a certain amount at t=PDURATION while NPER finds out the total number of periods/payments in a stream/series of cash flows.

Any investment or loan based on compound interest grows exponentially because interest credited or charged in the first period is added to the principal balance and interest credited and charged in each subsequent period is based on the amount inclusive of interest.

Let’s say you invest $10,000 at 5% per annum compounded semiannually. The interest earned in the first half-year on your investment equals $250 (=$10,000 × 5% × ½) and your investment balance after the first half-year is $10,250 (=$10,000 + $250). In the second half-year, your interest income is $256.25 (=$10,250 × 5% × ½) and your investment balance is $10,506.25 (=$10,250 + $256.25). Your investment income in the third and fourth half-years is $266.66 and $269.22 respectively. You might be waiting for your investment to reach $20,000 so that you can buy a new car. You are eager to know how long it will take. PDURATION tells you just that.


PDURATION(rate, pv, fv)

RATE refers to the periodic interest rate at which an investment or loan grows. It equals the nominal annual percentage rate divided by the number of compounding periods per year.

PV means the initial sum of investment or loan.

FV refers to your target investment or loan balance.

PDURATION calculates n in the below equation:

$$ FV=PV\ \times{(1+i)}^n $$

The result is not in years necessarily but in the same time unit for which we entered the interest rate.


Considering the facts given above: you have $10,000 invested at 5% compounded semiannually. Find out time it will take the investment value to grow to $20,000. The following screenshot shows how to use PDURATION in such a situation:

Excel PDURATION Function

The result of PDURATION is in the same time unit as the periodic interest rate. If the interest rate entered is semiannual, PDURATION result is in half-years and if monthly interest rate is used, PDURATION returns time in months.

In the above example since the periodic interest is for semiannual period, PDURATION is in half-years.

Time in years = PDURATION/m = 28.07/2 = 14.035 years.

Written by Obaidullah Jan