Using the Goal Seek Method

You can use the goalSeek() method in order to find a value for one cell that produces the desired formula result in another cell as shown in the code snippet shared below.

JavaScript	Copy Code
// This sample shows how to use the goal seek. // Loan amount is 10000, term is 18 months and pay 600 each month, evaluate what interest rate you will need to secure in order to meet your loan goal. sheet.setValue(0, 1, 10000); // Loan Amount sheet.setValue(1, 1, 18); // Term in Months sheet.setFormatter(2, 1, "0%"); // Interest Rate sheet.setFormatter(3, 1, "0.00"); sheet.setFormula(3, 1, "PMT(B3/12,B2,B1)"); // Payment GC.Spread.Sheets.CalcEngine.goalSeek(sheet, 2, 1, sheet, 3, 1, -600); // Result in B3 is 10%

JavaScript

Copy Code

// This sample shows how to use the goal seek.
// Loan amount is 10000, term is 18 months and pay 600 each month, evaluate what interest rate you will need to secure in order to meet your loan goal.

sheet.setValue(0, 1, 10000); // Loan Amount
sheet.setValue(1, 1, 18); // Term in Months
sheet.setFormatter(2, 1, "0%"); // Interest Rate
sheet.setFormatter(3, 1, "0.00");
sheet.setFormula(3, 1, "PMT(B3/12,B2,B1)"); // Payment
GC.Spread.Sheets.CalcEngine.goalSeek(sheet, 2, 1, sheet, 3, 1, -600); // Result in B3 is 10%

Product Support Forum | Print this page