Syntax. YIELDMAT(Settlement,Maturity,Issue,Rate,Pr,Basis)
Definition. This function calculates the annual yield of a fixed-interest security in the intra-annual area (without compound interest).
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The maturity date of the security.
Issue (required) The date of the emission of the security.
Rate (required) The agreed-upon nominal interest rate of the security based on one year.
Pr (required) The percentage of the par value of a security (as if it was 100 monetary units).
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.
The arguments of the function have the following requirements:
Settlement, Maturity, and Issue require a date specification without a time; decimal places are truncated. Any decimal places of Basis are also truncated to make the number an integer.
If Settlement, Maturity, or Issue does not have a valid date, YIELDMAT() returns the
#VALUE!error. The settlement date must be earlier than the maturity date.If Rate or Pr is less than zero, YIELDMAT() returns the error
#NUMBER!.If Basis cannot be resolved to a number from 0 through 4, YIELDMAT() returns the
#NUMBER!error.
Important
Excel Help contains the following note:
“Dates should be entered with the DATE() function or as results of other formulas or functions. Problems might occur if dates are entered as text.”
Background. To implement the finance mathematical benefit principle
Payment of the creditor = Payment of the debtor
for the start of the transaction, the price of a fixed-interest security plus potential accrued interest equals the cash value of the debtor’s future payments certified in this security. The price is the percentage of the security’s par value, as if the par value was 100 monetary units. In this case, the future payment is the repayment plus the nominal interest. There is no compound interest, because evaluations take place only for the intra-annual timeframe. A divided interest rate according to the duration is used for the discount calculation instead of a divided duration in the exponent (ISMA method).
To calculate the defined yield, the difference between the future payments of the debtor and the current payment of the creditor (purchaser of the security) must be put into a relation to this current payment:
The interest is calculated from the annual nominal interest rate in relation to the difference between the maturity date and the emission date. The accrued interest is calculated in the same way, only here the time between the purchase date and the emission date is important. The determined ratio becomes the yield when it is extrapolated to the entire year.
Example. A federal bond issued in the year 2010 has received a coupon of 5.250 percent. The maturity date was specified as January 4, 2011. On August 31, 2010, the price was 101.670. How high was the yield at that time? Using the known data with YIELDMAT() returns 0.39 percent. You also get the same result by using the function YIELD().