Home > Formulas > Sum > Check Box Change Formula With Check Box ✅ See how to change a formula result by clicking a worksheet check box. In this example, formula shows total amount repaid, if the Check Box is checked. No macros required -- formulas do all the work. Also see the Loan Payment Schedule Template page

Loan Payment Formulas

Thanks to Dave Peterson, who contributed this technique to change the formula results with a CheckBox.

This example shows a check box changing the result for a loan payment formula.

You can use this technique in other types of workbooks too -- for example, use a checkbox to specify if tax should be included, or to apply a customer discount to the price.

Set Up the Variable Cells

This worksheet calculates loan payments, using the PMT function. At the top of the worksheet, you'll set up a data entry range, where you can enter the loan information.

Put these headings and values in cells A2:B7 -- the green cells are the variables, and you can change them later.

Loan Amount	$1,000,000.00
Increment	$50,000.00
Interest Rate	7.000%
Increment	0.250%
Years	15
Increment	5

Set Up the Loan Table Headings

Next, you'll set up the headings for the Loan Payment table. Most of the heading cells contain formulas, linked to the variable cells.

Column Headings

1. Enter these text headings:
   • A11: Years
   • B11: Interest Rate
2. Enter these formulas in the column headings, to display the loan amounts, in increments:
   • C11: =$B$2
   • D11: =C11+$B$3
• Select cell D11, and copy it across to cell F11, to complete the column headings.

Row Headings

There are three groups of row headings -- one group for each interest rate. You'll set up the first group, as described below, then copy it to the create the other groups.

Group 1

1. Enter these formulas in the Years row headings:
• A12: =$B$6
• A13: =A12+$B$7
2. Select cell A13, and copy it down to cell A16, to complete the first group of Year headings, with increments.

   

3. Enter this formula in the Interest Rate row headings:
   • B12: =$B$4
4. Select cell B12, and copy it down to cell B16, to complete the first group of Interest Rate headings.

   

Group 2

1. Copy cells A12:A16
2. Click on cell A18, and paste
3. Enter this formula in cell B18:
• B18: =B12+$B$5
4. Select cell B18, and copy it down to cell B22, to complete the second group of row headings, showing the interest rate with one increment.

Group 3

1. Copy cells A18:B22
2. Click on cell A24, and paste, to create the third group of row headings, showing the interest rate with two increments.

Add the CheckBox

A checkbox from the Form controls will let you see loan payments, or total amount to be paid.

1. On the Excel Ribbon, click the Developer tab.
2. In the Controls group, click Insert
3. Under Form Controls, click CheckBox, to activate that tool.

   

4. Click in cell C2, to add the CheckBox there.
5. Select the text in the CheckBox, and type a new description: Show Total Paid Back
6. Click OK to close the dialog box

   

7. Widen the CheckBox, so all the text is visible
8. Right-click on the CheckBox, and click Format Control
9. On the Properties tab, remove the check mark from "Print Object"
10. On the Control tab, set the Linked Cell to $C$1

    

11. Click OK to close the dialog box

You can hide row 1, and/or format cell C1 so the text doesn't appear. Use this Custom Format to hide the text:

1. Select cell C1, and on the keyboard, press Ctrl + 1 to open the Format Cells dialog box
2. On the Number tab, select Custom as the Category.
3. In the Type box, enter three semicolons: ;;;

   

4. Click OK to close the dialog box

You'll be able to see TRUE or FALSE in the formula bar, but nothing appears in the cell, thanks to the custom format.

Set Up the Worksheet Headings

Next, you'll set up the worksheet headings.

1. Enter the following text and formulas:
   • A8: Loan Comparison Worksheet
   • A9: ="Amounts Shown Are " & IF(C1,"Total To Be Repaid","Monthly Payments")
   • The heading in cell A9 will change, depending on the checkbox setting. If C1 is TRUE, it will end with "Total To Be Repaid". If C1 is FALSE, it will end with "Monthly Payments".
   • A10: =NOW()
2. To centre the headings, select cells A8:F10, and on the keyboard, press Ctrl + 1 to open the Format Cells dialog box.
3. On the Alignment tab, under Horizontal, select Center Across Selection, then click OK.

This will centre the headings, without merging the cells.

Create Loan Table Formulas

The next step is to add the loan payment formula. The formula uses the PMT function, to calculate the monthly payments.

PMT(rate, nper, pv, [fv], [type])

For example, to simply calculate the loan payment in cell C12, this formula would be used:

=-PMT($B12/12,12*$A12,C$11)

• rate: The rate is in cell B12, and that annual interest rate is divided by 12.
• nper: The number of periods is 12 per year, multiplied by the number of years in A12
• PV: The present value is the loan amount in cell C11

Add the CheckBox Factor

The PMT function calculates the loan payment for a single month. With the checkbox at the top of the worksheet, you can opt to see the total amount to be repaid. That would be the monthly payment, multiplied by the total number of payments (number of years x 12).

At the end of the PMT function, you'll check the value in cell C1, which is linked to the checkbox. If cell C1 is TRUE, then the monthly payment is multiplied by the number of payments. If C1 is FALSE, the monthly payment is multiplied by 1.

Enter this formula in cell C12:

=-PMT($B12/12,12*$A12,C$11)*IF($C$1,$A12*12,1)

Copy the Formula

1. After you enter the formula in cell C12, copy it across to F12, and down to row 16.
2. Then, copy the PMT formulas from Group 1, to Groups 2 and 3.

Format the Worksheet

1. Format the worksheet, with currency, and percentages.
2. Test the checkbox, and adjust the column width to fit the total amount to be repaid numbers
3. If you plan to protect the sheet, unlock cells B2:B7, so the variables can be changed, and unlock cell C1, which is linked to the checkbox.

Download the Sample File

To see the formulas, and test the checkbox, get the Excel Formula Checkbox sample workbook. The file is in xlsx format, and is zipped. There are no macros in the workbook.

More Tutorials

Cost Calculator, Annual

Loan Payment Schedule Template

Order Form with Billing Check Box

Excel PMT function

Sum Functions

VLOOKUP

INDEX / MATCH

Count Cells

INDIRECT Function