The Excel Intrate function calculates the interest rate for a fully invested security.
The syntax of the function is:
where the arguments are as follows:
|settlement||-||The security's settlement date (i.e. the date that the coupon is purchased).|
|maturity||-||The security's maturity date (i.e. the date that the coupon expires).|
|investment||-||The initial amount invested into the security.|
|redemption||-||The amount to be received at maturity.|
Warning: Microsoft advises that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the settlement and maturity dates should be input to the Intrate function as either:
In the spreadsheet below, the Excel Intrate function is used to calculate the interest rate of an investment of $1,000, which was used to purchase a security on 01-Apr-2005. The security matured on 31-Mar-2010, with a redemption value of $2,125 and the US (NASD) 30/360 day count basis is used:
|1||settlement date:||01-Apr-2005||Interest rate of a security with value $1,000,|
invested on 01-Apr-2005, which matures on
31-Mar-2010, with a redemption value of $2,125.
The US (NASD) 30/360 day count basis is used:
|2||maturity date:||31-Mar-2010||=INTRATE( B1, B2, 1000, 2125 )|
The formula in cell D2 of the above spreadsheet returns the value 0.225, or 22.5%.
Note that, in the above example:
Further details and examples of the Excel Intrate function are provided on the Microsoft Office website.
If you get an error from the Excel Intrate function, this is likely to be one of the following: