How do you calculate modified duration in Excel?
Enter “Modified Duration” into cell A8 and the formula “=MDURATION (B2, B3, B4, B5, B6, B7)” into cell B8. The resulting modified duration is 7.59.
How do I create a formula in Excel for duration?
Another simple technique to calculate the duration between two times in Excel is using the TEXT function:
- Calculate hours between two times: =TEXT(B2-A2, “h”)
- Return hours and minutes between 2 times: =TEXT(B2-A2, “h:mm”)
- Return hours, minutes and seconds between 2 times: =TEXT(B2-A2, “h:mm:ss”)
How do you calculate modified duration?
To find the modified duration, all an investor needs to do is take the Macaulay duration and divide it by 1 + (yield-to-maturity / number of coupon periods per year).
What is Mduration function in Excel?
Excel’s MDURATION function returns the modified Macauley duration for an assumed par value of $100. The Macaulay duration is the weighted average term to maturity of the cash flows from a security, which can be calculated with Excel’s DURATION function.
How do I use the MIRR function in Excel?
The MIRR function syntax has the following arguments:
- Values Required. An array or a reference to cells that contain numbers.
- Finance_rate Required. The interest rate you pay on the money used in the cash flows.
- Reinvest_rate Required. The interest rate you receive on the cash flows as you reinvest them.
What is modified duration example?
For example, assume bank A and bank B enter into an interest rate swap. The modified duration of the receiving leg of a swap is calculated as nine years and the modified duration of the paying leg is calculated as five years. The resulting modified duration of the interest rate swap is four years (9 years – 5 years).
How do I calculate the difference between two times in Excel?
There are two approaches that you can take to present the results in the standard time format (hours : minutes : seconds). You use the subtraction operator (-) to find the difference between times, and then do either of the following: Apply a custom format code to the cell by doing the following: Select the cell.
What is the difference between modified duration and Macaulay duration?
The Macaulay duration calculates the weighted average time before a bondholder would receive the bond’s cash flows. Conversely, the modified duration measures the price sensitivity of a bond when there is a change in the yield to maturity.
What is MIRR formula?
MIRR = (Future value of positive cash flows / present value of negative cash flows) (1/n) – 1.
What is the MIRR function?
The Modified Internal Rate of Return (MIRR) is a function in Excel that takes into account the financing cost (cost of capital) and a reinvestment rate for cash flows.