How to Use PDURATION Function in Excel (2 Practical Examples)

In financial calculations, we often need to find different kinds of durations for loans or investments. It helps to take decisions for loans or investments by evaluating the time. As time is a big factor in financial operations. So, today we are gonna show you the use of function- PDURATION (and MDURATION function) in Excel which deal with time.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


How to Use PDURATION Function in Excel

The PDURATION Function is a new financial function that was introduced in Excel 2013 and it calculates the number of periods required for an investment to reach a designated future value, at a constant interest rate.


Syntax of Excel PDURATION Function

The syntax of the PDURATION function is:

=PDURATION(rate, pv, fv)

  • Arguments Explanations:
Argument Required/Optional Explanation
Rate Required Refers to the interest rate per period.
pv Required Present value of the investment.
fv Required Future value of the investment.

Examples of PDURATION Function in Excel

Example 1: Calculate Monthly Periods from Yearly Rate

Assume, we want to calculate the number of months, needed by an investment of $2500, earning 3.7% interest annually, to reach $3500.

Example of PDURATION Function: Calculate Monthly Periods from Yearly Rate

Steps:

  • In order to calculate the PDURATION, we input the following formula in Cell C9:
=PDURATION(C5/12,C6,C7)

Formula with PDURATION Function: Calculate Monthly Periods from Yearly Rate

Here, we divide the interest rate which is an annual interest rate by 12 since we want to ultimately obtain the monthly periods. Note, however, that all values for PDURATION are positive.

  • Upon pressing ENTER button, we get a value of 109.29 months.

This value means that it would take 109.29 months, for an investment with an initial value of $2500 to reach a future value of $3500.


Example 2: Calculate Monthly Periods from Monthly Rate

Now assume, we want to find the number of months, needed by an investment of $3200, earning a 1.5% monthly interest rate, to reach $55500.

Example of PDURATION Function: Calculate Monthly Periods from Yearly Rate

Steps:

  • Type the following formula in Cell C9:
=PDURATION(C5,C6,C7)

Formula with PDURATION Function: Calculate Monthly Periods from Yearly Rate

Here, we didn’t need to divide the rate by 12 as it was a monthly rate.

  • Finally just hit the ENTER button and you will get the monthly period of 36.38 months.


Extra Feature: What Is MDURATION Function in Excel & How to Use It?

The MDURATION function is used to determine the duration for security by applying the modified Macauley method along with an assumed par value of $100.


Syntax of Excel MDURATION Function

The syntax of the MDURATION function is:

=MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

  • Arguments Explanations:
Argument Required/Optional Explanation
Settlement Required The settlement for the security date. It is the date after the issue date when the security is traded to the buyer.
Maturity Required The maturity date of the security. When the security date expires then we call it the maturity date.
Coupon Required The annual coupon rate of security.
Yld Required The annual yield of the security.
Frequency Required The count of coupon payments per year. For annual, it is = 1; for semiannual, it is = 2; for quarterly, it is = 4.
Basis Optional The type of day counts.

Example of MDURATION Function

Now assume that, the price of a bond is $15,000, it pays a 5% coupon rate, and get matures in 2 years. The rate of interest is 3%. The frequency of the coupon is twice in a year.

How to Use MDURATION Function in Excel

Steps:

  • Insert the following formula in Cell C11
=MDURATION(C5,C6,C7,C8,C9)

  • After hitting the ENTER button, we’ll get the mduration- 1.90.


Things to Remember

  • While using the PDURATION function, don’t forget to divide the interest rate according to the type of period whether it is quarterly, monthly or yearly basis.
  • You must have to insert the dates by storing as a date format for the Settlement and Maturity arguments, otherwise, the MDURATION formula can return an error. That means, you will have to select the format of the cells as a date and then insert the date.

Conclusion

That’s all for the article. I hope the above procedures will be good enough to use Excel’s PDURATION, and MDURATION functions. Feel free to ask any questions in the comment section and give me feedback. Visit ExcelDemy to explore more.

Taryn Nefdt

Taryn Nefdt

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

2 Comments
  1. Dear Ms. Taryn N,
    I have a assuming problem in Excel (using PMT function) as below:

    “Mr. Adam wants to sign an insurance contract. He expects to earn $110000 after 18 years at 15% annual interest rate. How much money he must pay monthly for the insurance company, knowing that when signing the contract Mr. Adam paid $10000?”

    rate: 15%/12
    nper: 18*12
    pv: 10000
    fv: 110000

    I tried calculate PMT in 4 formulars:
    1. =PMT(15%/12,18*12,10000,110000)
    2. =PMT(15%/12,18*12,-10000,110000)
    3. =PMT(15%/12,18*12,10000,-110000)
    4. =PMT(15%/12,18*12,-10000,-110000)
    I do not know which formular is right
    I’m very confuse in using PMT function, especially using PV and FV parameter sign (- or +).
    Could you explain to me the problem above?
    I’ve found and read Excel’ help and other examples on the web but still not sure exactly.

    Thank you so much and looking forward to hearing from you!
    Your student,
    Hai. [email protected]

  2. Hi HAIVH
    Let me first clear up your confusion.
    • Normally, when you pay a certain amount, you have cash outflow. Hence the amount you pay becomes negative.
    • On the other hand, when you receive a certain amount, you have cash inflow. Hence the amount you get becomes positive.
    Coming to your problem,
    • PMT function is used to calculate the monthly payment for a loan. This problem is not a case of loan payment. So, I think the PMT function is not suitable to be used here.

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo