YIELDMAT

• How to use YIELDMAT formula?
• Examples of using YIELDMAT formula
• YIELDMAT formula not working?
• Similar formulas to YIELDMAT

Usage

Use the YIELDMAT formula with the syntax shown below, it has 5 required parameters and 1 optional parameter:

=YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention])

1. settlement (required):
   The settlement date of the security. This is the date when the buyer takes possession of the security and pays the seller.
2. maturity (required):
   The maturity date of the security. This is the date when the issuer must repay the face value of the bond to the holder.
3. issue (required):
   The issue date of the security. This is the date when the security was originally issued.
4. rate (required):
   The annual coupon rate of the security as a decimal.
5. price (required):
   The price of the security per $100 face value.
6. day_count_convention (optional):
   Optional. The day count convention to use for calculating the fraction of a year between settlement and maturity. This can be either a string or a number. If omitted, the default day count convention is 30/360.

Examples

Estimate the yield of a discount bond

The YIELDMAT function can be used to estimate the yield of a discount bond, which is a bond that is initially sold at a price below its face value. For example, suppose you purchase a $1,000 bond with a 5% annual coupon rate for $950 that matures in 2 years. You can use the YIELDMAT function to estimate the yield of this bond based on the settlement date, maturity date, issue date, coupon rate, and price.

Calculate the yield of a security that pays interest at maturity

The YIELDMAT function can be used to calculate the yield of a security that pays interest at maturity, such as a zero-coupon bond or a Treasury bill. For example, suppose you purchase a Treasury bill with a face value of $1,000 that matures in 3 months and is currently trading at a price of $990. You can use the YIELDMAT function to calculate the yield of this security based on the settlement date, maturity date, issue date, and price.

Common Mistakes

Incorrect order of arguments

One of the most common mistakes when using YIELDMAT formula is to input the arguments in the wrong order. Make sure to input the arguments in the correct order, which is settlement, maturity, issue, rate, price, [day_count_convention].

Invalid settlement date

Another common mistake is to input an invalid settlement date. Make sure to input a valid date in the settlement argument.

Invalid maturity date

Similarly, an invalid maturity date can cause the formula to return an error. Make sure to input a valid date in the maturity argument.

Invalid rate

If the rate argument is not a number, the formula will return an error. Make sure to input a valid number in the rate argument.

Invalid price

Similarly, if the price argument is not a number, the formula will return an error. Make sure to input a valid number in the price argument.

Related Formulas

The following functions are similar to YIELDMAT or are often used with it in a formula:

• YIELD

  The YIELD function calculates the yield of a security that pays periodic interest. The yield is the annualized percentage rate returned on the bond, assuming the bond is held until maturity. This function is commonly used in finance and investment analysis.

• PRICE

  The PRICE function calculates the price per $100 face value of a security that pays periodic interest. It is commonly used to determine the current value of a bond. The function takes the settlement date, maturity date, annual coupon rate, yield, redemption value, and frequency of coupon payments as input. It returns the price of the security, which is the sum of the present value of the coupon payments and the present value of the redemption value.

• DURATION

  The DURATION function calculates the Macauley duration of a security paying periodic interest, such as a US Treasury Bond, based on expected yield. The Macauley duration is a measure of the sensitivity of the price of the security to changes in interest rates. This function is commonly used in finance and investment analysis.

Learn More

You can learn more about the YIELDMAT Google Sheets function on Google Support.