What is RRI Function in Google Sheets?
RRI stands for Rate of Return on Investment. The RRI function in Google Sheets returns the interest rate that is required by an investment to reach a specific value within a fixed number of periods. This formula determines the annualized growth rate of an investment. It assumes that the growth is steady over the given investment period. It is a very useful function to compare different investment opportunities or to understand how any investment can grow over time. So, you can use it to calculate the growth rate of any savings or for the projection values for future investments. We will run you through all the nitty-gritty of the function throughout this article to help you understand it in detail.
In this simple example below, we have some investment details which include its current value of $2000, the future value of $3600, and the period is five years. We enter the details as shown below in cells B1, B2, and B3. To calculate the interest of the investment using the RRI function, you would use the following formula:
=RRI(B1, B2, B3). Press Enter. The investment would grow at approximately 12.4%
Key Takeaways
- The RRI Function in Google Sheets calculates the annualized rate of return on an investment, over a specified time frame.
- The RRI formula is as follows:
=RRI(number_of_periods, present_value, future_value).
- It helps make key financial investment decisions based on historic performance by showing the potential return of an investment.
- The time period (nper) should be calculated based on whether the interest is compounded monthly, quarterly, semi-annual, or annual.
- The RRI function is often used to calculate the compound annual growth rate (CAGR) of an investment.
Syntax
Now that we have been introduced to how the
rate of return on investment works, let us look at the RRI formula in Google Sheets.
=RRI(number_of_periods, present_value, future_value)
- number_of_period- (Mandatory) The number of periods.
- present_value – (Mandatory)The present value of the investment
- future_value – (Mandatory) The future value of the investment.
How To Use RRI Function in Google Sheets?
To understand how to use RRI to calculate the rate of returns on an investment, let us understand how to enter the function and its parameters in Google Sheets. The RRI Google Sheets function can be entered in two ways.
- Enter RRI manually
- Access from the Google Menu bar
Enter RRI Manually
Let us look at how to enter the function manually
We have a $3,000 investment over 5 years and aim to take it to $6,000. Let us calculate the RRI needed to double the investment.
Step 1: Enter all the details in your spreadsheet.
- Number of Periods (nper) – 5
- Present Value (pv) – 3000
- Future Value (fv) – 6000
Step 2: Let us find the rate of return using the RRI function. In cell B4, let us the RRI formula. Here’s the formula you should enter.
=RRI(B1, B2, B3)
Step 3: After entering the formula, press Enter. You will get the investment’s annualized growth rate. This result indicates the constant rate at which your initial amount invested will grow yearly for you to double the value in five years. Is it doable, we have to decide!
Access From the Google Menu Bar
- Choose the cell where you wish to enter the RRI function.
- Go to the “Insert” tab -> “Function” -> “Financial” -> “RRI”
- Enter the required arguments within the parentheses and press Enter to get the result.
Examples
In this section, we will explain the RRI function in Google Sheets and its purpose with some practical scenarios. This will leave you well-equipped to utilize RRI for all your financial planning.
Example #1 – Calculating RRI Value For Day Period
Let’s look into an interesting RRI in Google Sheets example. Imagine you’re planning to buy some expensive jewelry and want to see how much you need to save to reach your goal. Suppose we invest $500 (pv) for 90 days (nper) and it must grow to $750 (fv), what is the RRI for this period at a constant rate.
Step 1: We wish to calculate the RRI for an investment over a period of 90 days. When we use days as the number of periods, we just enter the number of days as the nper argument. Let us enter the following details in a Google Sheet.
Number of Periods in days(nper): 90
Present Value (PV): $500
Future Value (FV): $750
Step 2: Let us use the RRI formula in cell B4.
=RRI(B1, B2, B3)
It calculates the RRI over 90 days based on the initial investment of $500 and a future value of $750.
Press Enter.
Step 3: The result gives the RRI (the daily return rate) required which is approximately 0.0045 meaning that the investment grows at a rate of 0.45% per day. You can then buy your favorite jewelry with this good investment.
Example #2 – Calculating RRI Value For Monthly Period
Like the previous example, this pertains to saving for a dream vacation. We have an initial investment of $5,000 and a future target value of $7,000. The number of periods is 18 months. Let us calculate the rate of return using the RRI function in Google Sheets.
Step 1: Enter the details as shown below.
- Present Value (PV) = $5,000
- Future Value (FV) = $7,000
- Number of Periods (nper) = 18 months
Step 2: Use the RRI formula as shown below. As with the days period, we enter the number of months directly as the nper argument.
=RRI(B3, B1, B2)
Step 3: Press Enter. The formula will calculate the RRI needed to grow the investment from $5,000 to $7,000 over 18 months which will help you take your vacation.
The RRI Value monthly will be approximately 0.018 or 1.89% per month.
Example #3 – Calculating RRI Value For Yearly Period
In this final example, we have to calculate the RRI on an investment using the RRI function. The asset was purchased at the present date with a PV of $100,000.
The holding period is a period of 5 years with annual compounding.
If the asset is sold at the end of the holding period for $160,000 in proceeds, what is the rate of return on the investment?
- Present Value (PV) = $100,000
- Future Value (FV) = $160,000
- Holding Period = 5 Years
Step 1: The first step to calculate the RRI on the investment is to enter the details.
Step 2: The three inputs include the present value, the future value, and the number of compounding periods, we can enter them into the RRI function as shown below.
=RRI(B1, B2, B3). Press Enter.
The equivalent interest rate on the investment comes out to 0.0985 or 9.85%.
Important Things to Note
- The RRI function always assumes a constant annual growth rate. However, this is not practical especially with fluctuating markets.
- You can keep changing the nper, pv, and fv values for analysis of different scenarios to see how they affect your rate of return. It will help you in strategic financial decision-making.
- All the arguments used in the RRI function in Google Sheets must be positive.
- To avoid the #NUM error, the future_value must be greater than 0.
- If future_value is 0, the rate calculated is -1 or -100%.
Frequently Asked Questions (FAQs)
The RRI is beneficial as it helps estimate the growth of an investment over time. Assuming a constant annual growth rate, it helps investors make informed decisions by showcasing potential returns based on historical performance. It also makes it easy to calculate future values without using complex formulas.
Thus, it can be used to compare different investment opportunities We can easily compare the annual growth rates required to reach our set financial goals considering different options like stock or savings accounts.
It can also help project the growth rate for investments in businesses such as startups. The function will help you understand if a business is financially viable.
It can help you plan your retirement as you can estimate the amount you must save to reach your retirement goals.
As we all know, the formula for the RRI function is as follows.
=RRI(nper, pv, fv)
Here, nper is the total number of compounding periods for the investment.
We adjust the nper based on the following:
The RRI function assumes a constant growth over the given periods. For investments that grow at a varying rate over time, we cannot expect the RRI function to provide a correct result. Hence, in such scenarios, we use different methods like calculating the average rate of return if the growth rate changes over time.
Download Template
This article must help understand RRI Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is RRI Function in Google Sheets. We learn its types & how to use it to find the annualized return rate on an investment. You can learn more from the following articles. –
Leave a Reply