Products

RRI function

PRO BASIC

The RRI function in Jspreadsheet Formulas Pro is used to compute the interest rate of a completely invested security. It requires three inputs: the total number of periods, the starting value, and the final value. Using these, it calculates and returns the equivalent interest rate. This is particularly useful for understanding the rate of return on your investments.

Documentation

Calculates the interest rate of a fully invested security.

Category

Financial

Syntax

RRI(nper, pv, fv)

Parameter Description
nper The total number of payment periods for the investment.
pv The present value, or the lump-sum amount that a series of future payments is worth right now.
fv The future value, or a cash balance you want to attain after the last payment is made.

Behavior

The RRI function is used to calculate the equivalent interest rate for a specified number of periods based on a present value, future value, and total number of periods. Here's how it handles different scenarios:

  • Empty Cells: If any of the arguments are empty cells, the RRI function will return a #VALUE! error.
  • Text: If any of the arguments are text that cannot be converted into a number, the RRI function will return a #VALUE! error.
  • Booleans: If any of the arguments are boolean values, the RRI function will treat them as numbers (TRUE as 1 and FALSE as 0).
  • Errors: If any of the arguments are error values, the RRI function will return that error value.
  • Negative Values: The RRI function can handle negative values. However, the present value and the future value should not be of the same sign. If they are, the function will return a #NUM! error.

Common Errors

Error Description
#VALUE! This error will be returned if any of the arguments are non-numeric values that cannot be converted into numbers, or if any of the arguments are empty cells.
#NUM! This error will be returned if the present value and the future value are of the same sign, or if the number of periods is less than or equal to zero.
#DIV/0! This error will be returned if the present value is zero.

Best practices

  • Ensure that all inputs to the RRI function are numeric. If there's a chance an input might be text, consider using a function like 'IFERROR' to handle this scenario.
  • Be aware that RRI cannot handle scenarios where the present value and future value have the same sign. In real-world terms, this means that you can't use RRI to calculate the interest rate if the value of an investment doesn't change or if it moves in the same direction as the initial investment.
  • Keep in mind that the RRI function returns the interest rate per period. If you want to find the annual interest rate, you'll need to adjust the result accordingly.
  • Always cross-verify the results as the function may not yield accurate results for non-periodic or continuous compounding interest scenarios.

Usage

A few examples using the RRI function.

RRI(12, 1000, 2000) → 0.0595 (approx. 5.95% per period)
RRI(36, 10000, 15000) → 0.0113 (approx. 1.13% per period)
RRI(24, 5000, 10000) → 0.0293 (approx. 2.93% per period)
RRI(10, 1000, 2000) → 0.0717 (approx. 7.17% per period)

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YmE2ZTI5M2U1MTMwNTc1ZjNmMmNiNmRmZGI5MzljMWM3MGE5NGY5MmJjMDA0ZmJlYTJhZTQyOTUyZTUyYWVkNWE5NDE3NWI2YzVhNWViNzljZWE0ODZjNTMzNjA1Yjk5NzBmYjA2YjRiOTA1MjQ5NzMyOTg2NjA3YTdkYmEyYjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGsyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Periods",
        "Present Value",
        "Future Value",
        "Interest Rate"
    ],
    [
        12,
        -1000,
        2000,
        "=RRI(A2,B2,C2)"
    ],
    [
        36,
        -10000,
        15000,
        "=RRI(A3,B3,C3)"
    ],
    [
        24,
        -5000,
        10000,
        "=RRI(A4,B4,C4)"
    ]
]
  }]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('YmE2ZTI5M2U1MTMwNTc1ZjNmMmNiNmRmZGI5MzljMWM3MGE5NGY5MmJjMDA0ZmJlYTJhZTQyOTUyZTUyYWVkNWE5NDE3NWI2YzVhNWViNzljZWE0ODZjNTMzNjA1Yjk5NzBmYjA2YjRiOTA1MjQ5NzMyOTg2NjA3YTdkYmEyYjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGsyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Periods",
        "Present Value",
        "Future Value",
        "Interest Rate"
    ],
    [
        12,
        -1000,
        2000,
        "=RRI(A2,B2,C2)"
    ],
    [
        36,
        -10000,
        15000,
        "=RRI(A3,B3,C3)"
    ],
    [
        24,
        -5000,
        10000,
        "=RRI(A4,B4,C4)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}
<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('YmE2ZTI5M2U1MTMwNTc1ZjNmMmNiNmRmZGI5MzljMWM3MGE5NGY5MmJjMDA0ZmJlYTJhZTQyOTUyZTUyYWVkNWE5NDE3NWI2YzVhNWViNzljZWE0ODZjNTMzNjA1Yjk5NzBmYjA2YjRiOTA1MjQ5NzMyOTg2NjA3YTdkYmEyYjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGsyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Periods",
        "Present Value",
        "Future Value",
        "Interest Rate"
    ],
    [
        12,
        -1000,
        2000,
        "=RRI(A2,B2,C2)"
    ],
    [
        36,
        -10000,
        15000,
        "=RRI(A3,B3,C3)"
    ],
    [
        24,
        -5000,
        10000,
        "=RRI(A4,B4,C4)"
    ]
]

        return {
            data
        };
    }
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YmE2ZTI5M2U1MTMwNTc1ZjNmMmNiNmRmZGI5MzljMWM3MGE5NGY5MmJjMDA0ZmJlYTJhZTQyOTUyZTUyYWVkNWE5NDE3NWI2YzVhNWViNzljZWE0ODZjNTMzNjA1Yjk5NzBmYjA2YjRiOTA1MjQ5NzMyOTg2NjA3YTdkYmEyYjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGsyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Periods",
        "Present Value",
        "Future Value",
        "Interest Rate"
    ],
    [
        12,
        -1000,
        2000,
        "=RRI(A2,B2,C2)"
    ],
    [
        36,
        -10000,
        15000,
        "=RRI(A3,B3,C3)"
    ],
    [
        24,
        -5000,
        10000,
        "=RRI(A4,B4,C4)"
    ]
]
            }]
        });
    }
}