Effective Interest Rates
Article Index
Effective Interest Rates
Using spreadsheets to convert rates
EIR, APR and credit card interest
Compounding periods
Where does the exp come from?
Net and gross - tax allowances
Examples and keypoints

Banner

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 Figure 1 in column A.

 

fig1

Figure 1

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.

fig2

Figure 2

First enter the labels shown in in row 1 of Figure 2. 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 in Figure 3.

fig3

<ASIN:0470044039>

Banner



 
 

   
RSS feed of all content
I Programmer - full contents
Copyright © 2014 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.