Connect with us

Interest Rate [Investment Analysis] Formula Using Excel

Published

on

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.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *




Are you looking for easy accounting tutorial? Established since 2007, centre-potential.ru hosts more than 1300 articles (still growing), and has helped millions accounting student, teacher, junior accountants and small business owners, worldwide.

Trending


Related pages


after tax profit margin formulapercentage completion method income taxfinancial projection meaningamortization of intangiblesjob order costing accountingprofit maximization in financial managementhow to interpret a cash flow statementrecording notes payablewhat is provision for doubtful accountsunder ifrs the balance sheet is referred to as therefresh pivot table in vbacash and cash equivalents example problemsan activity based overhead rate is computed as followsus gaap consolidated financial statementsvertical analysis exampleaccural based accountingaociprocurement credit cardmyob tutorialfasb bad debt expensegross profit method inventorypromissory letter for loanaccrual basis assumptionhow to calculate overheadsadjusting entries accrued expensesoverallocaterecording accrued interestis gleim cpa review goodaccounting journalizing examplesadjusting entries practice problemscpa salary nycpromissory note formatpercentage of completion method ifrsaudit assertions completenesscombined leverage formulaprepaid wages journal entrywhen cpa exam score releaseimpairment ias 36absorption in cost accountingmicromash cpa reviewwhen are product costs expensedmaker of promissory notehow to calculate accumulated depreciationlate payment forgiveness letterunrealized foreign exchangedeferred financing costs fasbstatic budget and flexible budgethybrid method of accounting taxdividend formula accountingdays payable outstanding dpointerest rate swap tax treatmentthe depreciable base for an asset ishow do you compute earnings per sharehow do you calculate contribution margindeferred tax accounting entriesstockholder equity equationis notes payable a debit or creditformula for fixed asset turnovertransposition error examplecost accounting standard costing and variance analysisgoodwill calculation methodaudit representation letter samplewhat is operating leverageincome statement common sizeinternally generated goodwillhow to calculate beginning finished goods inventorycurrent and noncurrent liabilitiesbank reconciliation format in excelfavorable and unfavorable variancesdisclaimer audit report exampleccm methoddeferred rent tax treatmentpengertian forecasting budgetthe formula for computing annual straight-line depreciation ismateriality constraintias 36 impairment of assets example