Hospitals monitoring swaps for termination can use a simple technique to get a surprisingly accurate estimate of mark-to-market value under any interest rate scenario.

Predicting swap termination (aka mark-to-market) value can be helpful for borrowers looking to unwind their swaps. Due to the very low current interest rate environment, most pay-fixed interest rate swaps carry negative mark-to-market values, some in the millions of dollars. Because swaps typically go out many years, they can be highly sensitive to changes in rates.

A swap’s mark-to-market is the present value of the remaining scheduled series of net cash flows. The full calculation used by swap desks is complex and involves building a swap curve through “bootstrapping” with benchmark deposit rates, Eurodollar futures, and swap rates as inputs. Numerous adjustments are required along the way.

There is however a much simpler alternative which consists in using Excel’s built-in Data Analysis tool to regress historical mark-to-market values against LIBOR swap rates. This is a similar technique to what is frequently used to qualify swaps for fair value hedge accounting treatment under FAS133. You’ll need historical monthly swap mark-to-market values, which are readily available from your swap counterparty, and the relevant historical LIBOR swap rates, which are posted on the Federal Reserve Board website.

The Excel regression tool produces a simple linear equation **M = aX + b**, where M is the mark-to-market, X the LIBOR rate, and a and b are the slope and intercept constants from the regression analysis. For example, assuming a = 3,000,000 and b = – 20,000,000, if LIBOR = 5.00%, M will be (5 x 3,000,000) – 20,000,000 = -5,000,000, i.e. a negative mark-to-market of $5 million.

How accurate is this approach? Mark-to-market values are highly correlated to LIBOR swap rates, so with sufficient historical data points, the R-squared (how well the model predicts outcomes) routinely comes out in the 0.90 to 0.95 range. 1.00 is a perfect fit. The above chart shows how closely predictions are to historical values in an actual client situation. Accuracy will ultimately depend on a number of factors, including selecting the relevant LIBOR tenor for the swap’s average life.

Keep in mind that this technique is a shortcut and should only be used for estimation purposes. If you would like step-by-step instructions, email us.