Power of HotDocs – Creating a Loan Amortization Table

Financial Calculations Made Easy

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. On the right, you can see 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.

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.

Share This Article :

Related Posts