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.
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
// 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
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.