The INTRATE function in Google Sheets is a powerful tool for financial analysis, specifically designed to calculate the effective interest rate of an investment. This function is particularly useful for investors looking to evaluate the performance of their investments through price changes rather than relying on interest or dividends.

Syntax

INTRATE(settlement, maturity, investment, redemption)

• settlement: The date when the investment was purchased.
• maturity: The date when the investment will mature or be sold.
• investment: The amount paid for the investment at the time of purchase.
• redemption: The amount received from selling the investment at maturity.

Example #1

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

This function calculates the interest rate when you buy an investment for $1,000 and sell it for $1,200 after one year. The result is an effective interest rate of 20%.

Example #2

=INTRATE("2023-05-01", "2023-11-01", 500, 550)

In this case, the investment was purchased for $500 and sold for $550 six months later. The function will return an effective interest rate of approximately 20%.

Example #3

=INTRATE("2022-12-01", "2023-12-01", 1500, 1800)

Here, an investment of $1,500 is sold for $1,800 a year later, resulting in an effective interest rate of 20%.

Error handling

• NUM!: This error occurs if the function’s parameters lead to an undefined interest rate, often due to invalid dates or values.
• VALUE!: This error indicates that one or more of the input parameters are not recognized as valid data types, such as text instead of numbers.
• REF!: This error appears when any of the date references are invalid or out of scope for the function context.

Conclusion

The INTRATE function is an invaluable asset for financial planning and investment analysis, helping investors quickly assess their returns without the complicating factors of interest or dividends. Its straightforward syntax and robust functionality make it an essential tool in any financial analyst’s toolkit.