Build a Loan Calculator with Form Controls in a JavaScript Spreadsheet
Tags: webdev, devops, javascript, tutorial
Published: 2023-05-05 19:39:45

---
canonical_url: https://www.grapecity.com/blogs/build-a-loan-calculator-with-form-controls-in-a-javascript-spreadsheet
description: Learn how to build a loan calculator with form controls in a JavaScript spreadsheet. See more from SpreadJS today.
---
Many business applications need to interact with various data sources and present them in creative and intuitive ways, such as dashboards for quick data analysis and help in making better business decisions. Most recently, in the v16 release [SpreadJS](https://www.grapecity.com/spreadjs "https://www.grapecity.com/spreadjs"), our JavaScript spreadsheet provides the new applicable Excel-Like forms to make adding advanced data entry and selections to your worksheets easier.

The form controls can be used to easily reference and interact with cell data, such as adding controls to chart sheets. Excel provides several controls for dialog sheets that are useful for selecting items from a list.

In this blog, we create a fully functional Loan Calculator using the SpreadJS [Form Controls](https://www.grapecity.com/spreadjs/docs/features/form-controls) to add several different types of form controls within the JavaScript Spreadsheet.

*   [Getting Started](#start)
*   [Loan Amount](#amount)
*   [Loan Term](#term)
*   [Interest Rate](#rate)
*   [Down Payment](#down)
*   [Loan Calculations](#calculations)

### <a id="start"></a>Getting Started

The form controls of SpreadJS can be used to add advanced data entry and selections to your worksheets, making it easier to reference and interact with cell data. With the v16 release of SpreadJS, some exciting Excel-Like form controls are waiting for you to try.

In this sample, we will guide how to create a functional loan calculator using these new imperative features. You will see how user-friendly form controls can be organized by styling and positioning them on different areas of the worksheet.

So let's get started!

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/hyycskt2/01-form.png?width=500&height=245.22292993630572)

Download the example file [here](https://files.grapecity.com/gc-website-media/nd3ps1c0/loancalculator_download.zip) and follow along.

### <a id="amount"></a>Loan Amount

The [Spin Button](https://www.grapecity.com/spreadjs/api/interfaces/GC.Spread.Sheets.Shapes.ISpinButtonFormControlOptions) control can be great for specifying the loan amount. It allows you to increase or decrease a value easily, and by a predetermined amount.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/4sdlnqbe/02-form.png?width=472&height=162)

In SpreadJS, you can add the _spinButton_ form control to the worksheet by passing the _spinButton_ of the _FormControlType_ enumeration as a parameter in the _addFormControl_ method, as shown below:

```
    //add spinButton
    let spinButton = sheet.shapes.addFormControl("spin button", GC.Spread.Sheets.Shapes.
    FormControlType.spinButton 50, 50, 160, 100);
```

You can also set _spinButton_ options using the _options_ method and assign value.   

```
 //set spinButton options
    var options = spinButton.options();
    options.minValue = 100000;
    options.maxValue = 3000000;
    options.step = 100;
    options.cellLink = "'Loan Calculations'!C4";
    spinButton.options(options);
```

Another way to add this control to your worksheet is by using the [Designer](https://www.grapecity.com/spreadjs/designer/index.html) component that SpreadJS offers.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/pe5aobh4/03-form.png?width=500&height=131.28205128205127)

In the Insert tab, click on the Controls panel and choose the ‘Spin Button’ control type. You can define _spinButton_ options by using the Format Shape dialog box.

Control options give you the capability to define the spin button options and assign values as below:

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/df0lktjk/04-form.png?width=417&height=487)

### <a id="term"></a>Loan Term

To help you determine the loan term, a possible option can be using the [Combo Box](https://www.grapecity.com/spreadjs/api/interfaces/GC.Spread.Sheets.Shapes.IComboBoxFormControlOptions) control. Combo Boxes are drop-down elements that allow the selection of any value from the values listed in the drop-down list.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/vx5l3d0n/05-form.png?width=367&height=142)

To make our calculator suitable for both short-term and long-term loans, we have listed 1 to 25-year periods for you to specify your loan time frame.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/jlgjwve2/06-form.png?width=98.75444839857651&height=500)

You can add in your worksheet the _comboBox_ option of the _FormControlType_ enumeration as a parameter in the _addFormControl_ method.    

```
//add comboBox
    var comboBox = sheet.shapes.addFormControl("comboBox", GC.Spread.Sheets.Shapes.
    FormControlType.comboBox, 100, 50, 200, 30);
```

You can also set the _comboBox_ options using the _options_ method, assign input range, set dropdown length, and link a cell.

```
    //set comboBox options
    var options = comboBox.options();
    options.inputRange = "'Loan Term'!A2:A26";
    options.cellLink = "'Loan Calculations'!C7";
    options.dropDownLines = 4;
    comboBox.options(options);
    comboBox.value(14);
```

### <a id="rate"></a>Interest Rate

To define the Interest Rate, a good idea is to use the [Group Box](https://www.grapecity.com/spreadjs/api/interfaces/GC.Spread.Sheets.Shapes.IGroupBoxFormControlOptions) control since it logically groups the controls in the form, improving its readability. We have presented Interest Rates as [Option Buttons](https://www.grapecity.com/spreadjs/api/interfaces/GC.Spread.Sheets.Shapes.IOptionButtonFormControlOptions) that only allow exclusive selections inside the groupBox.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/gltieljk/07-form.png?width=366&height=202)

To add a _groupBox_ form control to the JavaScript worksheet, you need to pass the _groupBox_ option of the _FormControlType_ enumeration as a parameter in the _addFormControl_ method.

```
    //add groupBox
    var groupBox = sheet.shapes.addFormControl("groupBox", GC.Spread.Sheets.Shapes.
    FormControlType.groupBox, 40, 130, 240, 80);
```

If you use the [Designer](https://www.grapecity.com/spreadjs/designer/index.html) component, you can insert the _groupBox_ by clicking on the Insert tab and choosing the _groupBox_ control on the Form Control panel.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/q43j3v0x/08-form.png?width=500&height=122.87581699346406)

Similarly, you can add an _optionButton_ in a Javascript spreadsheet, bypassing the _optionButton_ control of the _FormControlType_ enumeration as a parameter in the _addFormControl_ method.

```
    //add optionButton
    var optionButton = sheet.shapes.addFormControl("optionButton", GC.Spread.Sheets.Shapes.
    FormControlType.optionButton, 50, 50, 100, 30);
```

In addition, set the control's options using the _options_ method and linking a cell.

```
    //set optionButton options
    var options = optionButton.options();
    options.cellLink = "'Loan Calculations'!C8";
    optionButton.options(options);
    optionButton.value(true);
```

Another way to apply to add an _optionButton_ to the worksheet is by clicking on the Control panel under the Insert tab.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/xlykdy00/09-form.png?width=500&height=125.16297262059973)

You can specify the control _options_ below by clicking on the Format Shape dialog box.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/rkeprjc4/10-form.png?width=398&height=397)

We have linked the interest rate _optionButton_ with the C8 cell from the ‘Loan Calculations’ sheet, where we have calculated the Interest Rate.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/ekugrafj/11-form.png?width=360&height=261)

### <a id="down"></a>Down Payment

A Down Payment is a sum of money a buyer pays in the early stages of purchasing an expensive good or service. As mentioned, form controls can be incorporated into your dashboards, making interacting with charts and formulas easy. One of these form controls is the [List Box](https://www.grapecity.com/spreadjs/api/interfaces/GC.Spread.Sheets.Shapes.IListBoxFormControlOptions) control that we have used to define our Down Payment.

![JavaScript Loan Calculator](https://files.grapecity.com/gc-website-media/43vikcoq/12-form.png?width=360&height=182)

You can add a _listBox_ form control to the worksheet. This can be done by passing the _listBox_ option of the _FormControlType_ enumeration as a parameter in the _addFormControl_ method.

```
    //add listBox
    var listBox = sheet.shapes.addFormControl("listBox", GC.Spread.Sheets.Shapes.FormControlType
    .listBox, 100, 50, 200, 150);
```

You can also set the _listbox_ options using the _options_ method, assign input range, set selection type and link a cell. So, when the user changes the value in the _listbox_ control, the value in the cell also changes.

```
    //set listBox options
    var options = listBox.options();
    options.inputRange = "'Down Payment (%)'!A2:A11;
    options.cellLink = "'Loan Calculations'!C5";
    options.selectionType = GC.Spread.Sheets.Shapes.ListBoxSelectionType.single;
    listBox.options(options);
```

### <a id="calculations"></a>Loan Calculations

You will find below the formula references that we have used to calculate the financed amount, monthly repayment, total interest, and total repayment of the loan amount defined earlier.

*   **Amount Financed**: Loan Amount - (Loan Amount * Down Payments) - Arrangement Fee.
*   **Monthly Repayment**: PMT(Interest/12, Term*12, Amount Financed)
*   **Total Repayment**: Monthly Repayment * Term
*   **Total Interest**: Total Repaid - Amount Financed

Finally, you have created a Loan Calculator using the SpreadJS form controls feature.