// Create a new workbook Workbook workbook = new Workbook(); //Set MaximumIterations and MaximumChange workbook.getOptions().getFormulas().setMaximumIterations(1000); workbook.getOptions().getFormulas().setMaximumChange(0.000001); IWorksheet activeSheet = workbook.getActiveSheet(); activeSheet.getRange("A1:A4").setValue(new String[]{"Loan Amount", "Term in Months", "Interest Rate", "Payment"}); // This is the amount that you want to borrow. activeSheet.getRange("B1").setValue(100000); activeSheet.getRange("B1").setNumberFormat("$#,##0"); // This is the number of months that you want to pay off the loan. activeSheet.getRange("B2").setValue(180); // This is the number of interest rate. activeSheet.getRange("B3").setNumberFormat("0.00%"); // This formula calculates the payment amount. activeSheet.getRange("B4").setFormula("=PMT(B3/12,B2,B1)"); activeSheet.getRange("B4").setNumberFormat("$#,##0"); // Use goal seek to calculate the value of cell B3. activeSheet.getRange("B4").goalSeek(-900, activeSheet.getRange("B3")); activeSheet.getRange("A1:B4").autoFit(); // Save to an excel file workbook.save("GoalSeek.xlsx");
// Create a new workbook var workbook = Workbook() //Set MaximumIterations and MaximumChange workbook.options.formulas.maximumIterations = 1000 workbook.options.formulas.maximumChange = 0.000001 val activeSheet = workbook.activeSheet activeSheet.getRange("A1:A4").value = arrayOf("Loan Amount", "Term in Months", "Interest Rate", "Payment") // This is the amount that you want to borrow. activeSheet.getRange("B1").value = 100000 activeSheet.getRange("B1").numberFormat = "$#,##0" // This is the number of months that you want to pay off the loan. activeSheet.getRange("B2").value = 180 // This is the number of interest rate activeSheet.getRange("B3").numberFormat = "0.00%" // This formula calculates the payment amount. activeSheet.getRange("B4").formula = "=PMT(B3/12,B2,B1)" activeSheet.getRange("B4").numberFormat = "$#,##0" // Use goal seek to calculate the value of cell B3. activeSheet.getRange("B4").goalSeek(-900.0, activeSheet.getRange("B3")) activeSheet.getRange("A1:B4").autoFit() // Save to an excel file workbook.save("GoalSeek.xlsx")