Understanding Historic Rate Rollovers in Excel

Historic rate rollovers are a practice of extending the maturity of a forward foreign exchange contract at the original exchange rate, rather than closing the contract and opening a new one at the current market rate. This can be done to avoid realizing gains or losses on the contract, or to match the timing of the contract with the underlying transaction that it is hedging. However, historic rate rollovers also involve an implicit loan or borrowing between the parties, depending on whether the contract is in or out of the money. This means that there are credit and interest rate risks associated with this practice, as well as potential accounting and regulatory issues. Therefore, historic rate rollovers should be done with caution and proper oversight, and only in certain circumstances where they serve a legitimate purpose.

Basic Theory:

Historic rate rollovers involve the calculation of a compounded interest rate based on historical interest rates over different periods. This is commonly used in finance to understand the impact of changing interest rates on investment returns or loan costs.

The formula for historic rate rollovers is as follows:

$FV = PV \times (1 + r_1) \times (1 + r_2) \times \ldots \times (1 + r_n)$

Where:

$FV$ is the future value.
$PV$ is the present value.
$r_1, r_2, \ldots, r_n$ are the historical interest rates for each period.

Procedures:

Gather Historical Interest Rates:
- Collect historical interest rates corresponding to each period relevant to your calculation.
Set Up Your Excel Sheet:
- Create a table with columns for periods and historical interest rates.
Use Excel Formula for Rollover:
- In a separate cell, use the following Excel formula:
```
=PV * PRODUCT(1 + historical_rates_range)
```
  Replace PV with the present value and historical_rates_range with the range of historical interest rates.

Explanation:

Let’s consider a scenario where an investment of $10,000 is made for three years with annual interest rates of 5%, 7%, and 4% for each respective year.

Scenario:

Present Value ( $PV$ ): $10,000
Historical Interest Rates ( $r_1, r_2, r_3$ ): 5%, 7%, 4%

Calculation:

$FV = 10000 \times (1 + 0.05) \times (1 + 0.07) \times (1 + 0.04)$

Excel Table:

Period	Interest Rate
Year 1	5%
Year 2	7%
Year 3	4%

Excel Formula:

=10000 * PRODUCT(1 + B2:B4)

Result:

$FV \approx 10000 \times 1.05 \times 1.07 \times 1.04 \approx 11704.44$

Alternative Approaches:

Manual Calculation:
- You can manually calculate the compounded interest for each period and multiply them, but using the Excel formula automates this process and reduces the risk of errors.
XIRR Function:
- Excel’s XIRR function can be used for more complex scenarios where cash flows are irregular. This function considers the timing of cash flows, providing a more accurate result in certain situations.