See how to calculate loan payments, and show the total amount repaid, if the Check Box is checked. No macros are required  the formulas do all the work.
Thanks to Dave Peterson, who contributed this technique to change the formula results with a CheckBox.
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.
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.
A checkbox from the Form controls will let you see loan payments, or total amount to be paid.
You can hide row 1, and/or format cell C1 so the text doesn't appear. Use this Custom Format to hide the text:
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.
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:
=PMT($B12/12,12*$A12,C$11)
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)
To see the formulas, and test the checkbox, download the free Excel Formula Checkbox sample workbook. The file is in xlsx format, and is zipped. There are no macros in the workbook.
Last updated: February 14, 2018 1:22 PM Contextures RSS Feed