Calculating the Dollar Value of Basis Points in Excel

In the fixed income market, each instrument's smallest increment of movement is called a tick. Each variation cannot be lower than a tick, and it is determined according to the value of the instrument traded. For the market rate, the minimum unit is the basis point. If a rate goes from 2 to 2.1%, the rate will be increased by 10 basis points. The basis point corresponds to one-hundredth of 1%, or 0.01%.

This term is frequently used in the monetary policy rate sector, where one does not speak of a percentage variation but basis points. Thus a rise in the interest rate from 1.3% to 1.35% does not increase by 3.84%, but by five basis points. It is common to hear media commentators state that such an increase corresponds to an increase of 0.05%. However, this is not the case; the increase is, in fact, five basis points up to 3.84%.

Often interest rates change from 25 to 50 basis points. But even an increase of a few basis points on an interest rate may result in an overall increase of all levels, whether in credit markets or real estate markets. The largest market moves, such as those between 100 and 200 points, are rare and often signal an impetus to fundamentally change the monetary policy of the country or correct a deteriorating economic environment. For example, a central bank, by changing its rates, can significantly impact the economy of a country. Thus, rising interest rates can slow growth but also inflation.

Conversely, a sharp drop in interest rates will result in a boom in the economy because money is cheaper to obtain.

Borrowing rates for individuals closely follow bank-lending rates, which in turn reflect rates set by central banks. Here's how. Often banks borrow from each other the money that they lend out to clients. The interbank rate is the rate of interest charged on short-term loans made between institutions.

These short-term loans are of one week or less. Some, made to meet reserve requirements, are just for one day—literally overnight—and these loans are made at a rate known as the overnight rate or (in the U.S.) the federal funds rate.

The fed funds rate, as it's colloquially known, is one of the most important interest rates in the U.S. economy. Lenders derive their prime interest rate, the rate they charge their most creditworthy borrowers, from it. The terms for other consumer loans or investment instruments, like CDs, are then based on this prime rate: You've probably seen an interest rate advertised as "prime plus 3%!" or "two points above prime!" So the fed funds rate ends up influencing short-term interest rates for everything from home and auto loans to credit cards' APRs. Longer-term rates reflect it too, albeit indirectly.

Calculating the dollar value of basis points is a straightforward procedure in Excel.

As an example, say we have a one-year loan of $1,000,000. Let’s calculate the value of a 1.5 basis-point change on an annual, quarterly or monthly basis. We would simply prorate the value of the change in basis by four if for a quarter period, and by 12 on a monthly time span.

We multiply the value of the instrument we wish to compute the by the change in basis points. Here we multiply C9 by C11 and divide by 10,000. 10,000 corresponds to 0.01 of 1%. 0.01*0.01=0.0001. The "VLOOKUP" takes care of the prorating on a quarterly or monthly basis.

Here we see that the value of 1.5 basis points on $1,000,000 prorated to a monthly basis corresponds to $12.50.