Syntax. YIELD(Settlement,Maturity,Rate,Pr,Redemption,Frequency,Basis)

Definition. This function determines the yield of a fixed-interest security (loan).

Arguments

The arguments of YIELD() have the following requirements:

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 (loan) 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.

Because the price is usually determined by market supply and demand, the benefit principle helps you calculate the yield that is used to determine the price with a cash value calculation that corresponds to the market price. This internal interest payment is called yield. There is no general resolution formula for this. You need to use a mathematical approximation. With a detailed calculation schema, as shown in the examples later in this section, Excel provides the target value search for solving the equation.

The cash value calculation is no problem, if the purchase date (change of ownership) of a security with an annual interest payment is the same as the day of the interest payment. In this case, only the whole year needs to be taken into consideration. A situation in which the change of ownership takes place between interest due dates, or in which interest payments take place several times a year, is not so easy. In finance mathematics there are several ways to deal with a year that is broken up into parts. The best known methods are Moosmüller and Braess/Fangmeyer, as well as ISMA.

The ISMA method returns the same as YIELD() for a one-time interest payment per year, and YIELD() can easily be recreated for biannual and quarterly interest payments.

In Excel terminology and for the functions derived from it, the benefit principle of creating cash values (discounting future payments) for price calculation by Excel works as shown on the next page.

COUPNUM is the number of interest payments after the purchase, COUPDAYSNC is the number of days until the next interest payment date, and COUPDAYBS is the number of days since the last interest payment date.

If Frequency = 1, this formula also represents the ISMA method. If Frequency equals 2 or 4, Excel calculates with an even distribution of the yield over the periods within the year. The ISMA method, however, uses a period apportionment following the correlation between nominal and effective interest, which you can find in the function descriptions for EFFECT() or NOMINAL():

In case of several interest payments per year, you can therefore calculate the yield by using the ISMA method with YIELD(). Afterwards, you need to convert the “effective” Excel yield by using the function EFFECT().

Examples. The following examples explain the YIELD() function.

Annual Interest Payment (Coupon). A 4.500 percent federal loan issued in 2003 had a price of 109.010 on August 31, 2010. It matures on January 4, 2013. Which yield did you find in the corresponding notifications or daily papers?

If you want to determine the ISMA yield with the Exact/Exact day counter method without using the function YIELD() (that is, you want to learn what exactly YIELD() is implementing), you can prepare a worksheet as shown in Figure 15-9.

Figure 15-9. Preparing price and yield calculations.

Enter the known data into the first seven cells from C6 to C10. In preparation, enter the day counter mode by typing 1 (this corresponds to Basis = 1 in all of the integrated functions that will be used) and the frequency of the interest payments.

Then use =COUPNCD(C6,C9,C12,C11) to determine the next coupon payment date: January 4, 2011. The formula =COUPDAYSNC(C6,C9,C12,C11) helps you count the days up to this interest date: 126. To calculate the accrued interest, you need the days since the last interest payment date (=COUPDAYBS(C6,C9,C12,C11) counts 239 here) so that you can use =C10*C16/C17*C8 to determine an amount of $2.95. The number of the remaining interest dates, which you need in order to discount the future payments, is determined with =COUPNUM(C6,C9,C12,C11) and returns 3.

Now let’s look at discounting. The PV()function helps you here. You can use the formula =PV(C27,C19-1,- C10*C8,-C10*C8,1) as a yield calculation to determine the cash value of future interest payments (the result is 13.42) and as a compound interest calculation for discounting the repayment amount with =PV(C27,C19-1,0,-C8) to get 98.78. For discounting, you first use the assumed yield (4.5 percent) in C27, which can cause different number sequences in both cash values.

You now need to follow the ISMA procedure to determine the sum of the two cash values over the fractional duration of 126 of 365 days with a fractional exponent in the interest formula: =PV(C27,C15/C17,0,-C21-C20) returns 102.92.

Now you compare the sum of the price and the accrued interest (C23) with the sum of the calculated cash values (C24) by creating the difference in C25. On this cell, you run the goal seek with a target value of 0 and the modifiable cell C27 (where your yield assumed thus far is located). The result is the same as can be determined with =YIELD(C6,C9,C10,C7,C8,C12,C11): 0.617%.

Biannual Interest Payment (Coupon). There are only a few loans that have more than one interest payment date per year. In the sample files for this function, the loan of the previous example was therefore given a fictitious biannual interest payment, so the calculations could be performed. The Excel price has a different number of decimal places than the ISMA price. If you want to determine the ISMA yield, you need to evaluate the number calculated with YIELD() (it is twice the correct ISMA biannual yield) with EFFECT(YIELD(),Frequency) according to the periods.