Skip to main content

Excel INTRATE Function

Supposing you spent $86.3 to purchase a bond today, and this bond’s face value is $100 and will expire in 2 years, how could you calculate its discount rate in Excel? Here, you can apply the INTRASTE function to solve this problem easily.
The INTRATE function can figure out the interest rate for a full invested security in Excel.  

Function syntax and arguments

INTRATE(settlement, maturity, investment, redemption, [basis])

(1) Settlement: Required. It’s the security’s settlement date that the buyer purchased the security. It’s later than the issue date.

(2) Maturity: Required. It’s the security’ maturity date when the security expires.

(3) Investment: Required. It’s the total amount invested in the security.

(4) Redemption: Required. It’s the total amount you can get back from the security at the maturity.

(5) Basis: Optional. It’s the type of day count basis to use. There are 5 types:

Values Description
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Return Value

Numerical value.

The INTRATE function will return the interest rate for a fully invested security.

Usage notes

(1) The INTRATE function returns #VALUE! error value, if the settlement or maturity are not valid dates.

(2) The INTRATE function returns #NUM! error value, if the investment or the redemption is less than or equal to 0.

(3) The INTRATE function returns #NUM! error value, if the settlement date is no less than the maturity date.

(4) The INTRATE function returns #NUM! error value, if the basis is less than 0 or greater than 4.

(5) The basis will be truncated to integer. For example, you type 1.4 as the basis value, the INTRATE function will take it as 1 automatically. 

Formula Example: Calculate the discount rate of a bond or coupon in Excel

Supposing there is a coupon issued on 1/1/2000, it will expire on 1/1/2020, and its face value is $100. Then you spent $88.3 to purchase it on 7/1/2017. Now you can apply the INSTRATE function to figure out the discount rate of this coupon easily.
Formula:or

In this example, the settlement date is 7/1/2017 in Cell C4, the maturity date is 1/1/2020 in Cell C5, and the investment value is $88.3, the investment value is $100. Therefore, you can apply one of below formulas to figure out the discount rate of this coupon easily.

=INTRATE(C4,C5,C6,C7,C8)

=INTRATE("7/1/2017","1/1/2020",88.3, 100, 1)


The Best Office Productivity Tools


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations