Skip to main content

Excel INTRATE Function

Author: Kelly Last Modified: 2019-05-30

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.

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("7/1/2017","1/1/2020",88.3, 100, 1)

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


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