Interest calculations.

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.

On Apr 12, 8:24*pm, "DB." wrote:
* * 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.

As AER is compounded daily, the daily interest is the 365th root of
the AER, i.e. DailyInterest^(365)=AER.

So your formula in A4 should read =A1*(A2^(A3/365)), if in A2 your AER
is expressed as a multiplier (ie 5.5% = 1.055).

If the cell is formatted as a percentage, then you need
=A1*((A2+1)^(A3/365))

Put in days=365 to check that this works and gives the AER.

Hope this helps

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

"Alex Simmons" wrote in message
...
On Apr 12, 8:24 pm, "DB." wrote:
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.

As AER is compounded daily, the daily interest is the 365th root of
the AER, i.e. DailyInterest^(365)=AER.

So your formula in A4 should read =A1*(A2^(A3/365)), if in A2 your AER
is expressed as a multiplier (ie 5.5% = 1.055).

If the cell is formatted as a percentage, then you need
=A1*((A2+1)^(A3/365))

Put in days=365 to check that this works and gives the AER.

Hope this helps


My, that was quick! Yes, it works (of course!) Very many thanks!
From a reply to a recent posting here I've learned how to put in my
deposit and withdrawal dates to calculate the 'days in' I'll need in
cell A3 (above). I'm learning!

--
DB.

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

On Sat, 12 Apr 2008 19:24:44 GMT, "DB." wrote:

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!

Using Excel financial functions, and assuming 365 compounding periods per year,
you could use this formula:

=FV(NOMINAL(AER,365)/365,Days,,-Deposit)

--ron

IMHO Norman knows more about this subject than anyone (at least that I know)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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