Using the Interest Rate Add-in Functions

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.