This company offered commercial leasing arrangements for capital equipment. In essence it was a very simple CRM. Each enquiry was treated completely separately. Even if the client had other leases. There were two complex bits. One was calculating the payments. The other was populating the legal documents.
[1] At school compound interest was straight forward. Principle + Interest – Payment. You would then loop through this over the number of years (Term). Eventually arriving at zero. Loan paid off.
Nothing could be that simple. The calculation had to take into account the following factors.
- Was there an advance tranche of the loan?
- How much?
- Were the re-payments: monthly, quarterly, semi-annual or yearly?
- Were they to be paid in advance or arrears?
- Was there a deposit or advance payment needed?
- Was it hire purchase, lease or loan
- What sort?
- Sometimes VAT was involved. Sometimes insurance…
- And balloons were involved too. Nothing to do with parties and actually just one and it’s to do with deferring amounts.
They had been using the IMPT & PPMT functions in excel. But it didn’t quite take into account all of the above. So they had to spend time manually calculating the answer.
Working closely with the MD we came up with a calculation/script that fulfilled all his criteria – at the click of one button.
[2] We then had to produce the legal documents. They already had the various legal templates. All we had to do (!!) was to pull through conditional values based on all the data and amend certain paragraphs and/or conditionally hide or include them. It was meticulous work, but the end result was that at the click of another button, the right form, with the correct paragraphs and the correct values could be printed (or a pdf produced) immediately.
