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.

### 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)**, where

^{fT}**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 = Ne**

^{RT}* 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 **T _{1}** and

**T**with

_{2}**T**, the

_{1}< T_{2}*continuously compounded forward rate*

**F**is defined through the forward equation

**M**, where

_{2}= M_{1}e^{F(T}_{2}^{-T}_{1}^{)}**M**is the notional of a loan undertaken at time

_{1}**T**and

_{1}**M**is the agreed repayment amount at the later time

_{2}**T**. No arbitrage – i.e. fairness – would then imply that the package consisting of this forward loan plus a spot loan on a lesser amount

_{2}**N**designed to repay exactly

**M**at time

_{1}**T**should be fully equivalent to a single spot loan on

_{1}**N**designed to repay exactly

**M**at time

_{2}**T**.

_{2}The two spot loans satisfy **M _{1}**

**= Ne**and

^{RT}_{1}**M**

_{2}**= Ne**respectively, since we assume that the rate

^{RT}_{2}**R**is flat.

Replacing these in the forward equation above we get **Ne ^{RT}_{2}** =

**Ne**from which it follows that

^{RT}_{1}e^{F(T}_{2}^{-T}_{1}^{) }**e**=

^{R(T}_{2}^{-T}_{1}^{)}**e**<=>

^{F(T}_{2}^{-T}_{1}^{)}**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