Understanding Implied Repo Rate in Excel

The implied repo rate is the rate of return that you can earn by borrowing money to buy a bond in the spot market and selling a futures contract on the same bond. A bond is a type of loan that pays interest and principal at fixed intervals. A spot market is where you can buy or sell something for immediate delivery. A futures contract is an agreement to buy or sell something at a specified price and date in the future.

The implied repo rate is based on the idea of a repurchase agreement (repo), which is a way of lending and borrowing bonds using cash as collateral. In a repo, you lend a bond to someone and receive cash in return. You agree to buy back the bond at a slightly higher price after a certain period of time. The difference between the initial and final price is the interest that you earn on the cash. The repo rate is the annualized interest rate of this transaction.

The implied repo rate is similar to a repo, but instead of lending a bond, you sell a futures contract on the bond. This means that you agree to deliver the bond at a certain price and date in the future. By doing this, you lock in the future price of the bond and avoid the risk of price fluctuations. You use the cash from selling the futures contract to buy the bond in the spot market. You hold the bond until the delivery date of the futures contract and use it to repay the cash loan. The difference between the spot price and the futures price of the bond is the interest that you earn on the cash. The implied repo rate is the annualized interest rate of this transaction.

The implied repo rate can be used to compare the profitability of different bonds and futures contracts. The higher the implied repo rate, the more attractive it is to buy the bond and sell the futures contract. The bond with the highest implied repo rate is called the cheapest to deliver (CTD) bond, because it has the lowest initial cost to yield the highest return. The implied repo rate can also indicate the demand and supply of bonds and cash in the market. If the implied repo rate is high, it means that there is a shortage of cash or a surplus of bonds. If the implied repo rate is low, it means that there is a surplus of cash or a shortage of bonds.

Theory

The Implied Repo Rate is calculated by comparing the forward price of a security (the expected future price) with
its current spot price. The formula is as follows:

$\text{Implied Repo Rate} = \frac{(\text{Forward Price} - \text{Spot Price})}{\text{Spot Price}} \times \frac{360}{\text{Days to Maturity}}$

Where:

Forward Price is the expected future price of the security.
Spot Price is the current price of the security.
Days to Maturity is the number of days until the security matures.

Procedures

Gather Data: Collect the necessary data, including the current spot price, the forward
price, and the days to maturity of the security.
Calculate Implied Repo Rate: Apply the Implied Repo Rate formula to determine the implicit
interest rate.
Create an Excel Table: Organize the data and calculations in an Excel table for clarity and
ease of understanding.

Scenario: Calculating Implied Repo Rate with Real Numbers

Let’s consider a scenario where an investor holds a Treasury bond with a spot price of $1,000, a forward price of
$1,020, and 30 days to maturity.

Excel Table:

Data	Values
Spot Price	$1,000
Forward Price	$1,020
Days to Maturity	30
Implied Repo Rate	Formula

Excel Formula:

$\text{Implied Repo Rate} = \frac{(\$1,020 - \$1,000)}{\$1,000} \times \frac{360}{30}$

Excel Calculation:

$\text{Implied Repo Rate} = 0.02 \times 12$

$\text{Implied Repo Rate} = 0.24$

Result:

The Implied Repo Rate for this scenario is 0.24 or 24%.

Other Approaches

Yield Calculation: Calculate the yield to maturity (YTM) of the security and compare it
with the risk-free rate.
Using Excel Functions: Utilize Excel functions like RATE or YIELD to calculate the Implied
Repo Rate based on cash flows.