Understanding Repo Price Calculation in Excel

A repo, or repurchase agreement, is a type of short-term loan that involves the exchange of securities for cash. The borrower sells securities to the lender and agrees to buy them back later at a slightly higher price. The difference between the sale price and the repurchase price is the interest that the borrower pays to the lender, also known as the repo rate.

The repo price is the amount of cash that the lender receives from the borrower when the repo transaction is initiated. The repo price depends on the value of the securities that are used as collateral, the repo rate, and the duration of the repo contract.

To calculate the repo price, we need to know the following information:

• The original selling price of the securities, which is the market value of the securities at the time of the repo transaction.
• The repurchase price of the securities, which is the original selling price plus the interest that the borrower pays to the lender.
• The number of days to maturity of the repo contract, which is the period of time between the sale and the repurchase of the securities.

The formula to calculate the repo price is:

Repo Price = Original Selling Price + Interest

To calculate the interest, we need to use the repo rate and the number of days to maturity. The formula to calculate the interest is:

Interest = Original Selling Price * Repo Rate * (Number of Days to Maturity / 360)

The 360 in the denominator is a convention that assumes there are 360 days in a year. Some markets may use different conventions, such as 365 or actual/actual.

For example, suppose a borrower sells $100 million worth of 10-year Treasury bonds to a lender for a one-day repo contract at a repo rate of 2%. The original selling price of the bonds is $100 million, the repurchase price of the bonds is $100,055,556, and the number of days to maturity is 1. The repo price is:

Repo Price = Original Selling Price + Interest Repo Price = $100,000,000 + ($100,000,000 * 0.02 * (1 / 360)) Repo Price = $100,000,000 + $5,556 Repo Price = $100,005,556

This means that the lender pays $100,005,556 to the borrower in exchange for the bonds. The next day, the borrower pays $100,055,556 to the lender to get the bonds back. The lender earns $50,000 in interest, which is the difference between the repurchase price and the repo price.

Basic Theory:

A repo is a short-term borrowing arrangement where one party sells securities to another with an agreement to repurchase them at a specified future date and price. The difference between the sale and repurchase price, expressed as an annualized percentage, is known as the repo rate. The repo price is the total cash paid by the buyer to the seller.

The formula for repo price (P) can be expressed as follows:

Where:

• P is the repo price
• F is the face value of the securities
• r is the annualized repo rate
• t is the number of days to maturity

Procedures:

1. Gather Information: Collect the necessary information, including the face value of the securities (F), the annualized repo rate (r), and the number of days to maturity (t).
2. Apply Formula in Excel: In Excel, create a table with the following columns: Face Value (F), Repo Rate (r), Days to Maturity (t), and Repo Price (P). Apply the repo price formula to calculate the repo price for each transaction.
3. Utilize Excel Functions: Leverage Excel functions such as to simplify the calculations and enhance the accuracy of your results.

Example Scenario:

Let’s consider a scenario where the face value of the securities is $1,000, the repo rate is 3.5%, and the number of days to maturity is 30.

Result:

The repo price (P) in this scenario is approximately $971.94.

Other Approaches:

1. Excel Rate Function: You can use the RATE function in Excel to calculate the periodic interest rate and then apply it to the repo price formula.
2. Data Tables: Explore Excel data tables to perform sensitivity analysis on repo prices based on different repo rates and days to maturity.