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 useRRI
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)"
]
]
}]
});
}
}