Time for a coffee break? Understanding Time and its implications on Interest Rates
With this article I want to give you an intuitive feeling of the concept of interest rate and also show you how to work with various types of interest rates – such as a compounded interest rate - in Excel as accurately as market professionals do.
Contrary to the four-dimensional space-time of relativistic physics, financial stochastic processes evolve in a one-dimensional world consisting of time alone.
Comparing the relative importance of finance's two more fundamental concepts, Time and Transaction, the former is more basic as its absence precludes the latter, but not the other way around.
Benjamin Franklin, one of the Founding Fathers of the United States of America, in an essay titled Advice to a Young Tradesman quoted in 1748: Remember that time is money. He that can earn ten shillings a day by his labour, and goes abroad, or sits idle one half of that day, though he spends but sixpence during his diversion or idleness, it ought not to be reckoned the only expence; he hath really spent or thrown away five shillings besides.
So – at least according to Franklin – time is money, but the real question is: how much money?
The answer obviously depends on the amount of time in consideration and a little thinking reveals that dependence to be anything but linear.
If I asked you for 1,000 $ and I promised you to pay back in a year's time, you may demand an extra 10 $ as compensation for that interval of time.
But if I had dared to suggest paying you back after 100 years, chances are that you would not have agreed with a proportional compensation of 100*(10 $) = 1,000 $!
This example shows that measuring the value of time is not a trivial task.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
How did our prehistoric ancestors measure the time value of money?
The early Homo sapiens were fully aware of the time value of money, under the provision that "money" for them were tangible exchangeable assets such as sheep.
A prehistoric shepherd would be willing to lend a few sheep to a poorer fellow under the promise of getting them back some time later including a few of the lambs that would be born in the meantime.
This little "extra" would have made sense because the shepherd-turned-lender would be forced to forego the ensuing posterity of the lent sheep and therefore would not have been content by getting only the original sheep back.
One may view the shepherd's demand of getting back a few lambs on top of the original sheep as an immoral practice. It may well be so, but it is still – at least in my view - a fair practice considering the natural reproductive process of the sheep species.
If our shepherd had several prospective sheep-borrowers, he would likely use a table like the one below in order to quickly quote the terms of the lending contract to any forthcoming applicant.
By the way, no-arbitrage considerations would imply that all nearby shepherds would have to employ the same table for their lending-borrowing transactions, otherwise some smart sheep-traders would rush to exploit price discrepancies by borrowing N1 sheep from one generous lender who asks for N2 sheep back and immediately lend a few less than N1 – let's say M1 - sheep to some other borrower demanding exactly N2 sheep back, so that they would end up keeping N1 – M1 sheep right from the start, while their future rights and obligations would exactly cancel each other!
How do the present day lenders – i.e. financial institutions – measure the time value of money?
In principle, in the same way like their ancestors through a table that displays the return of any loan for a given initial amount and term to maturity.
The only difference is that there is no need to maintain such a table. Instead only a few numbers are maintained – called interest rates – that are capable of producing any or all of the table entries as needed.
So if you visit your local bank for a consumer loan of 10,000 $ payable in 2 years, the bank employee will not consult a table like the one below
but will be in a position to produce any table number M by using the single number R = 0.01 - referred to as a simply compounded interest rate – through the formula M = N(1+RT) where:
N is the initially disbursed loan amount, also known as loan notional and
T the time in years when the loan should be repaid.
As a matter of fact, the equation M = N(1+RT) serves as the definition of the simply compounded interest rate R, but other alternative definitions exist as well.
I describe below those definitions that are available in QuantLib and therefore in Deriscope as well.
Simply Compounded Interest Rate
Definition: M = N(1+RT)
R Expression: R = (M/N–1)/T = Δ/T, where Δ = (M-N)/N is the interest amount expressed as percentage of the loan notional.
Interpretation: Annual rate by which the percentage interest Δ increases over time.
Repayment Amount vs Maturity: Linear. See chart below.
Compounded Interest Rate
Definition: M = N(1+R/f)fT, where f is the compounding frequency, eg 2 for semiannual compounding.
R Expression: R = [(M/N)1/(fT)-1]f
Interpretation: Annual rate by which the percentage interest Δ increases over time with the additional provision that the interest amount accrued over each interval of length 1/f is reinvested in the notional at the end of that interval so that the rate R applies on a bigger notional for the purpose of calculating the interest accrued over the next time interval.
Note the above interpretation applies only for maturities T that are exact multiples of the compounding interval 1/f.
It follows immediately by setting T = k(1/f) in the above equation, where k is some integer. We then get M = N(1+R/f)k which clearly represents an amount N that is boosted k times by the factor (1+R/f).
Repayment Amount vs Maturity: Exponential. See chart below.
Continuously Compounded Interest Rate
Definition: M = NeRT
R Expression: R = ln(M/N)/T
Interpretation: Annual rate by which the percentage interest Δ increases over time with the additional provision that the interest amount accrued over each infinitesimal time interval of length 1/f is reinvested in the notional so that the rate R applies on a time-varying notional for the purpose of calculating the interest accrued over each such interval.
This rate is identical with the compounded rate in the case where the compounding frequency tends to infinity.
An interesting feature of a flat – i.e. maturity independent – continuously compounded interest rate is that the forward rate turns out to be flat as well and actually equal to the spot rate.
Given two maturities T1 and T2 with T1 < T2, the continuously compounded forward rate F is defined through the forward equation M2 = M1eF(T2-T1), where M1 is the notional of a loan undertaken at time T1 and M2 is the agreed repayment amount at the later time T2. No arbitrage – i.e. fairness – would then imply that the package consisting of this forward loan plus a spot loan on a lesser amount N designed to repay exactly M1 at time T1 should be fully equivalent to a single spot loan on N designed to repay exactly M2 at time T2.
The two spot loans satisfy M1 = NeRT1 and M2 = NeRT2 respectively, since we assume that the rate R is flat.
Replacing these in the forward equation above we get NeRT2 = NeRT1 eF(T2-T1) from which it follows that eR(T2-T1) = eF(T2-T1) <=> F = R, which proves the assertion.
Repayment Amount vs Maturity: Exponential. See chart below.
Simple Then Compounded Interest Rate
Definition: Given a compounding frequency f, the accrued interest until time 1/f is calculated as if the rate were simply compounded, but the part of interest accrued after time 1/f is calculated using the compounded convention.
Compounded Then Simple Interest Rate
Definition: Given a compounding frequency f, the accrued interest until time 1/f is calculated as if the rate were compounded, but the part of interest accrued after time 1/f is calculated using the simply compounded convention.
Dependence of the Repayment Amount on the Time to Maturity
Below is a chart that shows the fact that a constant interest rate may imply dramatically different results with regard to the loan repayment amount at maturity when various rate definitions are adopted.
How do we measure time in finance?
Up until now, we have referred to the maturity T as the time from loan inception until loan repayment expressed in number of years.
This statement needs some clarification.
How many years are from 8 Mar 2018 until 18 Mar 2018?
In finance there is no unique answer to this question. It is determined by a special convention, called day count convention.
The simplest day count convention is the so called ACTUAL/365F (Fixed) which simply counts the number of days between the two dates and then divides that number with 365. According to this convention the answer to the above question is 10/365 = 0.027397 years.
Another simple convention is the ACTUAL/360 which divides the number of days with 360 and leads to a different answer of 10/360 = 0.027777 years.
Other conventions involve much more complex calculations.
Below is the list of conventions handled by QuantLib and therefore Deriscope:
30/360
30IT/360
30E/360
ACT/360
ACT/365F
ACT/365CA
NL/365
Bus/252BR
ACT/ACT(AFB)
ACT/ACT
ACT/ACT(ICMA)
Given how interest rates are defined, it follows that the rate applying to a particular loan transaction will also depend on the agreed day count convention.
For example, using the above formula R = (M/N–1)/T, the simply compounded rate describing a loan of 100 $ assumed on 8 Mar 2018 and agreed to be repaid on 18 Mar 2018 as 101 $ would equal (101/100-1)/ 0.027397 = 0.01/0.027397 = 36.5% under the ACTUAL/365F day count convention, but only 0.01/0. 027777 = 36% under the ACTUAL/360 convention.
Conversely – and perhaps more interestingly – if the bank grants you on 8 Mar 2018 a 10 days loan of 100 $ and quotes a simply compounded rate of 36.5% you may get the impression you would have to pay 101 $ back at maturity if you assumed the bank applies the ACTUAL/365F convention.
But if the fine print specified an ACTUAL/360 convention, then you would have to pay N(1+RT) = 100(1+0.36*0. 027777) = 101,013889 $ at maturity, amounting to a surprise extra of 0.013889 $.
No big deal of course, but for a 100,000,000 $ notional this "little extra" would suddenly become 1,000,000 times bigger, i.e. 13,889 $, which is a two-month salary for junior quants and justifies to some extent their relatively high remuneration!
The date bump question
Bankers love complexity because it thwarts their clients from understanding the impact of the various transaction details on the expected cash flows.
But sometimes complexity is there for a good reason, such as the time shift of the official transaction dates according to a pre-agreed rule referred as date bump (or date shift) convention.
QuantLib handles the following 5 conventions:
Following: If the reference date falls on weekend or holiday then it is shifted to the following business day.
Modified Following: This is probably the most common convention used.
If the reference date falls on weekend or holiday then it is shifted to the following business day. However if the following business day is in a different month, the previous business day is adopted instead.
Preceding: If the reference date falls on weekend or holiday then it is shifted to the previous business day
Modified Preceding: If the reference date falls on weekend or holiday then it is shifted to the previous business day. However if the previous business day is in a different month, the following business day is adopted instead.
Half Month Modified Following: If the reference date falls on weekend or holiday then it is shifted to the following business day. However if the following business day is either in a different month or has crossed the 15th of the same month, the previous business day is adopted instead.
None: No date adjustment.
As an example, the above loan quoted from 8 Mar 2018 to 18 Mar 2018 would actually involve 11 rather than 10 accruing dates if the date bump convention associated with the accrual rate happened to be Following, since 18 Mar 2018 falls on Sunday. This would mean that the borrower would have to repay the interest amount corresponding to 11 rather than 10 days!
Creating an interest rate object in Excel
The Deriscope wizard can generate the spreadsheet formula that creates an interest rate object in Excel as the following video demonstrates:
and this is how the pasted formula looks like:
Understanding the formula
As you see, cell A1 contains the formula =ds(A2:B6), which takes one input argument and returns the text &SimpleRt_A1:1.1
The prefix & indicates that &SimpleRt_A1:1.1 is the handle name of some object. In fact it points to an object of type Simple Rate and can be used in any context where an object of type Interest Rate is expected because the type Simple Rate inherits from Interest Rate.
The input range A2:B6 contains 5 key-value pairs with obvious meanings.
Nevertheless I can read the information applying to each key-value pair by selecting the respective cell as this video shows:
Regarding the values – indicated with blue color – I can edit them by manually typing in the cells or by using the wizard as shown by the next video:
Using the interest rate object
Deriscope supplies through QuantLib the following four functions that can be called from a given Simple Rate object:
DF: Returns the discount factor associated with the given rate value.
Compound Factor: Returns the compound factor associated with the given rate value.
Implied Rate: Returns the rate value associated with the given compounding factor.
Equivalent Rate: Returns the rate value associated with the conventions of the caller Simple Rate object, such that the generated interest amount equals the one generated by the supplied rate value and rate conventions.
The following video shows how I use the wizard to paste all these functions in the spreadsheet:
Below is the spreadsheet image with the pasted functions:
You may download the spreadsheet produced with the above steps here.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com
