An effective rate spreadsheet

The conversion of nominal to effective rate is difficult enough to warrant a spreadsheet devoted to it. First enter the text as shown in the screen dump below in column A.

Next enter the formulae:

=(1+B3/B4)^B4-1

in cell B5 and

=((1+B7)^(1/B8)-1)*B8

in cell B9 and set percentage formats with two decimal places on B3, B5, B7 and B9. Don't worry that you see #DIV/0! in the cells where the formulas are entered - this is just because no valid data has been entered for the formulae to work on. .

For most rate calculations the compounding period will be a month, making B4 and B8 almost permanently set to 12.

If you enter the nominal rate into B3 then the effective rate will be calculated in B5. If you enter the effective rate in to B7 then the nominal rate will be calculated in B9.

Nominal to effective converter

Another useful spreadsheet is one that shows the effective annual rate for a range of nominal annual rates.

This is very simple to construct.

First enter the labels shown in in row 1 of screen dump. Next enter the nominal percentage rates 1% to 30% in column A. Enter the formula to calculate the effective rate:

=(1+A2/12)^12-1

into B2 and the formula to calculate the difference between the nominal and effective rate:

=B2-A2

into C2.

Set both these cells to display in percentage format with two decimal places. Copy the two formluas in B2:C2 into B3..C31.

Notice the way that the difference between the nominal and effective rate increases as the nominal rate increases. You can see this more clearly by creating an X-Y chart with Nominal rate (column A) on the X-axis and Difference (column C) on the Y-axis as shown below.

The EIR - Effective Interest Rate

The use of the effective rate is enshrined in legislation in many countries but both the law and the terminology used varies.

For example in the USA the term APR is used to refer to the nominal Annual Percentage Rate i.e. without taking compounding into account but in the UK the same term, APR, refers to the Annual Percentage Rate which does take compounding (and other one off payments) into account.

You can see that confusion is not only possible but likely in reading any financial document. Your only hope of getting things right is to understand what is being calculated.

In general the term Effective Interest Rate (EIR) is used to refer to the general concept of including compounding in the quoted rate but this is sometimes called by other names, for example APR (Annual Percentage Rate) in the UK.

As we already know the formula to convert nominal to effective rate you might think that there is very little more to say about EIR but in fact there is a great deal.

The reason is simply that there are many different forms of loan involving different repayment schemes - regular payments, single payments and the effect of additional charges to consider. However in one very simple situation - where the interest is simply calculated on the balance at a periodic interval - we do indeed know how to calculate the EIR.

More complex loans will be covered in a later chapter.

Credit card EIR/APR

Although the EIR/APR has to be quoted for a great many types of loan, the most commonly encountered is the credit card.

In this case the nominal interest rate is quoted as a monthly rate and conversion has to be made to an effective annual rate.

For example, if an interest rate of 1% per month is quoted, the nominal annual rate (the APR in the USA) is 12% while the effective annual rate (the APR in many countries and the EIR in the USA) is 12.6825%.

Similarly a 2% per month nominal rate is equal to an EIR/APR of 26.8%. It is very important to notice that this simple calculation assumes that there are no charges other than the monthly percentage quoted.

Cards that charge fixed annual fees and cash advances which attract a single surcharge have an EIR/APR that is calculated by slightly more complicated methods.

A spreadsheet to calculate the EIR/APR on the monthly rate is easy enough to construct. First enter the text into column A as shown in below.

Next enter the formula:

=(1+B5)^12-1

into B5. This calculates the effective annual rate from the monthly nominal rate.

In the UK the effective rate has to be truncated to one decimal place to be called the APR and this is worth calculating because it demonstrates how to truncate a percentage in general.

To do this we can use the TRUNC(val,n) function which simpley chops off the decimal places in val to leave just n digits after the decimal point. You might think that you need TRUNC(val,1) but if you remember that percentages are actually stored as decimal fractions you should be able to see that the correct formula if TRUNC(val,3) e.g. 52.13% is in fact stored as 0.5213 and truncating this to three decimal places gives 0.521 which displays as 52.1.

So truncating a percentage to one decimal place corresponds to truncating the equivalent decimal fraction to three decimal places. This makes the correct formula:

=TRUNC(B5,3)

which should be entered into B6 to complete the spreadsheet.

<ASIN:1118490363>

<ASIN:0981893546>

<ASIN:1118018508>