01Oct2014

How to calculate accrued interest using Microsoft Excel?

ACCRINT

To calculate accrued interest, Excel has a function called ACCRINT. Using this function, we can calculate accrued interest for a security paying periodic interest. Go ahead and read through to know how and when the function can be used.

ACCRINT function in Excel returns the accrued interest for a security that pays interest on a periodic basis. To calculate accrued interest, first let us know the syntax of the ACCRINT function

Syntax of ACCRINT function is =ACCRINT(issue date, first interest date, settlement date, rate, par, frequency, [basis], [calculation method])

Note: The parameters that are mentioned in square brackets [] are optional

ACCRINT function in excel

In the above example:

  • Issue date is in cell B3.
  • First Interest date is the Interest payment date to the investors (cell B4)
  • Settlement date is the purchase date (Cell B5)
  • Annual rate is the rate at which the investor gets the interest amount (cell B6)
  • Par value is the face value of the investment (Cell B7)
  • Frequency is the interval between two consecutive payments (Quarterly, Semi Annual, and Annual). (Cell B8)
  • Basis is optional, it’s the type of day count we use when calculating interest for the security. The default value is zero if not mentioned (cell B9)
  • Calculation method is TRUE if calculating from issue date to settlement date and FALSE if calculating from settlement date to the first interest date (cell B10).

As per the example, we get $140 as Accrued interest for a quarter.

Note that the Issue date is the date when it is first issued. It is not the date when it was purchased.

Hope you are now comfortable with using ACCRINT function to calculate accrued interest in excel. If you are still facing issues or have queries, feel free to contact us

  • 1 Oct, 2014
  • Excel for Commerce
  • 0 Comments
  • accrint, accrued interest,

Categories

Comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>