RateConvert function

Time Value Of Money Analysis category - Advanced Interest Rate Conversions family Home 
What the name means"Generic Rate Convert"
SyntaxRateConvert ( FromDatesInt, IntSimple, FromDate, ToDate, [DayCountSource], [DayCountTarget], PrdsSource, PrdsTarget, [MarginOpt] )
DescriptionConverts an Interest Rate from one basis of compounding to another, taking into account possible irregular compounding periods.
See AlsoSimpleToSimpleX (FromDatesInt, IntSimple, FromDate, ToDate, [DayCountSource], [DayCountTarget], PrdsSource, PrdsTarget)
VariableType or ValueDescription
Inputs: (9)
1 FromDatesInt Range Of Dates The dates from which the interest rates are effective.
2 IntSimple Range of Floats (numbers) One or more annual interest rates where the rates are simple interest, not AER ie interest paid=fraction of year x simple interest rate
3 FromDate Date The date from which a period of time starts
4 ToDate Date The date up until which a period of time runs
5 DayCountSource Float (Optional) The Daycount basis of the interest rate you want to convert FROM. Either a single number denoting the DayCount method, or a number in the form ww.ss where ww is the daycount of whole periods and ss applies to stub periods. See Using Daycount. (Optional)
6 DayCountTarget Float (Optional) The DayCount basis of the interest rate you want to convert TO. Either a single number denoting the DayCount method, or a number in the form ww.ss where ww is the daycount of whole periods and ss applies to stub periods. See Using Daycount. (Optional)
7 PrdsSource Annual Sequence The Periods sequence of the interest rate you want to convert FROM.
8 PrdsTarget Annual Sequence The Periods sequence of the interest rate you want to convert TO.
9 MarginOpt Float (Optional) Optional Margin, or Spread, to be applied over and above the simple interest rate. If MarginOpt is not zero, then Total interest rate = Interest Rate + Margin. Note however that Interest on Interest is charged at Interest Rate only, ie no Margin added. If you want Interest on Interest to include the margin, then build the margin into the Interest Rate and omit MarginOpt. (Optional)
Output:
Return Value Result Float The result of the function, expressed as a number.
Defaults and Values

The variable(s) of this function have certain defaults and/or switch values associated with them. Click here to see more.

Range Handling and Constraints

This function has range inputs that are subject to range handling procedures to help reduce input errors and increase flexibility and speed. Click here to see more.

Why we need this function
  • We need this function because sometimes when you are compounding from one interest basis (e.g. quarterly, ACT/365) to another (e.g. semi-annual, 30/360), you can't do it using the normal algebraic formulae. Often you actually can't do it without working out the interest in detail for the exact period you are looking at. This function effectively works out the interest on two completely different compounding regimes, and gives the interest rate that makes the Target regime equivalent to the first.
  • Furthermore, where you have a separate margin that is needed for compounding the balance but where you don't want to compound interest on interest with this margin, the interest calculation has another step in it as the interest and interest on interest is compounded separately.
  • RateConvert is the only generic way to accurately convert a wide range of interest rate types from one basis to another.
Methodology
  • RateConvert is a highly generic function for converting any type of interest rate to any other.
  • It uses proper DayCount and Period definition variables to define both the interest rate you are converting from (the Source) and that you are converting to (the Target)
  • It returns the interest rate applicable to DayCountTarget, ŘPeriodsTargetŘ and ŘIntRatesSimpleŘ, between FromDate and ToDate.
  • The optional MarginOpt variable is to determine how interest on interest is calculated. If you use MarginOpt, this margin is added to ŘIntRatesSimpleŘ to determine the interest, but the interest on interest is only compounded at ŘIntRatesSimpleŘ, without adding any extra margin.
Discussion
Converting from one interest rate basis to another can be extremely complex. For example, many functions in this family deal with the simple case where an interest rate is being converted from a simple interest rate applied at periodic intervals, to an annually in arrears AER (annual equivalent rate) interest rate. These other functions use annuity formulae of the kind (1+r/4)^m-1. These annuity formulae are generalisations and are not accurate when you have specific interest payment dates that may not be at exactly equal intervals, or when you have some of the more exotic daycount conventions.

This function is truly generic, but you need to understand what its doing.
  • First, it calculates the interest between FromDate and ToDate on a example balance of $1000 using ŘIntRatesSimpleŘ and the 'Source' parameters. This is simple enough (it actually uses Con).
  • If you use MarginOpt, the interest rate on the balance is (IntRate+MarginOpt) and the interest on interest is at IntRate.
  • Then, it takes a guess at an interest rate for Target (ie the result), and calculates the interest as above.
  • The two amounts of interest are different because the guessed rate is probably wrong. The correct interest rate is determined simply by saying: Result=IntRateGuess*IntSource/IntTarget.
Question
In what circumstances do you need to specify MarginOpt?
Answer: Say you have a semi-annual rate on a bond that has swap associated with it that resets of a quarterly reference rate (e.g. 3 month LIBOR). You want to convert from a quarterly rate to a semi-annual rate, which involves compounding 2 quarters to make one semi-annual. But whilst you want to compound the balance of the swap at (reference rate+margin), you only want to compound the interest at the time value of money, ie the reference rate. In this case the interest rate you set is the quarterly reference rate, and you set the margin separately. See the example for more information.
Examples
  • Preview of the RateConvert example.
  • The above spreadsheet can be accessed from the Quick Reference (on the Business Functions menu), on the Examples menu, or in the Business Functions\Component Examples directory