Yesterday, I took on a new “challenge” in one of the many commercial lending systems we have been building. Typically, we attach a Loan Amortization Table supplied by the “bank client” to the loan documentation.
These tables project payments of principal and interest during the term of the loan. After some research on formulas, review of VBA model code and Excel spreadsheets, I took the challenge. That featured image up top is the result.
Once you realize that a REPEAT DIALOG is just an Array, and that a Computation (in HotDocs 11) can serve as a recursive function, then it is just a matter of patience before you can get such results.
Financial Calculations Made Easy
You can use a custom function to calculate the Monthly Payment.
fnc Monthly Payment(prmPrincipal, prmInterest, prmPayments)
SET locInterest TO ( prmInterest / 1200 ) SET locNU TO POWER( ( 1 + locInterest ) , prmPayments) ( locInterest * prmPrincipal * locNU ) / (locNU - 1 )
To produce the table, you need a repeating dialog and another custom function.
SET locPeriodPayment TO fnc Monthly Payment CO(prmPrincipal, prmInterest, prmPayments) SET NDX TO 1
WHILE NDX <= prmPayments // Loop through period for # of Payments IF NDX = 1 // For First payment, set to principal Balance SET locPeriodStartBalance TO prmPrincipal ELSE // but for prior payment set to Ending Balance of Prior Iteration SET locPeriodStartBalance TO locPeriodEndBalance END IF
// Set Local Variables SET locPeriodInterest TO locPeriodStartBalance * ( prmInterest / 1200 ) SET locPeriodPrincipal TO locPeriodPayment - locPeriodInterest SET locPeriodEndBalance TO locPeriodStartBalance - locPeriodPrincipal SET locPeriodDate TO prmStartDA + NDX MONTHS
// Set Values in the Table SET AMRTS Period NU[NDX] TO NDX SET AMRTS StartBalance NU[NDX] TO locPeriodStartBalance SET AMRTS Payment NU[NDX] TO locPeriodPayment SET AMRTS Principal NU[NDX] TO locPeriodPrincipal SET AMRTS Interest NU[NDX] TO locPeriodInterest SET AMRTS EndBalance NU[NDX] TO locPeriodEndBalance SET AMRTS Payment DA[NDX] TO locPeriodDate
// Update the Index for next payment INCREMENT NDX
Reason to Upgrade Today
If you ever wanted a reason to upgrade, this script above would be it. It requires FUNCTIONS and the use of some newer functions, such as POWER which were added with HotDocs 11. If you are considering a stocking-stuffer, now is the time to upgrade. Please visit the Basha Systems Store and go to the HotDocs page to place your order. Click here to place your order now.