0
$\begingroup$

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:

  1. 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.)
  2. 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:

https://stackoverflow.com/questions/9329017/calculating-apr-using-reg-z-appendix-j

https://www.fdic.gov/regulations/laws/rules/6500-3550.html

How to calculate APR using Newton Raphson

$\endgroup$
4
  • $\begingroup$ I think this question would be more suited in Economics SE $\endgroup$ Feb 3, 2017 at 16:29
  • $\begingroup$ I used to work with this in car finance - maybe you could give an example of a simple interest repayment, and what the APR is that you get for that? There is an Excel IRR function that could obtain the monthly IRR over a stream of differently sized payments, that could then be converted to APR by $(1 + i)^{12} - 1$ $\endgroup$
    – Cato
    Feb 3, 2017 at 16:50
  • $\begingroup$ odd days to first payment - in EXCEL you could have a table of dates with payment amount, and a field that is daily internal rate of return - you could then use goal seek to find the irr that gives you zero present value (where the loan itself is a negative payment). $\endgroup$
    – Cato
    Feb 3, 2017 at 16:56
  • $\begingroup$ you could use Newton Raphson, but is seems a bit fiddly, goal seek in Excel will just guess until it gets the answer super quickly with modern computers. $\endgroup$
    – Cato
    Feb 3, 2017 at 16:58

2 Answers 2

0
$\begingroup$

With Goal Seek - I put some formulae behind the scenes to work out number of days old the loan was, and multiplied by $(1 + i)^n$ to get a stream of present values, then used goal seek to get i - finally annual rate (APR?) is $(1+i)^{365} - 1$

enter image description here

that's an idea - however I notice your US financial regulations have all sorts of rules to do with days and converting them into fractions of months - but the general idea could still be used.

$\endgroup$
2
  • $\begingroup$ Could you be a little more clear? $\endgroup$ Nov 12, 2019 at 23:02
  • $\begingroup$ no not really, The question was too general to address really - and he never came back $\endgroup$
    – Cato
    Nov 13, 2019 at 17:49
0
$\begingroup$

Excel's financial functions absolutely can't properly handle an unusual days to first payment, but it's easy to do manually.

Check out https://www.bankersonline.com/forum/ubbthreads.php/topics/1403020/constant_payment_amount_loan_c.html

Here's my implementation in pseudocode, works really well and matches the hundreds of banks I work with:

var dailyRate = InterestRate / 36500;
var longdays = DaysToFirstPayment - 30;
var principle = AmountFinanced;
var odddaysint = longdays * dailyRate * principle;
var r = InterestRate / 1200;
var c = Pow(1.0 + r, NumberOfPayments); // 1+r ^ NumberOfPayments
var adjustedPrincipal = AmountFinanced + odddaysint;
var A = adjustedPrincipal * r * c / (c - 1);
Payment = Round(A, 2); // 2 decimal places

I am assuming monthly payments. This should be easy to adapt to Excel.

DaysToFirstPayment is the # of days from the start of the loan until the first payment is due.

I'm not addressing the APR issue.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.