Understanding Forward-Forward Prices After Spot in Excel

A forward-forward price is the price agreed upon today for a transaction that will take place at a future date, but after another future date. For example, suppose you want to buy a car in six months, but you don’t have the money right now. You can enter into a forward-forward contract with the seller, where you agree to pay a certain price in nine months, which is three months after the delivery date of the car. This way, you can lock in the price of the car today, and pay for it later.

The forward-forward price is based on the current spot price of the asset, plus or minus any costs or benefits associated with holding or borrowing the asset until the payment date. For example, if the car has a spot price of $20,000 today, and the interest rate is 5% per year, the forward-forward price for nine months would be $20,000 plus the interest you would pay for borrowing $20,000 for nine months, which is $750. Therefore, the forward-forward price would be $20,750.

The forward-forward price can also be calculated by using the spot price and the forward price for the delivery date. The forward price is the price agreed upon today for a transaction that will take place at a future date. For example, the forward price for the car in six months would be the spot price plus the interest you would pay for borrowing $20,000 for six months, which is $500. Therefore, the forward price would be $20,500. The forward-forward price for nine months would be the forward price for six months plus the interest you would pay for borrowing $20,500 for three months, which is $256.25. Therefore, the forward-forward price would be $20,756.25.

The forward-forward price can be used to hedge against the risk of price fluctuations in the future, or to speculate on the expected movements of the price. For example, if you expect the price of the car to increase in the future, you can enter into a forward-forward contract to buy the car at a lower price than the expected future price. If the price of the car increases, you can profit from the difference between the forward-forward price and the actual price. However, if the price of the car decreases, you will lose money from the difference.

Basic Theory

A forward-forward contract involves two separate forward contracts with different expiration dates. The first forward contract is initiated at the spot date, and the second is initiated at a future date after the first contract matures. The forward-forward price is the agreed-upon price for the second forward contract, established at the spot date.

The formula for the forward-forward price ( $F2$ ) after the spot date is given by:

$F2 = \frac{S \times (1 + r1 \times (T1/T2))}{(1 + r2 \times (T2-T1)/T2)}$

Where:

$S$ is the spot price.
$r1$ and $r2$ are the continuously compounded interest rates for periods $T1$ and $T2$ , respectively.

Procedures

Gather Information:
- Obtain the spot price ( $S$ ).
- Determine the interest rates for the two periods, $r1$ and $r2$ .
- Identify the time periods for the two forward contracts, $T1$ and $T2$ .
Apply the Formula in Excel:
- Use Excel functions to input the values into the formula.
- Utilize the natural logarithm function LN() for the continuous compounding.
Create an Excel Table:
- Organize the gathered information in an Excel table for clarity.
- Label each column with the corresponding variable.
Calculate Forward-Forward Price:
- Input the formula into a designated cell to compute the forward-forward price.

Explanation with Scenario

Let’s consider a scenario:

Spot Price ( $S$ ): $100
Interest Rate for the First Period ( $r1$ ): 4%
Interest Rate for the Second Period ( $r2$ ): 5%
Time for the First Contract ( $T1$ ): 1 year
Time for the Second Contract ( $T2$ ): 2 years

Excel Table

Variable	Value
$S$	$100
$r1$	4%
$r2$	5%
$T1$	1
$T2$	2

Excel Formula

$F2 = \frac{100 \times (1 + 0.04 \times (1/2))}{(1 + 0.05 \times (2-1)/2)}$

Excel Calculation

=100*(1+0.04*(1/2))/(1+0.05*(2-1)/2)

Result

The forward-forward price ( $F2$ ) is approximately $103.77.

Other Approaches

Using Excel Goal Seek:
- Set up the formula and use Goal Seek to find the forward-forward price by changing the cell with the spot price until the formula result matches a target value.
Data Tables in Excel:
- Create data tables to analyze how the forward-forward price changes with variations in spot price, interest rates, or contract durations.