Syntax. INTRATE(Settlement,Maturity,Investment,Repayment,Basis)
Definition. This function calculates the equivalent interest rate in arrears for a security that has received a markdown (discount, disagio) for an intra-annual duration. The only difference between this function and YIELDDISC() is the naming of the arguments.
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The day when the payments from the security must take place.
Investment (optional) The purchase price of the security at the day of settlement.
Redemption (required) The payment at the day of maturity.
Basis (optional) The method you want to use for determining the days in the year according to Table 15-2, shown earlier. If this argument is omitted, Excel calculates with Basis = 0.
All function arguments that take a date use the date without time; fractions are rounded. The argument Basis also requires an integer, and decimal places are truncated.
Take note of the following:
If invalid dates are used or no numbers are entered where required, the function returns the error
#VALUE!.If Investment is less than or equal to zero, or Redemption is less than or equal to zero, RECEIVED() returns the
#NUMBER!error.If Basis is less than zero or greater than 4, RECEIVED() returns the error
#NUMBER!.If Settlement is greater than or equal to Maturity, RECEIVED() returns the error
#NUMBER!.
Important
Excel Help contains the following note:
“Dates should be entered with the DATE() function or as results of other formulas or functions. Problems might occur if dates are entered as text.”
Background. In principle, this function belongs to the group of anticipative yield. The principle is based on the assumption that a seed capital amount (deposit amount, loan) results from the repayment amount minus the interest on this amount. This principle is mainly used in the intra-annual scope and differs from the principle based on a savings account or mortgage loan. In this case, interest is calculated at the end of a period based on the seed capital (interest yield in arrears).
To allow you to compare securities with interest payment in arrears, the difference between repayment and price must be put in relation to the price. The result corresponds to the relative increase until maturity (an interest rate in arrears), which, as is done frequently in similar situations, receives the name Yield. The calculation is done without compound interest. The interest rate must be extrapolated to one year (with corresponding day counting).
The function DISC() has the following relation to INTRATE():
The relationship permits you to calculate the equivalent anticipative annual interest rate from an annual interest rate in arrears. Equivalence means that two investors who are investing the same amount of capital (one with anticipative interest yield and one with interest yield in arrears) at the end of a year receive the same payment.
Example. You can use the examples for YIELDDISC() here and use the function RECEIVED() for the calculations.