Many of our large buildings are sold on promissory notes or leases with multi-year terms, paid monthly. The customer pays the same amount each month, but we need to split the payment into principal and interest. Currently, we have an Excel spreadsheet with an amortization table for each order. Just like a mortgage, each month the amount allocated to principal goes up, and the interest amount goes down… Our AR Specialist compiles that month’s rows for each order into one file, which I DMT to create invoices.
Is it possible to have Epicor do this somehow? In A/R Terms Maintenance, we can define a payment schedule, but I’m not sure what approach to take to split the payment into principal and interest when the invoices are created. Heck, I’m open to doing it outside of invoices too, as long as it goes to their respective GL accounts each month. Note I’ll check with the finance dept first!
Whoops… I totally misread your post the first time. You want Epicor to do everything. I think there is a way to setup finance charges. That could calculate interest, and it would continuously go down as the future finance charges would be based on the remaining balance.
I’d think you could use excel to calc the total payment (p+I) once, then just bill them that much each month. Epicor would apply that against the remaining balance + the interest accrued for the period. The interest part of the payment equals the accrued interest.
End edit
Original reply follows, you can ignore it.
The Excel financial functions like PMT() and INT() are fairly straightforward, but require info that might not be readily available in an invoice.
The first - and easiest for you - is to have finance calc the principle and interest and bill them as separate line items.
If you’re only given the total payment (P+I) to bill, you’d need to know the:
The initial principle, total number of payments, int rate, this payment number, int rate method (begining of term or end)
Or
Remaining principle, remaining number of payments, int rate, int method.
I’ll make up an excel file showing the formulas w/o using those specialized functions. Only with ±*/ and ^ (that last one is the trick)
@ckrusen - Good point! I’ll see what we can do with the Finance/Late Charge maintenance. That’d be great if every month it would apply the payment first against the finance charge, and the balance against the principal.
@bderuvo Sort-of, but the recurring invoice would need to have the principal and interest lines adjusted each month per the amortization schedule. Hmmm, I wonder how we could do that…
For example, the sales price is $100k. Add 60 months interest at 8% for $21k, and the total is now $121k. 121k/60 = $2027/mo recurring payment. This payment gets split between principal and interest. The first month starts off with $666.67 interest and $1360.97 principal, and the 60th (last) month ends up with $13.43 interest and $2014.21 principal.
@prash172 - Good idea. I’ve never looked at the Generate Allocation process… Can you share how you set it up to split it to principal and interest? Does it change every month based on an amortization schedule, or is P&I stay the same each month?
I checked Help, and it should be located in Financial Management > General Ledger > General Operations > Generate Allocations, but I don’t see it. Is it tied to a module we might not have (like Fixed Assets)?
You really can just think an amortization schedule as just being a finance charge. Since you still have a balance each month, a new finance charge is applied.
For example, $100,000 initial selling price, 6% interest, paid monthly over 2 years. Say you start making payments one month after you’ve received it…
At the end of month 1, you’ve incurred $500 of finance charge (6% / 12 x $100,000). Your new balance owed is $100,500.00 You make a payment of $4,432.06. and you balance drops to $96,067.94 ($100,500 - $4,432.06)
At the end of month 2 you incur $480.34 of finance charge (6% / 12 x $96,067.94). Your new balance is $96,548.28 You make another payment of $4,432.06. and you balance drops to $92,116.22
This continues to when the balance of prior to your last payment is $4,410.01. This incurs a finance charge of $22.05. You final payment of $4,432.06 - which is exactly the sum of those two, leaving you with a balance of zero
The only magic required is calculating the payment. And that only needs to be done once.
Another benefit of this is that a missed payment incurs more interest. Thus further incentivizing on-time payments.
