Syntax. MDURATION(Settlement,Maturity,Coupon,Yield,Frequency,Basis)
Definition. This function calculates the figure known as modified duration for fixed-interest securities.
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The date when the repayment of the loan certified by the security takes place.
Coupon (required) The agreed-upon annual interest rate as the price for the borrowed money.
Yield (required) The market interest rate on the settlement day, at which during calculation of the duration all future payments are discounted.
Frequency (required) Because it is quite common to have fixed-interest securities with several interest dates per year, the required argument Frequency supplies the dates-per-year number. Allowed values are: 1 = annual; 2 = biannual; 4 = quarterly.
Basis (optional) The method you want to use for determining the days in the year according to Table 15-2, shown earlier. If this argument is omitted, Excel calculates with Basis = 0.
All function arguments that take a date use the date without time; fractions are rounded. The arguments Frequency and Basis also require integers, and decimal places are truncated.
If invalid dates are used or no numbers are entered where required, the function returns the error #VALUE!. If invalid numbers are entered for nondate arguments, the function returns the #NUMBER! error.
Background. Volatility, or price changes as a reaction to market interest changes, is an important figure for portfolio risk management. Unlike with shares and options, the range of fluctuation for fixed-interest securities decreases towards the end of their duration, because the disbursement amount at the maturity date is fixed.
By using differential calculus, you can determine that the following applies to fixed-interest securities:
Duration is the security’s Macauley Duration (see DURATION()).
MDURATION() now returns the factor with which the market interest change must be multiplied (unsigned) to calculate the relative price change.
If there are several interest payments per year, the market interest (annual interest rate) is evenly distributed over the interest periods.
Example. A 4.500 percent federal loan initiated in 2003 had a yield of 0.61 percent on August 31, 2010. It will mature on January 4, 2013. Your price on that day was $109.027 (example annual interest rate for the function PRICE()).
If you perform the price calculations of the aforementioned example one more time, but this time with a yield that has been adjusted up by 0.5 percentage points to 1.11 percent (this is the market interest change you expect in the near future), the price changes to 107.800. This means a price loss of 1.13 percent.
You can get approximately the same result by calculating the duration with MDURATION(). With the loan data, MDURATION() results in 2.208. If you multiply this number with the 0.5 percentage points of market interest change, you get a relative price change of 1.1 percent. Because of the mathematical sign, this leads to a price loss of 1.204, which corresponds to an (approximate) future price of $107.824.
Therefore, the figure for modified duration can be supplied together with the price of a loan, so an investor is able to evaluate the structure of a deposit from a risk point of view without extensive recalculation.