SpotToSpot (FromDate, ToDate, BaseDate, ToDates, SpotRatesAER, [RateType], [DCount])
Calculate a FutureRate given a series of SpotRates.
This function works out the effective APR interest rate from FromDate to ToDate given a range of input SpotRatesAER. The spot rates apply from the BaseDate to the corresponding ToDate. Both this function and its companion function FutureToSpot
###### Methodology

Business Functions has functions for converting interest rates from Spot Rates to Future Rates and back again.

• Spot Rates are rates that are quoted in the financial markets for a fixed period of time from a Base Date until a date in the future e.g. the 10 year spot rate is 8%, i.e. it is 8% fixed for 5 years. The Spot Rate is the overall rate that gets you from the Base Date to Time B.
• Future Rates are the underlying forecast of interest rates that together combine to produce the Spot Rate. For example, 6% today rising to 12% in 10 years time might give an overall Spot Rate of 9.5%. If a Spot Rate is what gets from A to B, Future Rates are the rates you passthrough along the way.
• In BF it is assumed that these rates are annual equivalent rates (AER"s), and that interest is compounded until maturity. This is in order that the following approximation can be made.
• The SpotToSpot and FutureToSpot are the a simple approach to converting rates from one to the other. Derivatives traders actually use more complex formulae, see the Zero Coupon functions in the library such as ZeroCouponDF. SpotRate and FutureRate ignore the frequency of coupons and in effect assume that the SpotRate is a pure discount rate. Now, because SpotRates assume coupons are paid out alongthe way (they are looked up from bond and treasury rates) they are actually not pure discount rates, but can be approximated to discount rates.
• The method used by SpotToSpot and FutureToSpot is that the NPV is the same under each formula. So for a given time the NPV at the SpotRate is the same as the NPV at the range of FutureRates specified.

It was mentioned above that SpotToSpot is not as sophisticated as the method professional derivatives traders use. It is however a very good workmanlike approximation that does have intellectual "purity" inasmuch as the reverse of SpotToSpot is FutureToSpot, and vice versa. The two areas it does not address are:
• Payment Frequency. Quoted Spot Rates have a variety of frequencies - semi-annual, quarterly, even annually. These functions assume AER interest rates.
• The discounting/compounding rate should be the Zero Coupon Rate. These function use the spot or future rates supplied - not quite the same thing, but close enough for a lot of analytical work.

###### Tip

If you want to work out a future rate, just set FromDate and ToDate in the future.

###### Example
SpotToSpot.xls

Business Functions Ltd, London, UK Website Design: Webpure