Using the Interest Rate Add-in Functions

By: Stephen L. Nelson, CPA

Excel provides four add-in functions that let you make sophisticated interest rate calculations: DISC, EFFECT, INTRATE, and NOMINAL.

Some Background Info on the Interest Rate Add-in Functions

The DISC and INTRATE functions, which are related, work from the same basic set of
arguments: the settlement date, the maturity date, the redemption value, the price, the frequency, and the basis.

The settlement date specifies the date the bond is settled, or purchased. The maturity date
specifies the date the bond matures, or expires. You may enter the date arguments either as
text strings enclosed in quotation marks (for example, "7/4/99") or as serial date values (for
example, 37000 for April 19, 2001.)

The redemption argument is the bond's redemption value per each $100 of face value.
The price argument shows the price of a bond expressed as a percentage of its face value.
For example, a bond that cost $991.83 would be priced at 99.183.

The frequency argument gives the number of coupon payments made each year: you specify 1 to indicate an annual coupon, 2 to indicate a semiannual coupon, and 4 to indicate a
quarterly coupon.

The basis argument specifies the number of days in the month and in the year assumed for
the date calculations. You specify the basis as 0 for the US (or NASD) version of 30 days
in a month and 360 days in a year; as 1 for the actual number of days in the month and year;
2 for the actual number of days in the month but 360 days in a year; 3 for the actual number
of days in the month and 365 days in a year; and 4 for the European version of 30 days
in a month and 360 days in a year.

The EFFECTIVE and NOMINAL functions, which are also related, work from a set of
three arguments: the effective annual interest rate, the nominal interest rate, and the number
of compounding periods in the year.

Using the DISC Function

The DISC function calculates the discount rate for a security-the amount by which the
redemption value is reduced expressed as an annual percentage-given its settlement date,
maturity date, price, redemption, and basis. The function uses the following syntax:

DISC (settlement, maturity, price, redemption, basis)

For example, suppose you want to calculate the discount rate on a zero-coupon, $100
redemption-value bond that you purchased on July 10, 2000, for 99.875. If you choose to
use the US (or NASD) day-count-basis assumption, you use the following formula to make
this calculation:

=DISC("7/10/2000","11/30/2000",97.875,100,0)

The function returns the value .054643, which is equivalent to 5.4643%.

NOTE: The DISC function returns an error value if a date argument or the set of date arguments
is invalid or if a bond price or redemption value is set to zero.

Using the EFFECT Function

The EFFECT function calculates the effective annual interest given the stated annual interest
rate and the number of annual compounding periods. The function uses the following syntax:

EFFECT (nominal rate, compounding periods)

For example, if you want to calculate the effective interest rate when the nominal rate is 6%, but this rate is compounded daily (based on a 360-day year), you use the following formula:

=EFFECT(.06,360)

The function returns the value .061831, which is equivalent to 6.1831%.

NOTE: The EFFECT function returns an error value if you supply nonnumeric arguments, a
nominal rate argument equal to 0, or a number of compounding periods argument equal to some value less than 1.

Using the INTRATE Function

The INTRATE function calculates the interest rate for a fully invested, or zero-coupon,
security given its settlement date, maturity date, the initial investment amount, the redemption value, and the basis. The function uses the following syntax:

INTRATE (settlement, maturity, investment, redemption, basis)

For example, suppose you want to calculate the interest rate on a zero-coupon, $100
redemption-value bond that you purchased on July 10, 2000, for 99.875. If you choose to use the US (or NASD) day-count basis assumption, you use the following formula to make this calculation:

=INTRATE ("7/10/2000","11/30/2000",97.875,100,0)

The function returns the value .055829, which is equivalent to 5.5829%.

NOTE: The INTRATE function returns an error value if a date argument or the set of date arguments is invalid or if the investment or redemption value is set to zero.

Using the NOMINAL Function

The function calculates the nominal annual interest given the effective annual interest rate
and the number of annual compounding periods. The function uses the following syntax:

NOMINAL (effective rate, compounding periods)

For example, if you want to calculate the nominal interest rate when the effective rate is
6.1831% and this rate is based on daily compounding (based on a 360-day year), you use
the following formula:

=NOMINAL( .061831,360)

The function returns the value .06, which is equivalent to 6%.

NOTE: The EFFECT function returns an error value if you supply nonnumeric arguments, a
nominal rate argument equal to 0, or a number of compounding periods argument
equal to some value less than 1.

Top Searches on
Banking
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 

» More on Banking