Excel contains a large number of formulas that can be used to determine the interest rate on a variety of investments. In this post, I’ve listed the key interest rate formulas, including a brief explanation of each one, the components of each formula, and how the formula is used in an example [The definitions of the components of the various formulas are summarized in a set of definitions at the end of this section]. Enjoy!

Advertisement

 

Calculation Of Accrued Interest On A Security That Pays Interest At Maturity

For this calculation, use the ACCRINTM formula.

The details of the formula are:

ACCRINTM(Issue Date, Maturity Date, Annual Coupon Rate, Par Value)

Example:  If the issue date is 12/12/99 and the maturity date is 5/15/04 on a 9.5% coupon rate bond with a par value of $1,000, the formula would be:

ACCRINTM(“12/12/99”,“5/15/04”,9.5%,1000)

 

Calculation Of Accrued Interest On A Security That Pays Periodic Interest

This formula is used to accrue the amount of interest earned from a stream of regular interest payments from an investment. For this calculation, use the ACCRINT [without ‘M’] formula.

The details of the formula are:

ACCRINT(Issue Date, First Interest Date, Settlement Date, Annual Coupon Rate, Par Value, Number of Payments per Year)

 

Example: If the issue date is 3/31/01, the first interest date is 4/15/01, the settlement date is 4/10/01, the annual coupon rate is 11%, the par value is $1,000, and there are four payments per year, then the formula will be:

ACCRINT(“3/31/01”,“4/15/01”,“4/10/01”,11%,1000,4)

 

Calculation Of Annual Yield For A Discounted Security

If a company purchases a security at a discounted rate (usually because the stated interest rate is lower than the prevailing market rate), one should use the YIELDDISC formula to determine its annual yield.

The details of the formula are:

YIELDDISC(Settlement Date, Maturity Date, Price per $100 Face Value), Redemption Value)

 

Example: If the settlement date is 9/9/05, the maturity date is 12/31/15, the price is $101, and the redemption value is $100, then the formula will be:

YIELDDISC(“9/9/05”,“12/31/15”,101,100)

 
Calculation Of Yield For A Treasury Bill

To calculate this yield, use the TBILLYIELD formula.

The details of the formula are:

TBILLYIELD(Settlement Date, Maturity Date, Price Per $100 Face Value)

 

Example: If the settlement date is April 13, 2005, the maturity date is June 15, 2012, and the price per $100 face value is $94.30, then the formula will be:

TBILLYIELD(“4/14/05”,“6/15/12”,94.3)

 
Calculation Of Yield On A Security That Has A Short Or Long First Period

A security that was purchased in between its coupon payment dates will still earn the owner the full amount of the next coupon, even though the security was not held for the full period, which results in a higher than normal interest rate earned for the first period. To calculate the full-term yield with the odd-length first period, use the ODDFYIELD formula.

The details of the formula are:

(ODDFYIELD(Settlement Date, Maturity Date, Issue Date, First Coupon Date, Annual Coupon Rate, Price per $100 Face Value, Redemption Value, Number of Payments per Year)

 

Example: If the settlement date is May 2, 2003, the maturity date is November 11, 2008, the issue date is June 6, 2001, the first coupon date is July 7, 2003, the interest rate is 8.5%, the price is $98.25, the redemption value is $100, and four coupon payments are made per year, then the formula will be:

ODDFYIELD(“5/2/03”,“11/11/08”,“6/6/01”,“7/7/03”,8.5%,98.25,100,4)

 
Calculation Of Yield On A Security That Has A Short Or Long Last Period

This is the same type of situation as just described in the preceding scenario, except that we are now selling a security prior to the next scheduled coupon date. The formula now changes to ODDLYIELD.

The formula detail is:

ODDLYIELD(Settlement Date, Maturity Date, Last Coupon Date, Annual Coupon
Rate, Price per $100 Face Value, Redemption Value, Number of Payments per Year)

 

Example: if the settlement date is May 2, 2003, the maturity date is November 11, 2008, the last coupon date is August 11, 2008, the interest rate is 8.5%, the price is $98.25, the redemption value is $100, and four coupon payments are made per year, then the formula will be:

ODDLYIELD(“5/2/03”,“11/11/08”,“8/11/08”,8.5%,98.25,100,4)

 

 
Calculation Of Yield On A Security That Pays Interest At Maturity

Some securities pay all interest at the redemption date, rather than as regular coupon payments. To calculate the yield on these types of securities, use the YIELDMAT formula.

The detail for this formula is:

YIELDMAT(Settlement Date, Maturity Date, Issue Date, Annual Coupon Rate, Price per $100 Face Value)

 

Example: If the settlement date is February 15, 2002, the maturity date is April 15, 2011, the issue date is October 5, 2001, the interest rate is 8.2%, and the price is $101.125, then the formula will be:

YIELDMAT(“2/15/02”,“4/15/11”, “10/5/01”,8.2%,101.125)

 

Calculation Of Yield On A Security That Pays Periodic Interest

This is the standard formula for a basic bond purchase that has no unusual variations in terms of purchase or sale dates, and for which coupon payments are made in standard amounts and on regularly scheduled dates. For this situation, use the YIELD formula.

The detail of the formula is:

YIELD(Settlement Date, Maturity Date, Annual Coupon Rate, Price per $100 Face Value, Redemption Value, Number of Payments per Year)

 

Example: If the settlement date is January 8, 2001, the maturity date is May 15, 2007, the annual coupon rate is 7.5%, the price is $100.50, the redemption value is $100, and there are two coupon payments per year, then the formula will be:

YIELD(“1/8/01”,“5/15/07”,7.5%,100.50,100,2)

Most of the components of the above formulas are identical. To keep from repeating the definitions of each component for every formula listed above, they are summarized below:

Annual coupon rate. The listed coupon rate on a security.

First Interest date. The first date on which interest is earned on a security.

Issue date. The date on which a security is issued.

Last coupon date. The last coupon date for a security prior to its redemption date.

Maturity date. The date on which a security expires.

Number of payments per year. The number of coupon payments per year.

Par value. The listed price on a security.

Price per $100 face value. The actual price paid for a security can be higher or lower than the face value, depending on the discount or premium paid to acquire the stated interest to be paid on the security.

Redemption value. The amount paid at the termination date of the security per $100 of face value.

Settlement date. The date when the security is issued to the buyer.

 

The formulas described here should be sufficient for calculating the interest rates or accrued interest for the majority of investment situations for which one will need to calculate interest earnings.