Calculate the interest rate needed to reach your investment goal

 

by Kara Hiltz

Application: Microsoft Excel 2013/2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/office/201603/rri.zip

When you’re deciding how to invest money, you often have several options and many variables to consider, such as the amount you invest, the number of periods you will allow the investment to draw interest, the goal for your final investment value, and the interest rate. Excel’s RRI function can tell you what interest rate you’ll need in order to reach a specific value given the number of investment periods and the amount you invest.

To calculate interest rates for three portfolio options with the RRI function, we’ll:

  • Learn more about the RRI function and how it works.
  • Determine the variables that will feed into our RRI function: the investment amount, the number of investment periods, and the amount you want to reach with your investment.
  • Explore a variety of scenarios using the RRI function to determine the interest rate needed for each scenario.

 

Investing in your financial future requires research and critical thinking as you compare your options. There are usually several investment options available, all with different variables. One company may offer a 10-year investment option with a higher interest rate than a competing company that offers a 15-year investment option at a slightly lower rate. Which option will get you to your investment goal? In many cases, the interest rate makes all the difference.

Excel 2013 and 2016 offer a relatively new financial function—the RRI function—that can help you determine what interest rate you’ll need to reach your investment goal., as shown in Figure A. In this article, we’ll explain the RRI function and demonstrate how it can help you make an educated choice when it comes to investing your hard-earned money.

IME18502A

A:

The RRI function calculates the minimum interest rate needed to reach your investment goal.

An overview of the RRI function

The RRI function uses three arguments to calculate the interest rate needed to grow your investment and reach your goal value. You may recognize these arguments from similar financial functions: pv, fv, and nper. Here’s a quick synopsis of these three arguments:

  • nper: The nper argument represents the number of periods for which your investment will gain interest. These periods can be months, quarters, years, or any other applicable period; they just need to be consistent. The nper argument is the whole number representing the number of periods, regardless of how long those periods are.
  • pv: The pv argument is the initial amount of money invested. This argument cannot be zero, or Excel will return a #NUM! error for your IRR function.
  • fv: The fv argument represents your goal value, or the amount of money you want to reach by the end of your investment. If this argument is less than your pv argument, signaling that your goal is to lose money instead of gain money, the IRR function will return a negative value.

Excel uses a specific formula to calculate the interest rate value using these three arguments, as shown in Figure B.

IME18502B

 B:

The RRI function uses a simple equation in order to return the minimum interest rate needed to reach an investment goal.

Set up a few possible scenarios

Now that you know a bit more about the RRI function, let’s look at the RRI function in action. In our example, we’ve set up three possible portfolios, each with a different number of investment periods (nper) but the same investment amounts (pv) and goal values (fv).

Download: Follow along with our example by downloading and extracting the file portfoliocomparisonchart.xls from the download link given at the beginning of this arti[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage