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.
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:
|0 or omitted||US (NASD) 30/360|
The INTRATE function will return the interest rate for a fully invested security.
(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.
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)