Kind regards,

Niek Otten
Microsoft MVP - Excel

     By Norman Harker

     Here's a very long post but I hope the content will assist all those who

     struggle with these interest conversion calculation problems. It gives the

     basic details, sets out the 10 formulas and provides 10 User Defined

     Functions.

     *** Introduction ***

     Every profession has basic tools. Interest conversion formulas are the basic

     tools of investment analysis without which very little can be achieved in

     terms of performing the tasks or interpreting results.

     So be patient with the length of this posting as it aims at giving you the

     tools of the trade in a form that will involve the least pain and suffering.

     With interest conversion tools at hand your financial skills in Excel will

     go up many notches at once both in terms of what you can do and in terms of

     understanding what results you are getting.

     One of the great advantages of Excel is that tasks that were previously only

     reasonably capable of being performed by mathematics adepts can now be

     achieved by those who understand the principles only and don't want or need

     to juggle with formulas. But even the mathematics adepts can find life is a

     lot easier if they have standard formula re-expressions handy, or better, if

     they are in the form of ready to hand functions.

     Excel gives us these powers but they are not in a very user friendly form

     and at present, only those with knowledge and skills in financial maths are

     able to get the greatest use out of the program.

     *** Definitions ***

     There are two commonly quoted interest regimes:

     1. APR (Annual Percentage Rate) or 'Nominal'

     2. Effective

     Under the APR regime an interest rate is quoted in annual terms and *should*

     be quoted together with a compounding frequency per year. In calculating the

     interest the annual rate is divided by the compounding frequency and that

     rate is applied to the number of periods calculated in terms of the

     frequency. Thus if we use the commonly quoted APR(12) at (say) 6%, a rate of

     6%/12 = 0.5% is applied to the number of months involved in the calculation.

     Under the Effective regime an interest rate is quoted together with the

     period for which it is effective. Thus we might quote a rate of 5% per annum

     effective or 0.25% per month effective.

     Legislative and customary usage can cause confusion. Where a rate is merely

     labeled 'APR' you should assume (pending check of 'small print') that it is

     the APR(12) or more correctly described 'Nominal compounded monthly' rate.

     Similarly, we might see '7% effective' quoted and here we should assume

     (pending check of 'small print') that this in an annual effective rate.

     It should be clear that the effective rate is a more 'truthful' rate. Where

     Nominal and Effective quoted rates are the same, the impact of compounding

     is such that the Nominal rate produces more interest than the Effective

     rate. Similarly, for the same quoted level of rate a Nominal rate with a

     higher frequency of compounding produces more interest that one with a lower

     frequency of compounding.

     One rate, the Annual Effective Rate, is special. It is the only rate which

     has the same absolute level under both regimes; 6% per annum effective is

     the same as 6% Nominal compounded once per year. For this reason, financial

     calculators and Excel conversion routines and algorithms make a lot of use

     of the annual effective rate for conversions between regimes.

     Caution! Legislators have been at work in many countries in the area of

     forcing declarations of interest in lending and leasing documents and

     advertisements. Would you believe that there are cases where the legislators

     have stuffed up the definitions? In the UK, for example, original

     legislation on truth in lending required the quotation of a rate to be

     labeled 'APR' and then went on to give a perfect definition of the Annual

     Effective Rate! I'm not sure whether or not this has been changed or whether

     they have had to live with the error.

     Further, you do need to look at the fine print of the legislation because

     frequently there is a requirement for the statutory rate quotation to take

     account of various fees and charges and assumptions on term of lease or

     loan. You will need to use the basic principles set out here, but the

     calculations will be much more complex.

     *** Principle of Equivalence ***

     Any interest rate compounded at one frequency can be expressed as being

     equivalent to another interest rate compounded at another frequency.

     Using a simple example:

     5% per half year effective is equivalent to (1+0.05)^2 -1 = 10.25% per annum

     effective.

     We can use similar compound interest formulas and re-expressions to

     calculate equivalent rates to any quoted rate. We can express many different

     quotations of interest rates in terms of a common equivalent. Usually, that

     common equivalent will be the Annual Effective rate, but often custom or

     'Truth in Lending' legislation will require expression in terms of the

     APR(12); better described as the Annual Nominal Compounded Monthly.

     *** Concept of Conversion between Nominal and Effective Regimes ***

     There are 10 Interest Rate Conversions commonly required although we can

     boil them down to the solution of a common equation of equivalence:

     (1+Nomx/Freqx)^Freqx = (1+Nomy/Freqy)^Freqy

     Nomx and Nomy are Nominal (APR) rates compounded at frequencies per year of

     x and y.

     Effx and Effy are Effective rates for frequencies of compounding per year of

     x and y.

     It's very important to note that where Freqx (or Freqy) is 1, then

     Nomx/Freqx or (Nomy/Freqy) is the Annual Effective Rate.

     This leaves now leads in to the formulas required for interest rate

     conversion:

     *** Interest Rate Conversion Formulas ***

     If we regard Annual Effective as a "Special" rate there are no less than 10

     commonly required Interest Rate Conversions. Therein lays the cause of the

     common confusion. Here they are together with the formulas:

     1 Effx_Nomx         Effective for frequency to Nominal for Same Frequency

             = Effx * Freqx

     2 Nomx_AnnEff       Nominal for frequency to Annual Effective

             = (1 + Nomx / Freqx) ^ Freqx - 1

     3 AnnEff_Nomx       Annual Effective to Nominal

             = Freqx * ((1 + AnnEff) ^ (1 / Freqx) - 1)

     4 Nomx_Effx         Nominal for frequency to Effective for same Frequency

             = Nomx / Freqx

     5 Effx_AnnEff       Effective for frequency to Annual Effective

             = (1 + Effx) ^ Freqx - 1

     6 Effx_Nomy         Effective for frequency to Nominal for a different

     frequency

             = Freqy * ((1 + Effx) ^ (Freqx / Freqy) - 1)

     7 Effx_Effy         Effective for frequency to Effective for different

     frequency

             = (1 + Effx) ^ (Freqx / Freqy) - 1

     8 Nomx_Nomy         Nominal for a frequency to Nominal for a different

     frequency

             = Freqy * ((1 + Nomx / Freqx) ^ (Freqx / Freqy) - 1)

     9 Nomx_Effy         Nominal for a frequency to Effective for a different

     frequency

             = (1 + Nomx / Freqx) ^ (Freqx / Freqy) - 1

     10 AnnEff_Effx      Annual Effective to Effective for a frequency

             = (1 + AnnEff) ^ (1 / Freqx) - 1

     Those are the essential tools of most basic financial calculations. If you

     understand those, you are way ahead of the pack and incidentally you've just

     broken through the first pain barrier of financial analysis.

     These 10 conversions can be shown on a diagram that illustrates the overall

     scheme of conversions:

                 AnnEff

     Nomx                Nomy

     Effx                   Effy

     That diagram with pretty connecting arrows and a table of Excel formulas,

     UDF functions and Sharp Financial Calculator routines brings understanding

     to 100% of students in 2 hours of tutorial plus 1 hour private study. Before

     I introduced it, there was much wailing and gnashing of teeth. There were

     abysmal levels of understanding after about 12 hours of "teaching" and

     endless hours of padded cell torture. We now have 10 hours extra for

     generating more understanding and applications (and students have more time

     for B & B).

     *** Interest Rate Conversion Functions ***

     Since interest rate conversions are required so often and are often nested

     within other functions, I find the following User Defined Functions are

     pretty essential and I have derived a systematic approach to their naming

     and ordering of the function arguments that are intended make their use very

     easy.

     But first, here are the 10 User Defined Functions:

     1 EFFECTIVE FOR FREQUENCY TO NOMINAL FOR SAME FREQUENCY

     Function Effx_Nomx(Effx As Double, Freqx As Double) As Double

             Effx_Nomx = Effx * Freqx

     End Function

     2 NOMINAL TO ANNUAL EFFECTIVE

     Function Nomx_AnnEff(Nomx As Double, Freqx As Double) As Double

             Nomx_AnnEff = (1 + Nomx / Freqx) ^ Freqx - 1

     End Function

     3 ANNUAL EFFECTIVE TO NOMINAL

     Function AnnEff_Nomx(AnnEff As Double, Freqx As Double) As Double

             AnnEff_Nomx = Freqx * ((1 + AnnEff) ^ (1 / Freqx) - 1)

     End Function

     4 NOMINAL FOR FREQUENCY TO EFFECTIVE FOR SAME FREQUENCY

     Function Nomx_Effx(Nomx As Double, Freqx As Double) As Double

             Nomx_Effx = Nomx / Freqx

     End Function

     5 EFFECTIVE FOR FREQUENCY TO ANNUAL EFFECTIVE

     Function Effx_AnnEff(Effx As Double, Freqx As Double) As Double

             Effx_AnnEff = (1 + Effx) ^ Freqx - 1

     End Function

     6 EFFECTIVE FOR FREQUENCY TO NOMINAL FOR DIFFERENT FREQUENCY

     Function Effx_Nomy(Effx As Double, Freqx As Double, Freqy As Double) As

     Double

             Effx_Nomy = Freqy * ((1 + Effx) ^ (Freqx / Freqy) - 1)

     End Function

     7 EFFECTIVE FOR FREQUENCY TO EFFECTIVE FOR DIFFERENT FREQUENCY

     Function Effx_Effy(Effx As Double, Freqx As Double, Freqy As Double) As

     Double

             Effx_Effy = (1 + Effx) ^ (Freqx / Freqy) - 1

     End Function

     8 NOMINAL FOR FREQUENCY TO NOMINAL FOR DIFFERENT FREQUENCY

     Function Nomx_Nomy(Nomx As Double, Freqx As Double, Freqy As Double) As

     Double

             Nomx_Nomy = Freqy * ((1 + Nomx / Freqx) ^ (Freqx / Freqy) - 1)

     End Function

     9 NOMINAL FOR FREQUENCY TO EFFECTIVE FOR DIFFERENT FREQUENCY

     Function Nomx_Effy(Nomx As Double, Freqx As Double, Freqy As Double) As

     Double

             Nomx_Effy = (1 + Nomx / Freqx) ^ (Freqx / Freqy) - 1

     End Function

     10 ANNUAL EFFECTIVE TO EFFECTIVE FOR FREQUENCY

     Function AnnEff_Effx(AnnEff As Double, Freqx As Double) As Double

             AnnEff_Effx = (1 + AnnEff) ^ (1 / Freqx) - 1

     End Function

     What is the Logic that Allows Easy Choice and Naming of Function

     To use a function you need to be able to remember the name accurately. So

     naming, which we often relegate to a few seconds thought, is very important

     when there are 10 different functions for 10 different purposes. So I have

     derived and implemented a very simple algorithm for naming:

     1. First I named the various rates and frequencies:

     Nomx and Nomy are Nominal (APR) rates compounded at the compounding

     frequencies per year of x and y.

     Effx and Effy are Effective rates for the frequencies of compounding per

     year of x and y

     Freqx and Freqy are required for arguments. They are the numeric values

     representing the number of compounding periods per year of Nomx and Nomy

     (two different Nominal (APR) rates).

     AnnEff is regarded as a special case, which indeed it is, because it is the

     only rate where the absulute level is the same for both Nominal and

     Effective. Nominal compounded 1 times per year *is* the annual effective

     rate.

     2. This gives me my function name convention:

     RateYouHave_RateYouWant

     If there's only one species of Nominal rate (APR) or Effective rate then we

     use Nomx and Effx.

     Easy!

     I have Annual Effective. I want Nominal compounded monthly. Function name?

     AnnEff_Nomx

     I have a nominal rate compounded monthly (our common friend APR(12)) and I

     want the annual effective equivalent. Function name?

     Nomx_AnnEff

     What arguments are required and what order do they come in?

     1. First argument is always the rate you have

     2. Second argument is always freqx

     3. If there is another frequency involved in the two rates (known +

     required) and if that frequency is not 1, then you need the third argument

     freqy.

     And that's all there is to it. With those formulas and functions you now

     have the base tools for a comprehensive range of calculations. A whole World

     of applications can now be developed. You are no longer constrained by

     simplifying assumptions that produce errors and distortions. And when you

     get results from Excel Functions and your applications, you can understand

     them and convert them to common bases for evaluation.

     For further and better explanations with examples including ones that

     integrate the functions in Excel financial functions see John Walkenbach's

     Excel 2002 Formulas.

     HTH

     --

     Norman Harker

     Sydney, Australia

     Roll on Christmas 25th Dec and 7th Jan