Microsoft Office Tutorials and References
In Depth Information
Using INTRATE to Back into the Coupon Interest Rate
INTRATE to Back into the Coupon Interest Rate
to Back into the Coupon Interest Rate
If you have a fully invested bond and know what it will pay on maturity,
you can use Excel ’ s INTRATE function to back into the interest rate.
The INTRATE function returns the interest rate for a fully invested secur-
ity. This function takes the following arguments:
settlement — This is the security ’ s settlement date.
maturity — This is the security ’ s maturity date.
investment — This is the amount invested in the security.
redemption — This is the amount to be received at maturity.
basis — This is the type of day count basis to use. You use 0 for U.S.
The settlement, maturity, and basisarguments are truncated to integers. If
settlementor maturityis not a valid date, INTRATE returns a #NUM! error.
If investmentis less than or equal to 0 or if redemptionis less than or equal
to 0, INTRATE returns a #NUM! error. If basisis less than 0 or if basisis
greater than 4, INTRATE returns a #NUM! error. If settlementis greater than
or equal to maturity, INTRATE returns a #NUM! error.
(Redemption value – Investment) / Investment
and multiplies this by
(Number of days in year / Days from settlementto maturity).
In Figure 13.20 , Excel uses INTRATE to back into the interest rate that the
bond is paying.