The INTRATE function in Google Sheets is useful when you need to calculate the effective interest rate of an investment.
This function requires only the buy price and sell price and assumes that no interest or dividends come from the investment itself.
The rules for using the INTRATE function in Google Sheets are as follows:
- The function requires the date and price of the given investment at the time of purchase and at the time of sale.
- The function then outputs the effective interest rate.
- There is also an optional parameter in case you would like to select the day count method to use.
Let’s begin with a quick use-case.
Let’s say we have three investment options (A, B, and C) and would like to know which investment is best for your portfolio. You have access to historical data and know the performance of each investment in the year 2020. How would we know the interest rate of each investment?
INTRATE function it becomes quite easy to solve this. We just need to specify a buy date and sell date that’s the same for all investments, as well as the buy price and sell price of each investment on those dates.
The previous example is one of many ways we can apply the
INTRATE function in Google Sheets. This function can be used for any sort of financial asset that earns interest over time, such as bonds and securities.
Let’s dive into how to write the
INTRATE function in Google Sheets and later test out the function with sample prices and dates.
The Anatomy of the INTRATE Function
So the syntax (the way we write) of the
INTRATE function is as follows:
=INTRATE(buy_date, sell_date, buy_price, sell_price, [day_count_convention])
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is how we start any function in Google Sheets.
- INTRATE() is our
INTRATEfunction. It computes the effective interest rate of an investment
- buy_date refers to the date the investment was purchased.
- sell_date refers to the date the investment was sold.
- buy_price refers to the price of the investment at the time of purchase.
- sell_price refers to the price of the investment when it was sold.
- day_count_convention is an optional argument that indicates which day count method to use. By default, Google Sheets uses the US (NASD) 30/360 convention.
A Real Example of Using INTRATE Function
Let’s look into an example of the
INTRATE function being used in a Google Sheet spreadsheet.
In the table below, we have 5 different investments, all with the same Buy Date and Sell Date. Historical data allows us to get the buy price and sell price of each investment on those two dates. From just this information, we’re able to get the effective interest rate of each investment.
In this set, we can see that Investment C has historically had a higher return than the other four options.
To get the values in Column F, we just need to use the following formula:
You may create your own copy of the spreadsheet above using the link I have attached below.
Now that you’ve seen a real example of the
INTRATE function in Google Sheets, let’s begin writing it together!
How to Use INTRATE Function in Google Sheets
- First, we should make sure that all the necessary data is provided. In the table below, the data can be found in Columns B through E.
- To start using the
INTRATEfunction, select the cell we will put our function’s first output. In this case, we’ll start with cell F1.
- For the next step, we just need to type in the equal sign ‘=’ followed by ‘INTRATE(’ to specify the function we’ll be using.
- A tooltip box may appear with information on how to use the
INTRATEfunction. Clicking the arrow on the top-right-hand corner of the box will minimize this if necessary.
- Next, we must input the four arguments needed to make the calculation. Afterward, simply hit the Enter key on your keyboard to return the final result.
- We can see that the formula works as intended. Dragging down the formula in cell F1 will help us fill out the rest of the column.
Frequently Asked Questions (FAQ)
- Why does my formula return a #NUM! error?
As seen in the example below, there may be several reasons why the
INTRATEfunction in Google Sheets returns this error. If the buy price or sell price is lower than or equal to 0, then the function will return this error. If the sell date is equal to or less than the buy date, then the same error will be raised.
- Why does my formula return a #VALUE! error?
INTRATEfunction may return this type of error if any of the arguments are non-numeric. It is also possible that the dates provided are not valid dates.
This step-by-step tutorial just shows how easy it is to compute the interest rate of a given investment with the
INTRATE function in Google Sheets. You can now use the
INTRATE functions in Google Sheets together with the various other Google Sheets formulas available to create great worksheets that work for you.
Do subscribe to our newsletter to be the first to receive the latest Google Sheets guides and tutorials from us.