Page 2 of 7
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.
Figure 1
Next enter the formulae:
=(1+B3/B4)^B41
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.
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)^121
into B2 and the formula to calculate the difference between the nominal and effective rate:
=B2A2
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 XY chart with Nominal rate (column A) on the Xaxis and Difference (column C) on the Yaxis as shown in Figure 3.
<ASIN:0470044039>
