# how to calculate daily risk free rate using 13 week treasury bill

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.

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$. • Is there any specific reason for multiplying by 0.5 in d = ( float(i) * 0.5 ) / 100.0 # discount rate? – abu Jul 29 '19 at 10:51 • 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. – Mike O'Connor Jul 30 '19 at 12:24 • 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. – abu Jul 30 '19 at 14:37 • 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. – Mike O'Connor Jul 31 '19 at 19:32 • Hi Mike, I just wanted to make sure I follow up the logic of the transformation, also for other cases. Thank you for explaining! – abu Jul 31 '19 at 20:48 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. • 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. – noob2 Mar 17 '17 at 15:22 • 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. – Helin Mar 17 '17 at 15:26