Tips and cases of use for a successful WordPress website with calculated forms.
This post describes how to implement a mortgage calculator form, showing a table with the amortization of the debt.
The Calculated Fields Form plugin is distributed with many operations grouped in modules, one of them, the "Financial Operations" (with the Developer and Platinum version of the plugin), includes specific operations for Financial projects.
I'll describe the use of financial operations by implementing a hypothetical form (because the best method to learn something is doing it by yourself).
Please, visit the following link to download the form: CLICK HERE (Remember, it is required the Developer or Platinum versions of the plugin before importing this webform)
The first step would be to insert the entry fields, listed below:
Home Price(fieldname1): currency field with the price of the property to purchase.
Down Payment(fieldname2): currency field with the portion of the sale price of a property that won't be financed.
Length of Loan(fieldname3): number field, whose "Format" attribute is configured as "Digits" for the years required to repay the loan.
Interest Rate(fieldname4): number field, whose "Format" attribute is configured as "Percent" for the amount you'll pay each year as interest for the loan.
Mortgage Start Date(fieldname5): date field for entering the start date of the amortization schedule.
Once the entry fields have been inserted, it is time to include the fields to calculate the monthly payments and generate the amortization schedule.
To calculate the monthly payment, we will use the CALCULATEPAYMENT operation. This operation requires three parameters: the amount, number of months, and the interest rate.
PREC(CALCULATEPAYMENT(fieldname1-fieldname2, fieldname3*12, fieldname4*100),2)
The equation's description:
As the down payment won't be financed, the portion that of the sales price that matters for calculations is fieldname1-fieldname2 (the difference between the sales price and the down payment).
The length of the loan is entered in years, but the payments are monthly. So, the equation needs the duration in months: fieldname3*12
The third parameter would be the annual interest rate in percentage. Since the values of the numbers' fields formatted as "Percent" are represented as decimals, would be required to transform them into percent again: fieldname4*100
Finally, the use of PREC operation: PREC(X, Y) returns the X number rounded to Y decimals. As the previous equation should return a value in currency format, it must be rounded to two decimal places:
PREC(CALCULATEPAYMENT(fieldname1-fieldname2, fieldname3*12, fieldname4*100),2)
Now, we will move forward to generate the amortization schedule.
This process is more tricky, it requires to generate of the amortization schedule table from the equation's code, but as the calculated fields use input tags, display the resulting table into an "HTML Content" field.
Insert an "HTML Content" field in the form (fieldname7), with a DIV tag as its content where will be displayed the amortization schedule table:
<div class="amortization-schedule"></div>
Insert a second calculated field (fieldname8) to generate the amortization schedule table. This field has a particularity: since this field is used as an auxiliary, it is recommended to tick the "Hide Field From Public Page" checkbox in its settings. Enter the following equation into its "Set Equation" attribute:
(function () {
var r = CALCULATEAMORTIZATION(fieldname1-fieldname2, fieldname3*12, fieldname4*100, DATEOBJ(fieldname5)),
str = '';
if (r.length) {
str = '<table cellpadding="10" >';
str += '<thead>';
str += '<tr>';
str += '<th>Date</th>';
str += '<th>Interest</th>';
str += '<th>Payment</th>';
str += '<th>Payment to Interest</th>';
str += '<th>Payment to Principle</th>';
str += '<th>Principle</th>';
str += '</tr>';
str += '</thead>';
str += '<tbody>';
for (var i = 0, h = r.length; i < h; i++) {
str += '<tr>';
str += '<td>' + GETDATETIMESTRING(new Date(r[i]['date']), 'mm/dd/yyyy') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['interest'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['payment'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['paymentToInterest'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['paymentToPrinciple'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['principle'], 'USD') + '</td>';
str += '</tr>';
}
str += '</tbody>';
str += '</table>';
}
jQuery('.amortization-schedule').html(str);
return str;
})()
The equation's description:
This equation includes some new operations:
principle: how much remains to pay.
interest: accumulated interest paid until this date.
payment: the monthly payment (payment of interests plus payment of the property).
paymentToPrinciple: the portion of the payment to pay the property.
paymentToInterest: the portion of the payment to pay the interests.
date: payment date.
DATEOBJ: operation included in the "Date/Time Operations" module. It transforms a date field into a date object required by the CALCULATEAMORTIZATION operation.
GETDATETIMESTRING: this operation is similar to the previous one but in the other direction. It takes a date object and transforms it into a text with the structure passed as the second parameter.
NUMBERFORMAT: this operation is included in the "Financial" operations module, and transform a number to different formats. In this case, the second parameter is the 'USD' word to transform the number in currency format.
The equation's sections:
The first step in the equation is to declare the local variables. The "r" variable receives the results of the CALCULATEAMORTIZATION (the list of objects described above). The "str" variable is an empty string where we will be generating the HTML structure of the amortization schedule table:
var r = CALCULATEAMORTIZATION(fieldname1-fieldname2, fieldname3*12, fieldname4*100, DATEOBJ(fieldname5)), str = '';
The equation validates if the CALCULATEAMORTIZATION operation returns a valid list. If there is an error in the arguments, the operation returns an empty list:
if(r.length)
{
...
}
We have decided to show the amortization schedule in tabular format because it is easier to understand. Initially, we will generate the table header section:
str = '<table cellpadding="10" >'; str += '<thead>'; str += '<tr>'; str += '<th>Date</th>'; str += '<th>Interest</th>'; str += '<th>Payment</th>'; str += '<th>Payment to Interest</th>'; str += '<th>Payment to Principle</th>'; str += '<th>Principle</th>'; str += '</tr>'; str += '</thead>'; str += '<tbody>';
Now, we will go through the list of objects to create the rows of the table with the monthly amortization:
for (var i = 0, h = r.length; i < h; i++) {
str += '<tr>';
str += '<td>' + GETDATETIMESTRING(new Date(r[i]['date']), 'mm/dd/yyyy') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['interest'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['payment'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['paymentToInterest'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['paymentToPrinciple'], 'USD') + '</td>';
str += '<td>' + NUMBERFORMAT(r[i]['principle'], 'USD') + '</td>';
str += '</tr>';
}
After creating the rows, we must close the tbody and table tags:
str += '</tbody>'; str += '</table>';
To print the result, as I referred above, will be used the DIV tag into the "HTML Content" field.S
jQuery('.amortization-schedule').html(str);
Note, the equation includes a "return" instruction at the end, even if the table is shown into the "HTML Content" field. This return instruction assigns the table to the calculated field, allowing you to include it in the notification emails and thank you pages.
Please, visit the following link to download the form: CLICK HERE (Remember you need the Developer or Platinum versions of the plugin)