Saturday, June 8, 2019

The Basics of the Excel EFFECT function



This question shows us how the effective rate of a debt instrument with a nominal yield of 6 percent varies with the number of payments per year.  The effective rate is also calculated for the case of continuous compounding.   The output from the EFFECT function is shown to be consistent with a formula for the effective rate.  

Question:   Consider a bond with a nominal yield of 6.0 percent.   What is the effective yield on the bond when the number of yearly payments is 1, 2, 4, 12, or 52?

Some basic concepts: 

Nominal Rate -- The annual interest payments divided by the face value of the bond.

The Effective Rate -- The effective rate is the annual rate which corrects for the number of payments per year on the bond.

Using Excel to Calculate the Effective Rate:

The calculation using the Excel Effect function is presented below.

Col A
Col B
Col C
Col D
Nominal Rate
payments per year
Effective rate
Code
6.00%
1
6.000%
"effect(a3,b3)
6.00%
2
6.090%
"effect(a4,b4)
6.00%
4
6.136%
"effect(a5,b5)
6.00%
12
6.168%
"effect(a6,b6)
6.00%
52
6.180%
"effect(a7,b7)


The effective rate can also be calculated for continuously compounded returns.  The formula for this calculation is exp(0.06)-1, which is 6.184%.

The effective rate can also be calculated without the yield function from the formula r=(1+1/n)n  - 1


Confirm this formula is consistent with the EFFECT function.   Note that


6.136% = (1+6.0/4)4  - 1


The next post will look at effective rates of mortgages with and without posts and with and with and without prepayment. 

No comments:

Post a Comment