Taryn Nefdt
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.
Steps:
- In order to calculate the PDURATION, we input the following formula in Cell C9:
=PDURATION(C5/12,C6,C7)
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.
Steps:
- Type the following formula in Cell C9:
=PDURATION(C5,C6,C7)
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.
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.
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]
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.