background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for September 2024 for September 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for September 2024 is Open
Wall Street Prep

RRI Function

Step-by-Step Guide to Understanding the RRI Function in Excel

Last Updated May 14, 2024

Learn Online Now

RRI Function

How to Use RRI Function in Excel

The RRI function is a built-in feature of Excel used to calculate the rate of return, or the equivalent interest rate, on an investment across a stated time period.

The equivalent interest rate is defined as the rate of return earned on an investment, factoring in the effects of compounding.

For instance, if a corporate bond pays interest on a semi-annual basis, the annual equivalent rate (AER) converts the semi-annual rate of return into an annualized growth rate.

Since different investments compound at varying frequencies, an annualized rate of return can be useful for purposes of capital allocation since the investments can be compared on an apples-to-apples basis to determine which option yields the highest returns.

The Excel RRI function is frequently used to calculate an investment’s compound annual growth rate (CAGR), which measures the growth of an investment as if its value had steadily grown at a consistent rate on an annualized basis, including the effects of compounding.

The CAGR calculation is among one of the most practical tools for determining the return on an investment that rises and declines in value over time because the returned rate of return is “smoothed out” across the holding period.

There are other methods to calculate the CAGR of an investment aside from the RRI function such as the RATE function or using the manual formula, which is described below.

CAGR = (Future Value ÷ Present Value) ^ (1 ÷ Number of Periods)  1
  • Present Value (PV) → The beginning value of the investment in the initial period.
  • Future Value (FV) → The ending value of the investment in the final period.
  • Number of Periods (nper) → The total number of compounding periods, i.e. the product of the number of years and the frequency of compounding per year.

Excel RRI Function Formula Syntax

The formula for using the RRI function in Excel is as follows.

=RRI(nper, pv, fv)
  • nper” → The total number of compounding periods for the investment.
  • pv” → The present value (PV) of the investment, i.e. how much the investment is worth initially as of the current date.
  • fv” → The future value (FV) of the investment, i.e. the ending value to which the investment has grown since the present date.

There are only three inputs required in the calculation, but the “nper” input must be adjusted accordingly.

Periodicity Adjustment Formula
Months
  • nper = Number of Years × 12
Quarterly
  • nper = Number of Years × 4
Semi-Annual
  • nper = Number of Years × 2
Annual
  • N/A

RRI Function Calculator — Excel Model Template

We’ll now move on to a modeling exercise, which you can access by filling out the form below.

dl

Excel Template | File Download Form

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Submitting...

Step 1. Investment Assumptions

Suppose we’re tasked with calculating the compound annual growth rate (CAGR) on an investment using the RRI function in Excel.

The fair market value (FMV) of the asset on the present date, i.e. the date on which the asset was purchased, was $100,000.

The holding period of the investment was 10 years in length, with quarterly compounding.

If the asset is sold at the end of the ten year holding period for $220,000 in proceeds, what is the implied CAGR on the investment?

  • Present Value (PV) = $100,000
  • Future Value (FV) = $220,000
  • Compounding Frequency = 4x
  • Holding Period = 5 Years

Step 2. CAGR Calculation Using RRI Function in Excel

The first step to calculate the CAGR on the investment is to adjust the five year holding period to match the periodicity at which interest is compounded.

By multiplying five by four (i.e. the number of years by the quarterly adjustment factor), the total number of compounding periods comes out to 20 periods.

  • Number of Periods (nper) = 5 Years × 4 = 20 Periods

Since the values of all three inputs—the present value, the future value and the number of compounding periods—are now all known, we can enter them into RRI function formula in Excel.

=RRI(E9,E5,E6)

The equivalent interest rate, i.e. the CAGR, on the investment comes out to 4.0%.

  • Equivalent Interest Rate = 4.0%

Excel RRI Function Formula Calculator

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More
Comments
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.