How to Create a Simple Basis Points Calculator in Excel

Microsoft Excel is fantastic since it simplifies the process of creating and managing a portfolio (especially an investment portfolio). Basis Points can be referenced in stock trading, indexing, and interest rates when expressing percentage changes. Having a handy calculator makes them much easier to determine.

Building a Basis Points calculator in Excel is probably easier than you think and only uses two simple formulas – here’s how to do it!

First, what are Basis Points, and how are they used?

Although basis points are often used to communicate the movement of interest rates over time, they can be used whenever a small percentage change is being discussed. They provide a convenient way to express a change in rate in a standard and easily comparable form. For example, they can be used to communicate the difference between bond yields, currency exchange rates, stock indices, commodity prices, and any other time you need to express a small change in percentage.

One basis point is equal to 0.01% (1/100th of 1%). For example, 25 basis points = 0.25%. So, if a bond’s yield increases from 2% to 2.25%, this change can be expressed as an increase of 25 basis points. If the change in price was 0.05%, we could say the price changed by 5 basis points.

Converting percentage change to basis points is relatively straightforward.

How are Basis Points calculated?

Keeping in mind that one basis point is equal to 0.01%:

• To convert a percentage change into basis points, multiply the change by 100
• To convert basis points into a percentages, divide the number of basis points by 100

As an example, if the number of basis points is 500, the equivalent percentage would be 500 / 100 = 5%.

If the percentage change is 5%, the equivalent basis points would be 5 * 100 = 500 bps.

How to create a Basis Points calculator in Excel

A simple basis points calculator can be made using basic formulas, but there is a trick to computing these successfully in Excel because of the way Excel stores percentages. Let’s walk through three examples.

Compute basis points given percentage change

Use this method when you have the percentage change and want to compute the basis points (bps). Consider this screenshot:

Enter the percentage change in C7. To compute the basis points, use the following formula in C8:

=(C7*100)*100

Now … you’ll notice we multiplied the percentage change by 100 twice. This is because of the way Excel stores and displays percentages. That is, the number stored in C7 is actually 0.0025. When you apply a percentage format, then Excel displays that value as 0.25%.

Since 0.25% is 25 bps, we need to multiply the value in C7 (0.0025) by 100 to get it to 0.25 and then 100 again to get it to 25 bps.

Compute percentage change given basis points

Use this method when you have the bps and want to compute the percentage change. Consider this screenshot:

Enter the basis points in C11. To compute the percentage change, use the following formula in C12:

=(C11/100)/100

We divided C11 by 100 twice. This is because of the way Excel stores and displays percentages. The calculated value in C12 is actually 0.0025, but with the percent format applied, it is displayed as 0.25%.

Since C11 stores 25 bps, we need to divide it by 100 to get it to 0.25, and then divide it by 100 again to get it to 0.0025. That way, we can apply the percentage format to display 0.25%.

Compute change given start and end values

Use this method if you have two values for which you’d like to compute the percentage change and bps. Consider the screenshot:

Enter the starting value in C15. Enter the ending value in C16. The following formula will compute the change in C17:

=C16-C15

The following formula will compute the percentage change in C18:

=C17/C15

The following formula will compute the bps in C19:

=(C18*100)*100

If you’d like to download the file, it is here:

• Basis Points Excel Template

Once it’s set up, you can use your Basis Points calculator in Excel to plug in any values you need and get both the percentage change and the Basis Points. 

Do you have any other interesting tips or neat ways to use a Basis Points calculator in Excel? Let us know in the comments!