RATE function

PRO BASIC

The RATE function in Jspreadsheet Formulas Pro is a useful tool that calculates the interest rate for each period of an annuity. This can be particularly useful if you're trying to work out the recurring interest rate on investments or loans. You input the total number of payment periods, the payment made each period, and the present value of the loan or investment. The function returns the periodic interest rate that matches the given loan or investment parameters.

Documentation

Returns the interest rate per period of an annuity.

Syntax

RATE(nper, pmt, pv, [fv], [type], [guess])

Parameter	Description
`nper`	The total number of payment periods.
`pmt`	The amount paid each period, such as a loan or lease payment.
`pv`	The present value, or the total amount that a series of future payments is worth now.
`[fv]`	Optional. The future value, or a cash balance you want to attain after the last payment is made. If omitted, assumed to be 0.
`[type]`	Optional. When payments are due: 0 for end of period (default), or 1 for beginning of period.
`[guess]`	Optional. Your guess for the rate, used as a starting point for the iterative calculation. If omitted, the default guess is 0.1 (10%).

Behavior

The RATE function is used to calculate the interest rate per period of an annuity. It requires three basic inputs: the total number of payment periods in an annuity, the payment made each period, and the present value.

Empty cells: If any of the required inputs are left empty or missing, the RATE function would return a #NUM! error.
Text: If the function encounters text where it expects a number, it returns a #VALUE! error.
Booleans: Boolean values are treated as numbers in the RATE function, with TRUE being equivalent to 1 and FALSE equivalent to 0. However, it is not common to use Boolean values in financial calculations.
Errors: If any of the inputs to the RATE function are other formulas that result in an error, the RATE function will also return an error.

Common Errors

Error	Description
`#NUM!`	This error occurs when the `RATE` function fails to find a result after 20 iterations. This can occur if any of the inputs are non-numeric or if the calculation is not converging to a solution.
`#VALUE!`	This error occurs when one or more of the arguments passed to the `RATE` function are text that cannot be interpreted as numbers.

Best practices

Always ensure that the input values to the RATE function are numeric. The function does not handle text well and will return an error.

Be aware that the RATE function assumes that payments are made at the end of each period. If payments are made at the beginning of each period, you will need to adjust your calculations appropriately.

If the RATE function returns a #NUM! error, consider using the optional guess parameter to provide a starting point for the iteration process.

To improve readability and maintainability of your spreadsheet, consider breaking out complex formulas that include the RATE function into multiple cells or steps.

Usage

A few examples using the RATE function.

RATE(12, -100, 1000)
// Calculates the monthly interest rate for 12 periods, with a payment of -100 each period, and a present value of 1000.

RATE(12, -100, 1000, 0, 1)
// Same as above, but assumes payments are made at the beginning of each period.

RATE(24, -200, 5000, 1000)
// Calculates the interest rate for 24 periods, with -200 payments, a present value of 5000, and a future value of 1000.

RATE(36, -300, 8000, 0, 0, 0.05)
// Uses 5% as the initial guess to help the function converge on the solution.

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('MmUwNjJlNDllMWZjNGU0ODQ4ZTVjNGYyODRhZjRmNWM5ZTM5ZDI5YWFhZjcxZjhmYmNmMzlmNGU4ZTEwNzg4MjYyZmIzMTdiZWNiYzgwMWQxOTk0ZTQyZDljYWUwNmYxMDZiOTViMzU2ZTY2MDQ2YjE2N2ZjYTg4MTMwZDUyODEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Periods",
        "Payment",
        "Present Value",
        "Interest Rate"
    ],
    [
        12,
        -100,
        1000,
        "=RATE(A2,B2,C2)"
    ],
    [
        24,
        -250,
        5000,
        "=RATE(A3,B3,C3)"
    ],
    [
        36,
        -180,
        6000,
        "=RATE(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('MmUwNjJlNDllMWZjNGU0ODQ4ZTVjNGYyODRhZjRmNWM5ZTM5ZDI5YWFhZjcxZjhmYmNmMzlmNGU4ZTEwNzg4MjYyZmIzMTdiZWNiYzgwMWQxOTk0ZTQyZDljYWUwNmYxMDZiOTViMzU2ZTY2MDQ2YjE2N2ZjYTg4MTMwZDUyODEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Periods",
        "Payment",
        "Present Value",
        "Interest Rate"
    ],
    [
        12,
        -100,
        1000,
        "=RATE(A2,B2,C2)"
    ],
    [
        24,
        -250,
        5000,
        "=RATE(A3,B3,C3)"
    ],
    [
        36,
        -180,
        6000,
        "=RATE(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('MmUwNjJlNDllMWZjNGU0ODQ4ZTVjNGYyODRhZjRmNWM5ZTM5ZDI5YWFhZjcxZjhmYmNmMzlmNGU4ZTEwNzg4MjYyZmIzMTdiZWNiYzgwMWQxOTk0ZTQyZDljYWUwNmYxMDZiOTViMzU2ZTY2MDQ2YjE2N2ZjYTg4MTMwZDUyODEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Periods",
        "Payment",
        "Present Value",
        "Interest Rate"
    ],
    [
        12,
        -100,
        1000,
        "=RATE(A2,B2,C2)"
    ],
    [
        24,
        -250,
        5000,
        "=RATE(A3,B3,C3)"
    ],
    [
        36,
        -180,
        6000,
        "=RATE(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('MmUwNjJlNDllMWZjNGU0ODQ4ZTVjNGYyODRhZjRmNWM5ZTM5ZDI5YWFhZjcxZjhmYmNmMzlmNGU4ZTEwNzg4MjYyZmIzMTdiZWNiYzgwMWQxOTk0ZTQyZDljYWUwNmYxMDZiOTViMzU2ZTY2MDQ2YjE2N2ZjYTg4MTMwZDUyODEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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",
        "Payment",
        "Present Value",
        "Interest Rate"
    ],
    [
        12,
        -100,
        1000,
        "=RATE(A2,B2,C2)"
    ],
    [
        24,
        -250,
        5000,
        "=RATE(A3,B3,C3)"
    ],
    [
        36,
        -180,
        6000,
        "=RATE(A4,B4,C4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub