I Cant Use =RATE Function, Is My Syntax Wrong?

[HzdAngga]

Im trying to do =RATE syntax with this code:

import { HyperFormula } from 'hyperformula'

const options = {
    licenseKey: 'gpl-v3',
  }

  // define the data
  const data = [['=RATE(180; 10000000; 500000000)']]

  // build an instance with defined options and data
  const hfInstance = HyperFormula.buildFromArray(data, options)

  // call getCellValue to get the calculation results
  const result = hfInstance.getCellValue({ col: 0, row: 0, sheet: 0 })
  
  // result
  console.log(result)

and the result will be:

i tried in google sheets and it works fine tho:

any ideas?

[AMBudnik]

Hi @HzdAngga

First of all, you need to separate the parameters with commas and when I follow the patterns:

RATE(nper, pmt, pv)
nper: Required. The total number of payment periods in the loan or annuity.
pmt: Required. The payment made each period, which must remain constant throughout the life of the loan or annuity.
pv: Required. The present value of the loan or annuity, which is the total amount of the loan or investment.

it works well, Here's a working demo: https://stackblitz.com/edit/js-pkthpa?file=index.html,index.js

This is also mentioned in the Microsoft tutorial.

But now there is a limitation (To be confirmed with @sequba) that '=RATE(180, -10000000, 500000000)' (corrected syntax) exceeds the possible numeric value for the second parameter. If we drop one zero the error does not occur.

I found that

If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.

and this is the issue that we get if we use -10000000 as a 2nd parameter.

[HzdAngga]

Hi @HzdAngga

First of all, you need to separate the parameters with commas and when I follow the patterns:

RATE(nper, pmt, pv) nper: Required. The total number of payment periods in the loan or annuity. pmt: Required. The payment made each period, which must remain constant throughout the life of the loan or annuity. pv: Required. The present value of the loan or annuity, which is the total amount of the loan or investment.

it works well, Here's a working demo: https://stackblitz.com/edit/js-pkthpa?file=index.html,index.js

This is also mentioned in the Microsoft tutorial.

But now there is a limitation (To be confirmed with @sequba) that '=RATE(180, -10000000, 500000000)' (corrected syntax) exceeds the possible numeric value for the second parameter. If we drop one zero the error does not occur.

I found that

If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.

and this is the issue that we get if we use -10000000 as a 2nd parameter.

ah i see, thx for the reply @AMBudnik

[sequba]

I found that

If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
and this is the issue that we get if we use -10000000 as a 2nd parameter.

This is exactly right. #NUM error indicates that the algorithm doesn't converge for the provided set of parameters due to the numerical limitations.