I work for a small financial institution as an auditor. We do various small loans that are always simple interest. Currently, we calculate payments on loans using the =PMT() method in Excel.
There are a few problems:
- We state on our contracts the interest accrual method is Actual/365. However, as I understand it, the Excel =PMT() function is Actual/360.
- Business wants to start accepting loans where the days to first payment can vary between 30 - 50 days. =PMT() doesn't account for this.
- We already accept loans that have an odd last payment amount. =PMT() doesn't account for this.
I'm concerned we are not always complying with Regulation Z that requires the APR stated on the contract be within a certain tolerance of the actual APR when completing the calculation manually. I've spot checked a few of our loans with APRWIN which does this calculation for you (https://www.occ.gov/tools-forms/tools/compliance-bsa/aprwin-software.html) and some of them are out of tolerance. But I don't even know of APRWIN is the right tool as I don't know what interest accrual method is using do perform its calculation.
I brought all of this up internally. No one seems to know how to create a payment formula that will account for all of this. Myself included, I'm terrible at math. We have been going back and forth on the issue for months now and still no one has been able to come up with a suitable formula.
I'm very frustrated and stressed that no one is able to solve this. I'm looking for help in getting a set of formulas that will accomplish this.
I'm looking for:
A payment calculation formula that:
- Uses interest accrual method Actual/365.
- Can account for odd days to first payment. So, I can give the formula the contract start date and the first payment date, and the payment amount will adjust accordingly. (All other payments will be on a monthly schedule.)
- Is APRWIN the right tool to use in order to verify the calculated APR if the interest accrual method is Actual/365?
- If not, an APR calculation formula that works for interest accrual method Actual/365, can account for odd days to first payment, and can also account for an odd last payment amount.
- Some sort of explanation as to why this works. Think of the audience as someone who has a very basic understanding of financial math. Like, they understand how to put a simple amortization table together.
Links that have been brought to my attention, but don't fully explain how these formulas may change for different interest accrual methods, or odd payment schedules: