0
$\begingroup$

I want to calculate excess return for AAPL plus the S&P 500. I have computed monthly and daily logarithmic returns for every stock and for the market, I now need to calculate the risk free interest rate in order to be able to compute the excess return for every stock and the market.

Interest rates on three months T-Bills are a good proxy for the risk-free rate of return, but I have a lot of doubts on how to use data provided by Yahoo! Finance in order to compute the daily risk-free. I use the 13 week treasury bill (ticker: ^IRX) historical quotes provided by Yahoo! Finance. I divided t-bill rate by 100 and calculate the daily excess returns.

             t-bill         AAPL           Excess
2017-01-31    0.500     -0.00230205    -0.00730020
2017-01-30    0.495     -0.00262402    -0.00762402

However, I realized that monthly t-bill rate units are the same as daily rates.

              t-bill      AAPL          
Jan 2017      0.500      0.0477  
Dec 2016      0.480      0.0479 

I am making a mistake while computing daily and monthly t-bill rate units. The Federal Reserve (https://fred.stlouisfed.org/series/DTB3) also provide the same t-bill rates for daily and monthly frequency as in Yahoo Finance. How can I correctly calculate the excess returns? Thanks for any answer.

$\endgroup$
0

2 Answers 2

4
$\begingroup$

user233051 notes that ^IRX is indeed the official discount rate of the US Treasury. So to answer his question we need to exactly understand how the Treasury computes the discount rate. My answer is based on www.treasury.gov pages here, here and here.

The official way of calculating the discount rate $d$ is $d = \frac{100-P}{100}\frac{360}{n}$ where $P$ is the price per \$100 of par (face) value and $n$ is the number of days until expiration. In order to get the $d$ of this formula we would divide the ^IRX by 100 because it is stated as a percent. Yes, it's a somewhat arbitrary way of compounding the return, but that's what they do. The Treasury says "The Bank Discount rate is the rate at which a Bill is quoted in the secondary market and is based on the par value, amount of the discount and a 360-day year." The missing 5 or 6 days are for bankers' holidays.

But we want instead $r = \frac{100}{P}^\frac{1}{n}$ where $r$ is the daily risk-free return ratio. So we have some simple algebra to do. We solve the first equation for $P$ and substitute that value in the equation for $r$ and get $r = \frac{ 1 }{ 1 - \frac{dn}{360}}^{1/n}$ where $dn$ is the product of $d$ and $n$.

So we have our answer but we really need to know what $n$ is, for each day. The Treasury says that their discount rates are determined by prices in the secondary market--- for the most recently auctioned bill. New 13-wk bills are auctioned-off every Monday. So this means that at first $n$ is approximately 91 days, but it will decline to about 85 days before a new bill is auctioned. So unless our code is responsive to the days of the week (and we do some further research about the details) we may instead want to consider making an approximation.

To that end, we can do some simple algebra that shows that the quantity that we want to compute is rather insensitive to small differences in the assumed value of $n$, to such an extent that we almost don't really need to have any idea what the value of $n$ is (because it will always be fairly large, at least 85). Since $d$ is quite small and is also divided by $360$, $\frac{1}{1 - \frac{dn}{360}}\approx 1 + \frac{dn}{360}$. And we recognize $1 + \frac{dn}{360}$ to be the leading term in $d$ in $(1 + \frac{d}{360})^n$. Therefore $r\approx 1 + \frac{d}{360}$!

The following Python code explores which approximation is best. In the printout rf is the annualized risk-free return ratio, annualized so as to magnify the differences over a respectable period of time.

for i in range(1,5):

    d = ( float(i) * 0.5 ) / 100.0  # discount rate

    nhi = 91.0   # days until maturity
    nlow = 91.0 - 6.0

    rhi = ( 1.0 / (1 - d * nhi/360.0) )**(1/nhi)  # corresponding daily risk-free return ratio (exact)
    rlow = ( 1.0 / (1 - d * nlow/360.0) )**(1/nlow)  

    print( 'discount rate, d = {}'.format(d, '') )
    print( 'rf = {} {}'.format(rhi**365.0, 'exact: n = 91 days') )    # rf is the annualized risk-free return ratio
    print( 'rf = {} {}'.format( (1+d/252.0)**252.0, '(1+d/252.0)**252.0' ) )
    print( 'rf = {} {}'.format( (1+d/360.0)**365.0, '(1+d/360.0)**365.0' ) )
    print( 'rf = {} {}'.format(rlow**365.0, 'exact: n = 85 days') )
    print('')

discount rate, d = 0.005
rf = 1.00508553843 exact: n = 91 days
rf = 1.00501247101 (1+d/252.0)**252.0
rf = 1.00508228044 (1+d/360.0)**365.0
rf = 1.00508532578 exact: n = 85 days

discount rate, d = 0.01
rf = 1.01020342853 exact: n = 91 days
rf = 1.01004996668 (1+d/252.0)**252.0
rf = 1.01019031932 (1+d/360.0)**365.0
rf = 1.01020257221 exact: n = 85 days

discount rate, d = 0.015
rf = 1.01535391748 exact: n = 91 days
rf = 1.01511261146 (1+d/252.0)**252.0
rf = 1.01532424685 (1+d/360.0)**365.0
rf = 1.01535197778 exact: n = 85 days

discount rate, d = 0.02
rf = 1.02053725469 exact: n = 91 days
rf = 1.02020053039 (1+d/252.0)**252.0
rf = 1.02048419386 (1+d/360.0)**365.0
rf = 1.02053378305 exact: n = 85 days

Note that the calculated values that are exact for 91 days and 85 days are almost equal. Next to those the $1 + \frac{d}{360}$ estimate isn't too bad. Those possibilities should be used with code that pays interest over the weekend. If you're just doing bars without regard for the day of the week, you'd like to use $1 + \frac{d}{252}$ but it doesn't fare as well. All things considered, for those of us who do go so far as to give proper consideration to the weekend the most feasible thing to do would seem to be to approximate $n$ by, say, $88$.

$\endgroup$
5
  • $\begingroup$ Is there any specific reason for multiplying by 0.5 in d = ( float(i) * 0.5 ) / 100.0 # discount rate? $\endgroup$
    – abu
    Jul 29, 2019 at 10:51
  • $\begingroup$ Hi abu. No. It was chosen just for convenience so that the spread of assumed discount rates would cover the sort of range of values that we are seeing today in the markets. $\endgroup$ Jul 30, 2019 at 12:24
  • $\begingroup$ Hi Mike, thanks for the fast answer and confirming my suspicions! Also, am I right to assume that to arrive from IRX daily data to monthly risk-free rate the only modification is replacing rhi**365.0 with rhi**30.0? Assuming I use the last day of the month data. $\endgroup$
    – abu
    Jul 30, 2019 at 14:37
  • 1
    $\begingroup$ The questioner asked for the daily rate which is what my answer provides. So yes you would get the monthly rate by compounding as you indicate. But rhi is based on 91 days until expiration. I was showing there that the actual number of days until expiration (of the 13-wk treasuries) varies from 85 to 91 and that it is hardly worth it to figure out what the actual number of days left is because you get almost the same answer anyway. That said, I'm using 88 days--- roughly the average--- rather than the 91 of rhi. $\endgroup$ Jul 31, 2019 at 19:32
  • $\begingroup$ Hi Mike, I just wanted to make sure I follow up the logic of the transformation, also for other cases. Thank you for explaining! $\endgroup$
    – abu
    Jul 31, 2019 at 20:48
2
$\begingroup$

If you want to do it super precisely, the convention for building fixed-income total return index is as follows:

  1. You assume at the end of the month, you buy the instrument (in this case a 3-month T-bill).
  2. You hold this exact T-bill over the course of the next month and mark it to market daily and calculate the daily returns ($P_t / P_{t-1} - 1$, which is standard performance calculation).
  3. At the end of the month, you sell the T-bill and buy the next 3-month bill.

This of course requires that you have accurate bill pricing data. You can also look at the Bloomberg Barclays 3-Month Treasury Bellwether Index, or the BofA Merrill Lynch 3-Month Bill Index. Both are calculated this way.

Alternatively, a lot of people just use the previous month-end bill rate, divide it by 252, and use that as the daily bill return for the current month. Not as precise, but more than sufficient for most purposes.

$\endgroup$
3
  • $\begingroup$ If I look at 3 month Tbills now (2017 03 17) I see they are 0.7262% a year, so that is 0.007262 as a pure number and per day it is 0.007267/365 = 0.000019896 if I use the simple (but good enough for most purposes) method. If I have a million bucks in Tbills I earn about 20 bucks a day in interest. In January 2017, as you reported, it was 0.5% A YEAR and in Dec 2016 0.48% a year. $\endgroup$
    – nbbo2
    Mar 17, 2017 at 15:22
  • 1
    $\begingroup$ I changed the divisor to 252... Here's the accurate number for January: total return for 3m t-bill was 0.046947% per Bloomberg Barclays Index, so 1 million invested would've generated interest of $469 – $15 per calendar day and $22 per biz day. $\endgroup$
    – Helin
    Mar 17, 2017 at 15:26
  • $\begingroup$ Thanks for the answers. 3 month t-bill rate is 0.48% (0.0048) a year for 2016. For month Dec 2016, I will take 0.0048/12 = 0.00040. For daily basis, I will use (2017 03 17) 0.007267/252 = 0.0000288373. $\endgroup$
    – user233051
    Mar 19, 2017 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.