How To Use INTRATE Function in Google Sheets

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?

With the 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 INTRATE function. 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. 

Using the INTRATE Function in Google Sheets to get the interest rate of several investment options

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:

=INTRATE(B2,C2,D2,E2)

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

  1. 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.
    A historical price dataset we'll use the INTRATE function on

  2. To start using the INTRATE function, select the cell we will put our function’s first output. In this case, we’ll start with cell F1.
  3. For the next step, we just need to type in the equal sign ‘=’ followed by ‘INTRATE(’ to specify the function we’ll be using. 
  4. A tooltip box may appear with information on how to use the INTRATE function. Clicking the arrow on the top-right-hand corner of the box will minimize this if necessary.
    Typing INTRATE Function in Google Sheets into the formula bar

  5. 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.
    Adding the arguments to INTRATE Function in Google Sheets

  6. 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.
    Returning the effective interest rate of each investment using INTRATE Function in Google Sheets

 

Frequently Asked Questions (FAQ)

  1. Why does my formula return a #NUM! error?
    As seen in the example below, there may be several reasons why the INTRATE function 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.
    INTRATE error due to invalid arguments

     
  2. Why does my formula return a #VALUE! error?
    The INTRATE function 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.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like