# 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 FunctionsThe DISC and INTRATE functions, which are related, work from the same basic set ofarguments: 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 datespecifies the date the bond matures, or expires. You may enter the date arguments either astext strings enclosed in quotation marks (for example, "7/4/99") or as serial date values (forexample, 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 aquarterly coupon.The basis argument specifies the number of days in the month and in the year assumed forthe date calculations. You specify the basis as 0 for the US (or NASD) version of 30 daysin 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 numberof days in the month and 365 days in a year; and 4 for the European version of 30 daysin a month and 360 days in a year.The EFFECTIVE and NOMINAL functions, which are also related, work from a set ofthree arguments: the effective annual interest rate, the nominal interest rate, and the numberof compounding periods in the year.Using the DISC FunctionThe DISC function calculates the discount rate for a security-the amount by which theredemption 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, \$100redemption-value bond that you purchased on July 10, 2000, for 99.875. If you choose touse the US (or NASD) day-count-basis assumption, you use the following formula to makethis 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 argumentsis invalid or if a bond price or redemption value is set to zero.Using the EFFECT FunctionThe EFFECT function calculates the effective annual interest given the stated annual interestrate 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, anominal rate argument equal to 0, or a number of compounding periods argument equal to some value less than 1.Using the INTRATE FunctionThe 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, \$100redemption-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 FunctionThe function calculates the nominal annual interest given the effective annual interest rateand 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 is6.1831% and this rate is based on daily compounding (based on a 360-day year), you usethe 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, anominal rate argument equal to 0, or a number of compounding periods argumentequal to some value less than 1.