Business Functions Library for Excel

 Basic Interest Rate Conversions family Converting between different kinds of interest rate compounding Description: Do calculations and conversions between different types of interest rates. Introduction: There are three main ways interest rates are commonly described:Simple Interest Rate. This is where the interest rate described by dividing the actual interest paid by the product of the Principal and the fraction of a year represented by the time period. In other words, R = I /PT, or I = P x T x R where I is the total interest paid, R is interest rate per annum, P is the principal and T is time expressed as a fraction of a year. If interest is paid more than once per year, the effective interest rate is somewhat higher than the quoted interest rate.AER or "Annual Equivalent Rate". Also known as the "effective" or "annual effective" rate. This is where the interest rate is described as the simple interest rate that would be appropriate if interest had been paid annually in arrears. By quoting this rate the periodicity of the payments does not need to be quoted just to compare the economics of different AER"s, and comparison is the main reason for the use of AER"s. The AER is usually higher than Simple Interest because Simple Interest is usually paid at smaller intervals than yearly.Continuous Rate. Another way of providing a level basis for comparing interest rates is to assume that payments are made continuously, perhaps best envisaged as having interest paid every day. Using continuous rates has the same advantage as AER"s in that there is no need to quote the periodicity of payments to make a comparison, the rate is continuous.A fourth way of dealing with time value of money calculations is to simply calculate the difference in time value between two dates, known as a discount factor. This turns out to be a very good way of working because it avoids the question of "was that simple, AER or continuous".This family converts between all these four methods of calculating interest and time value of money calculations.The main functions of interest are self-explanatory: To convert between Simple Interest Rates and AER, use SimpleToAER and AERToSimple.To convert between Simple Interest Rates and Continuous Interest Rates, use SimpleToCont and ContToSimple.To convert between AER and Continuous Interest Rates, use AERToCont and ContToAER.To convert between Discount Factors and Interest Rates, use DFToAER, AERToDF, DFToSimple, SimpleToDF, and DFToCont and ContToDF.PmtsPerYear, DayCount and PeriodsAll the functions mentioned above that require information about periodicity (ie the ones that involve Simple Interest) use a variable called PmtsPerYear to describe this. A value of -4 indicates quarterly in arrears, -1 is annually in arrears and +2 would be biannually in advance (interest in advance of course rarely happens for lending or borrowing situations, but where rents or finance rents it can be of relevance).The functions involving discount factors ...DF... require the determination of a length of time in years between FromDate and the ToDate. The functions permit this through the use the DayCount which enables you to decide whether you want the length of time in years between the FromDate and ToDate to measured in a specific DayCount convention or in the default ACT/ACTM. Additionally, you can specify Periods for ACT/ACT (in period).Be careful when using SimpleToDF and DFToSimple not to confuse Periods and PmtsPerYear. Periods relates to the length of time between FromDate and ToDate, PmtsPerYear refers to the periodicity of simple interest. Whilst it might be useful and more accurate in some circumstances to be more specific than PmtsPerYear to specify simple interest payments, doing so would mean that we could not use annuity formulae because of the irregular periods and we would have to adopt a longhand approach to interest compounding which would be less transparent. For functions that use detailed DayCount and Periods definitions see the Advanced Interest Rate Conversions Family. Functions in the Basic Interest Rate Conversions family (13)SimpleToSimple Converts a simple interest from one PmtsPerYear basis to anotherSimpleToAER Convert a simple interest rate to an AER (annual equivalent rate)SimpleToCont Convert a simple interest rate to a Continuous Interest RateSimpleToDF Convert a simple interest rate to a discount factorAERToSimple Convert an AER (annual equivalent rate) to a simple interest rateAERToCont Convert an AER (annual equivalent rate) to a Continuous Interest RateAERToDF Convert an AER (annual equivalent rate) to a discount factorContToSimple Convert a continuous interest rate to a simple interest rateContToAER Convert a Continuous Interest Rate to an AER (annual equivalent rate)ContToDF Convert a continuous interest rate to a discount factorDFToSimple Convert a discount factor to a simple interest rateDFToAER Convert a discount factor to an AER (annual equivalent rate)DFToCont Convert a discount factor to a continuous interest rate

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure