Search Contextures Sites
Set Up the Variable Cells
Set Up the Loan Table Headings
Add the CheckBox
Set up the Worksheet Headings
Create Loan Table Formulas
Format the Worksheet
Excel Formula Checkbox Workbook
Excel Function Tutorials
Thanks to Dave Peterson, who contributed this technique to change the formula results with a CheckBox.
This Excel example calculates loan payments, and shows the total amount repaid, if the CheckBox is checked. No macros are required -- the formulas do all the work.
You can use this technique in other workbooks too -- for example, use a checkbox to specify if tax should be included, or to add an employee discount to the price.
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
Next, you'll set up the headings for the Loan Payment table. Most of the heading cells contain formulas, linked to the variable cells.
- Enter these text headings:
- A11: Years
- B11: Interest Rate
- 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.
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.
- Enter these formulas in the Years row headings:
- A12: =$B$6
- A13: =A12+$B$7
Select cell A13, and copy it down to cell A16, to complete the first group of Year headings, with increments.
Enter this formula in the Interest Rate row headings:
- B12: =$B$4
Select cell B12, and copy it down to cell B16, to complete the first group of Interest Rate headings.
- Copy cells A12:A16
- Click on cell A18, and paste
- Enter this formula in cell B18:
- B18: =B12+$B$5
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.
- Copy cells A18:B22
- Click on cell A24, and paste, to create the third group of row headings, showing the interest rate with two increments.
A checkbox from the Form controls will let you see loan payments, or total amount to be paid.
- On the Excel Ribbon, click the Developer tab.
- In the Controls group, click Insert
- Under Form Controls, click CheckBox, to activate that tool.
Click in cell C2, to add the CheckBox there. Select the text in the CheckBox, and type a new description: Show Total Paid Back Click OK to close the dialog box
Widen the CheckBox, so all the text is visible Right-click on the CheckBox, and click Format Control On the Properties tab, remove the check mark from "Print Object" On the Control tab, set the Linked Cell to $C$1
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:
- Select cell C1, and on the keyboard, press Ctrl + 1 to open the Format Cells dialog box
- On the Number tab, select Custom as the Category.
- In the Type box, enter three semicolons: ;;;
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.
Next, you'll set up the worksheet headings.
- 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()
- To centre the headings, select cells A8:F10, and on the keyboard, press Ctrl + 1 to open the Format Cells dialog box.
- On the Alignment tab, under Horizontal, select Center Across Selection, then click OK.
This will centre the headings, without merging the cells.
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:
- 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:
Copy the Formula
- After you enter the formula in cell C12, copy it across to F12, and down to row 16.
- Then, copy the PMT formulas from Group 1, to Groups 2 and 3.
- Format the worksheet, with currency, and percentages.
- Test the checkbox, and adjust the column width to fit the total amount to be repaid numbers
- 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.
To see the formulas, and test the checkbox, you can download the Excel Formula Checkbox sample workbook. The file is in Excel 2007 format, and is zipped. There are no macros in the workbook.
Excel Video Tutorials for Functions
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: May 22, 2013 7:47 PM