Note: The other languages of the website are Google-translated. Back to English

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.

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations