Calculating yield-to-call on preferred shares

Nov18

Written by:Charles Flock11/18/2014 11:15 AM

In this article we look at shares which pay a quarterly dividend to the preferred shareholders and which are callable at some future date and discuss various techniques for calculating a yield-to-call and explain why using the YIELD function isn't one of them.

Preferred shares are equity securities that may combine the characteristics of a bond with the characteristics of equity ownership. Generally, preferred shares have a preference with respect to dividends in that the preferred shareholders are to be paid a fixed amount per share before any dividends are paid to the common shareholders. In many ways, the preferred dividend looks like the coupon on a bond.

Let's look at the following example. The preferred shares of ABC Company are trading at 21.50. The preferred dividend is 4.9% payable quarterly and the shares are callable at 25.00 on 2017-12-15. We want to calculate the yield-to-call (YTC) on 2014-11-05.

As in calculating the yield on a bond, the calculation of the YTC requires calculating a discount rate for the remaining cash flow such that the discounted cash flow value is equal but opposite in value to the price that we are willing to pay for the security. This is exactly what the XIRR function does.

SELECT

wct.XIRR(amt_cf,dt_cf,NULL)asYTC

FROM (VALUES

('2017-12-15',25.0000 + .049/4*25)

,('2017-09-15',.049/4*25)

,('2017-06-15',.049/4*25)

,('2017-03-15',.049/4*25)

,('2016-12-15',.049/4*25)

,('2016-09-15',.049/4*25)

,('2016-06-15',.049/4*25)

,('2016-03-15',.049/4*25)

,('2015-12-15',.049/4*25)

,('2015-09-15',.049/4*25)

,('2015-06-15',.049/4*25)

,('2015-03-15',.049/4*25)

,('2014-12-15',.049/4*25)

,('2014-11-05',-21.5000)

)n(dt_cf,amt_cf)

This produces the following result.

YTC

----------------------

0.109065274786639

However, the result of the XIRR calculation is an annual rate while yield values are generally quoted to reflect the number of coupon payments per year. In this case, we want to turn that annual rate into a quarterly-compounded rate. The PERIODRATE function provides a very simple way to do this as we can see in the following SQL.

SELECT

wct.PERIODRATE(wct.XIRR(amt_cf,dt_cf,NULL),1,4)asYTC

/*wct.XIRR(amt_cf,dt_cf,NULL) as YTC*/

FROM (VALUES

('2017-12-15',25.0000 + .049/4*25)

,('2017-09-15',.049/4*25)

,('2017-06-15',.049/4*25)

,('2017-03-15',.049/4*25)

,('2016-12-15',.049/4*25)

,('2016-09-15',.049/4*25)

,('2016-06-15',.049/4*25)

,('2016-03-15',.049/4*25)

,('2015-12-15',.049/4*25)

,('2015-09-15',.049/4*25)

,('2015-06-15',.049/4*25)

,('2015-03-15',.049/4*25)

,('2014-12-15',.049/4*25)

,('2014-11-05',-21.5000)

)n(dt_cf,amt_cf)

This produces the following result.

YTC

----------------------

0.104868681753164

Let’s see what happens when we use the YIELD function. Notice that we have multiplied by the price and the redemption values by 4 because the YIELD function assumes that the par value of a bond is 100.

SELECT

WCT.YIELD(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,.049 --@Rate

,21.50 * 4 --@Price

,25 * 4 --@Redemption

,4 --@Frequency

,1 --@Basis

)as[YTC]

This produces the following result.

Y-T-C

----------------------

0.102094789436807

The difference between the results as about .0028, which is pretty substantial. The difference arises because of the conventions used in bond figuration. Bond prices are generally quoted as 'clean' prices, which means that they don't include the accrued interest on the bond as of the settlement date. However, from a cash flow perspective the accrued interest is part of the cash flow for the settlement date. We can easily see this by calling the table-valued function BONDCF which shows how the cash flows for bond relate to the price.

SELECT

CAST(date_pmtasdate)asdate_pmt

,amt_cashflow/ 4 asamt_cashflow

,PVCF/ 4 asPVCF

,cumPVCF/ 4 ascumPVCF

FROM

wct.BONDCF(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,.049 --@Rate

,wct.YIELD(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,.049 --@Rate

,21.50 * 4 --@Price

,25 * 4 --@Redemption

,4 --@Frequency

,1 --@Basis

)--@Yield

,25 * 4 --@Redemption

,4 --@Frequency

,1 --@Basis

,NULL--@Issue

,NULL--@FirstCoupon

,NULL--@LastCoupon

)

This produces the following result. PVCF is the Present Value of the Cash Flow and cumPVCF is the accumulation of the PVCF values.

As you can see, in order to get back to the price of 21.50, the yield calculation assumes that there is accrued interest of 0.171634615384615that is paid to the holder of the bond on 2014-11-05. In the case of preferred shares, however, that is not that case.

XLeratorDB provides another function for calculating yield from price that produces a more meaningful result; DIRTYYIELD. The DIRTYYIELD function works like the YIELD function except that no adjustment is made for the accrued interest; it assumes that the price passed into the function is the 'dirty' price of the bond. This is exactly the calculation that we are looking for.

SELECT

WCT.DIRTYYIELD(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,.049 --@Rate

,21.50 * 4 --@Price

,25 * 4 --@Redemption

,4 --@Frequency

,1 --@Basis

,NULL--@Issue

,NULL--@FirstCoupon

,NULL--@LastCoupon

)as[YTC]

This produces the following result.

YTC

----------------------

0.104946820085109

This is very close to the result returned by the XIRR calculation that we used earlier. If you are wondering what gives rise to the difference, XIRR is always calculating the exact number of days from the settlement date to the coupon and dividing by 365 whereas the DIRTYYIELD calculation calculates the number of days from the settlement date to the next coupon date divided by the exact number of days in the coupon period and then assumes that every other period is exactly the same length.

The following SQL uses the BONDCF function from above and shows how the values for time differ between the 2 calculations, which is why the YTC values are different.

SELECT

CAST(date_pmtasdate)asdate_pmt

,Nas[Number of Periods]

,DATEDIFF(d,'2014-11-05',date_pmt)/365e+00 * 4 as[Time in Years / 4]

FROM

wct.BONDCF(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,.049 --@Rate

,wct.YIELD(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,.049 --@Rate

,21.50 * 4 --@Price

,25 * 4 --@Redemption

,4 --@Frequency

,1 --@Basis

)--@Yield

,25 * 4 --@Redemption

,4 --@Frequency

,1 --@Basis

,NULL--@Issue

,NULL--@FirstCoupon

,NULL--@LastCoupon

)

This produces the following result.

date_pmt Number of Periods Time in Years / 4

---------- ---------------------- ----------------------

2014-11-05 0 0

2014-12-15 0.43956043956044 0.438356164383562

2015-03-15 1.43956043956044 1.42465753424658

2015-06-15 2.43956043956044 2.43287671232877

2015-09-15 3.43956043956044 3.44109589041096

2015-12-15 4.43956043956044 4.43835616438356

2016-03-15 5.43956043956044 5.43561643835616

2016-06-15 6.43956043956044 6.44383561643836

2016-09-15 7.43956043956044 7.45205479452055

2016-12-15 8.43956043956044 8.44931506849315

2017-03-15 9.43956043956044 9.43561643835617

2017-06-15 10.4395604395604 10.4438356164384

2017-09-15 11.4395604395604 11.4520547945205

2017-12-15 12.4395604395604 12.4493150684932

Here are a couple of other ways to calculate the yield-to-call.

First, the ODDFRATE function will calculate the periodic rate for an annuity and we can think of the preferred shares as an annuity where the preferred dividend is the periodic payment, the redemption value is the future value and the price of the shares is the present value of the annuity. We can use some of the other XLeratorDB bond functions to populate the other inputs to the function. Since the function calculates a periodic rate we will need to multiply the result by 4.

SELECT

wct.ODDFRATE(

wct.COUPNUM(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,4 --@Frequency

,1 --@Basis

)--@Nper

,.049/4 * 25 --@pmt

,-21.50 --@PV

,25.00 --@FV

,wct.COUPDAYSNC(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,4 --@Frequency

,1 --@Basis

)/

wct.COUPDAYS(

'2014-11-05'--@Settlement

,'2017-12-15'--@Maturity

,4 --@Frequency

,1 --@Basis

)--@FirstPeriod

)* 4 asYTC

This produces the following result, which is virtually identical to the result produced by DIRTYYIELD.

YTC

----------------------

0.104946820085308

Second, if you want to use the XIRR function but don't want to have to go to the trouble of calculating all the cash flows, you can use the BONDCF function in conjunction with the XIRR function, greatly simplifying the SQL.

XLeratorDB works with SQL Server 2005, 2008, 2012, and 2014 and contains hundreds of useful functions for financial, statistical, and mathematical calculations which are executed directly on your SQL Server database using Transact SQL.

If you are already an XLeratorDB user, just log into your account and download the latest version for free.

If you have any questions or if there is some functionality you would like see added, just send an e-mail to us at support@westclintech.com