Thanks, Niek. That little lot is going to need some taking-in! I'm
going to be late to bed tonight!

--
DB.


"Niek Otten" wrote in message
...
I'm sure there are shorter answers. But this is an answer fron Norman
Harker. That's different stuff!

--
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




"DB." wrote in message
...
| Here in UK, banks seeking depositors must (for comparison
purposes)
| quote the AER (Annual Equivalent Rate) for each type of account they
| offer. So that although interest may be earned daily the AER tells
us
| what the accrued daily interest will total in a year's time. Thus
| £2,000 invested on Jan.1st in an account offering 5.5% AER will have
| £110 added a year later.
| I wish to know what interest my deposit will have earned should I
| close the account early. So:
| In A1 I put the sum deposited.
| In A2 I put the AER (as a percentage).
| In A3 I put the number of days the money will have been in the
account.
|
| What must I put in A4 to calculate the interest I might expect?
|
| TIA for any (all) reply (replies). As an old dog, slow at learning
new
| tricks, I regard you who answer our questions on this ng as
geniuses!
|
| --
| DB.
|
|