YIELD Excel Formula

Author Zaheer    Category Formulas     Tags ,

Microsoft Excel is considered as a great tool in storing and managing data or group of data. Great proof to this is that almost all companies utilize it. According to most businesses and companies, the program serves as their database. The program allows them to alter information anytime of the day. Aside from storing data, the program also serve as their tool in computing mathematical equations. In order to use the program easily, diverse functions of the program should be learned first. Moreover, function’s syntax and codes should also be considered. In terms of accounting computations, one of the functions that users should recognize is the YIELD function.

Description of YIELD Function:

The function is used to compute the value of the yield of a certain bond. It calculates the security yield that pays period interest.

In order for users to get correct result, accurate syntax must be utilized since Microsoft Excel is a program that works in accordance to the formula that was keyed in. For YIELD function, the formula to use is:

=YIELD(settlement, maturity, rate, pr, redemption, frequency, basis)

Where in the arguments are:

  • settlement – is the security date of settlement. The value for this is the date after the buyer purchased the security.
  • maturity – this is the date that security will get mature or the date when security will expire.
  • rate – this is the rate of the yearly coupon of the security.
  • pr – this is the cost of security per $100 par value.
  • redemption – refers to the value of security redemption per $100 face value.
  • frequency – this the value of payment for the coupon every year.  For yearly payment it is equal to 1, for semiannual it’s equal to 2 and for quarterly it is equivalent to 4.
  • basis – this is the kind of day count basis used during the computation.

YIELD Function Example

To understand more how it is used in Excel, take a look at the given example below:

The coupon was purchased on September 11, 2011 and will mature on December 31, 2011 with a rate of 9%. The cost of the coupon per $100 par value is $101 and the value of redemption is $100. The payment is done yearly using the US day count basis which is 30/360 days.

In this given problem, the known values are as follows:

settlement – September 11, 2011

maturity – December 31, 2011

rate – 9%

pr – $101

redemption – $100

frequency – 1

basis – 0

To use Excel in solving the problem, follow the following instructions:

  • Type in first the data written above. Observe the rule of inputting one data per cell.
  • After keying in all the needed data, click the cell where the answer will appear and follow the syntax for YIELD function. Click the cell that corresponds to syntax arguments.
  • After typing in the value required, press the enter key.
  • Immediately the answer will appear having the value of 0.0526 or 5.26%

These are the procedures on how to compute the yield value of a security bond. If there are errors obtained, check the formula because frequent errors are obtained of keying in the wrong value on the formula.

Post comment