Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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)


Excel Productivity Tools

300 Advanced Features Help 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 70% time.

  • 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.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.