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