Calculating yield-to-call on preferred shares
Nov
18
Written by:
Charles Flock
11/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) 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.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) as YTC
/*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_pmt as date) as date_pmt
,amt_cashflow / 4 as amt_cashflow
,PVCF / 4 as PVCF
,cumPVCF / 4 as cumPVCF
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.
date_pmt amt_cashflow PVCF cumPVCF
---------- ---------------------- ---------------------- ----------------------
2014-11-05 -0.171634615384615 -0.171634615384615 -0.171634615384615
2014-12-15 0.30625 0.302875957795737 0.131241342411121
2015-03-15 0.30625 0.295337844045598 0.426579186456719
2015-06-15 0.30625 0.287987342278013 0.714566528734732
2015-09-15 0.30625 0.280819783121152 0.995386311855884
2015-12-15 0.30625 0.273830613416622 1.26921692527251
2016-03-15 0.30625 0.267015393327093 1.5362323185996
2016-06-15 0.30625 0.260369793515914 1.79660211211551
2016-09-15 0.30625 0.253889592396924 2.05049170451244
2016-12-15 0.30625 0.247570673452704 2.29806237796514
2017-03-15 0.30625 0.241409022619581 2.53947140058472
2017-06-15 0.30625 0.235400725737715 2.77487212632244
2017-09-15 0.30625 0.229541966064645 3.00441409238708
2017-12-15 25.30625 18.4955859076238 21.5000000000109
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_pmt as date) as date_pmt
,N as [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 as YTC
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.
SELECT
wct.PERIODRATE(wct.XIRR(amt_cashflow,date_pmt,NULL),1,4) as YTC
FROM (
SELECT
'2014-11-05' as date_pmt
,-21.50 as amt_cashflow
UNION ALL
SELECT
date_pmt
,amt_cashflow / 4
FROM
wct.BONDCF(
'2014-11-05' --@Settlement
,'2017-12-15' --@Maturity
,.049 --@Rate
,0 --@Yield
,25 * 4 --@Redemption
,4 --@Frequency
,1 --@Basis
,NULL --@Issue
,NULL --@FirstCoupon
,NULL --@LastCoupon
)
WHERE
date_pmt > '2014-11-05'
)n
This produces the following result.
YTC
----------------------
0.104868681753164
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