The INTRATE function in Excel is used to determine the interest rate on a fully invested security. This function is especially useful for investors and financial professionals who need to calculate the yield of securities based on actual investment dates and cash flows. By accurately assessing the rate of return, users can make more informed investment decisions.

Syntax

INTRATE(settlement, maturity, investment, redemption, [basis])

• settlement: The date when the security is purchased.
• maturity: The date when the security matures.
• investment: The amount invested in the security.
• redemption: The amount returned when the security matures.
• [basis]: (Optional) The type of day count basis to use.

Example #1

=INTRATE("2023-01-01", "2024-01-01", 1000, 1100)

This function calculates the interest rate for a security purchased on January 1, 2023, maturing on January 1, 2024, with an investment of $1,000 and a redemption value of $1,100. The result would be approximately 10%.

Example #2

=INTRATE("2023-06-01", "2023-12-01", 2000, 2500)

This function evaluates a security bought on June 1, 2023, maturing on December 1, 2023, with a $2,000 investment and a redemption of $2,500. The calculated interest rate would be approximately 25%.

Example #3

=INTRATE("2023-03-15", "2023-06-15", 1500, 1600, 1)

This formula assesses a security purchased on March 15, 2023, that matures on June 15, 2023. The amount invested was $1,500 with a redemption value of $1,600. The interest rate returned would be around 6.67%, using the actual/actual basis for the calculation.

Error handling

• VALUE! – This error occurs if any of the input arguments are invalid, such as non-date values for settlement or maturity.
• NUM! – This indicates a problem with the calculated interest rate, typically occurring if the investment exceeds the redemption value.
• NAME? – This error arises if Excel cannot recognize the function name, usually due to typing errors.

Conclusion

In summary, the INTRATE function is a powerful tool in Excel for determining the interest rate of fully invested securities. By utilizing the correct parameters, users can gain insights into their investments, enabling smarter financial decision-making. Understanding this function is essential for anyone involved in financial analysis or investment management.