The One and Only EFFECTIVE Interest RateFinancial tools like Microsoft Excel using PPR are dangerous Interest rates are either continuous or discrete. Interest formulas are written in either continuous or discrete form. Continuous rates together with continuous interest formulas or discrete rates with discrete formulas. It does not matter (au fond) which one you choose, both roads lead to Rome. You only cannot mix both. Continuous rates do not fit into discrete formulas nor do discrete rates fit into continuous formulas. Microsoft Excel makes a mess by mixing these up. An interest formula with (1 + interest rate) for instance function PV in Microsoft Excel means that the 'interest rate' cannot have a dimension. Excel states 0.08/12 = 0.00666666... Excel gives the answer 59,777.15 to the PV of monthly payment 500, during 20 years, whereas annual rate is 8 %. Via (1.00666666...)12 = 1.08299950... the used MPR (Monthly Percentage Rate) can be transformed into the equal APR (Annual Percentage Rate) 8.299950... %; to two decimal places 8.30 %. Whereas Excel states 0.08/12 = 0.00666666... this rate must be the continuous i ending up into
B0 = 59,658.4570... Microsoft XL, financial function PV: monthly payment 500, 20 years, annual rate 8 % Discrete rate
..?.. 0.08/12 N = 240 Discrete formula B0 = 59,777.15
year month Continuous rate
8 %/year 0.08/12 N = 240 Continuous formula B0 = 59,658.46 Microsoft XL starts with 8 %/year and ends up with B0 = 59,777.15 That does not make sense.
Either you start with 8 %/year but then you get B0 = 59,658.46
or starting with 0.08/12 monthly discrete interest rate the answer is B0 = 59,777.15 but in this case the corresponding annual discrete interest rate is 8.30 %. Not 0.08 but 0.083. Just a number, no more, no less.
If indeed 8 % annual discrete interest rate then the corresponding monthly discrete interest rate is 0.6434... %. Compare with 0.08/12 = 0.00666666... Discrete rate
0.08 0.006434 N = 240 Discrete formula B0 = 61,038.87
year month
These are not just slight differences. The differences are significant.
Watch out, be careful, when undertaking financial calculations. The so-called effective interest rate in Microsoft Excel is NOT the effective rate! Banks as well as other financial institutions name nominal rates in big figures in advertisements and so on, to impress all of us. And governments make laws to protect people against excesses. Much diversity of opinion seems to be common regarding what is really the growth of capital, even in science. It starts with a certain definition given by scientists and laid down in a treaty, embedded in the constitution, and finally all are chained up. Microsoft Excel, Hewlett Packard, Texas Instruments, banks, the public, everybody is chained up. It seems to be true for everyone to see everywhere. It is covered by scientists. Any smart student, anybody making up his/her own mind can easily understand where it does not fit. A discrete rate, any discrete rate can never be effective. The word 'effective' means real, true, truly, i.e. there can be only one effective rate. According to the opinion of some scientists annual rates can be or even must be named 'effective'. Why 'one year'? There is no science in it. An amount of money is growing as time goes by. A growing process all of the time. Never a dull moment. It does not grow faster in some periods and slower at other moments. It grows steadily, well described by the e-power. There are many more growth processes described by the e-power. The process of capital growth is just one of the applications using the formula FV = PV . eit whereas i (only this i) is the effective rate. Not my definition, not a definition by mankind, but the one and only truth. Science is trying to find such regularities. Usually there is little science in definitions. Refer to mathematics and observations of growth processes in practice. By making calculations with the e-power, the most precise answers on all money calculus can easily be found in the least necessary steps. Every step in calculations is dangerous because at each step accuracy may be lost. Moreover, i is a fraction, so i = 12 %/year º 1 %/month. Discrete interest rates are not fractions and that is where all the misery starts. What Microsoft Excel (and other financial tools as well) is calculating:
([1] + [1/TIME]) is not allowable. In the US it is common practice - there is no reason to adopt nor sustain that common practice - to speak of an 8 % nominal rate when the so-called effective rate (APR) is 8.30. This is called by definition 'the effective rate' in the US. The US Congress attempted to remedy the situation with the passage of 'Truth-in-Lending' in 1967. There are detailed regulations ('Regulation Z') that explicitly set forth the formulas to be used and what items are to be considered in calculating interest. In general, results are stated as an APR (Annual Percentage Rate). Microsoft claims:
The problem is not Excel; XL is applying the correct formulas. The problem is that many of the people using XL and other financial tools are not aware of the distinctions between "effective" and "nominal" interest. Summons:
Lay i down asap in constitutions all over the world to make it crystal clear to everybody for ever. To make calculations short and easy. To minimize costs. To improve capital budgeting decisions. Meanwhile Microsoft Excel is in a tight situation between what is proven science on the one hand and on the other 'Regulation Z'. Microsoft XL is NOT applying the correct formulas and is NOT aware of the distinctions between "effective" and "nominal" interest, broadcasting: - 8 %/year nominal interest rate = 0.08/12 per month
MERELY SPEAKING OF - inserting this 0.08/12 per month interest rate into (1 + interest rate)
ENCROACHMENT ON DIMENSION - speaking of effective rate is 8.30
APR 8.30 º MONTHLY PERCENTAGE RATE 0.08/12
IDENTICAL
If APR 8.30 is called 'effective' then the same qualification applies to the rate on the right of this equation and to innumerable other rates.
Hence APR 8.30 cannot be the effective rate.
The man in the street has only to push the e-key on any simple pocket-calculator to defeat the entire financial community that is clinging to old traditions. Who is afraid of the e-power? Busy with all kinds of financial tools. Neither effective (not doing right things) nor efficient (not doing things right). The measuring-staff is i, the continuous interest rate. Function FV = PV . eit People become confused by using a PPR, any Period Percentage Rate. Starting from reference i you will be lead directly to Rome. PPR is not user-friendly. Moreover, the continuous i is the reference and will stay the reference forever. An 'annual rate' (discrete) that is arrived at by multiplying a periodic rate by the number of periods - as Microsoft Excel does - is not an appropriate measure. Maybe 'the man in the street' an average person has enough trouble with grade school arithmetical concepts and the e-power is totally foreign to him, still he is able to push the e-key on the keyboard of any pocket-calculator. How many keys are we all pushing all the time? Not knowing what. Only be interested in the outcome. There is no reason NOT to use the continuous i. The e-key leads you right to Rome. The continuous i is a fraction. Grade school arithmetical concepts are enough to handle the continuous i, yes indeed, and not PPR. Using PPR (Discrete Compound Interest) to rank investments is often deceptive. Financial tools like Microsoft Excel using PPR are dangerous. Still today many people stick to the simple interest (SI). Why? One reason is undoubtedly its simplicity. It holds both SI p % annually = SI p/12 % monthly and i % annually = i/12 % monthly. The interest rates both SI and i are fractions as distinct from discrete PPR rates which are not fractions. In the case of PPR, even the best mathematicians must be on their alert and many people are being fooled with PPR. This text is summing-up: SSRN340301_Microsoft Excel, Financial Functions, Matter in Dispute |
