Power of HotDocs – Creating a Loan Amortization Table

by

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.

fnc CreateAmortTable

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
END WHILE

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.