Does anyone know of a relatively simple calculator that will handle payments applied to principal and interest due and calculate new payment due and balance remaining?

Using what interest accrual basis? Also, what do you mean - new payment due? Are these open-end loans?

XL will likely do what you want but it has to be in agreement with the allowed variable as Randy noted.

If you can do sample calculations by hand, XL is always a great choice for creating your own model. Once you have a tested model, you can produce or verify an entire portfolio.

It was for an owner-financed piece of property [personal] whereby the "buyer" pays sporadically and in bits/pieces. I was looking for something that might account for that on a simple interest-type basis.

That paints a better picture. A few more details:
1. Is the seller/lender subject to a state law that restricts or regulates the deal in any way?
2. Assuming it's legal for the seller/lender to charge interest, does state law specify U.S. Rule interest calculations for this type of credit?
3. How is the sales finance agreement worded re:
a. Calculation of interest?
b. Repayment of principal (payment schedule)?

Obviously, sporadic payments in bits & pieces more or less forces the parties to use some form of daily simple interest. In the event the sales finance agreement doesn't spell it out clearly, most likely the method will be either actuarial or U.S. Rule.

There is only one difference between these methods. Both use daily simple interest (PrincipalBalance * DaysSincePreviousPayment * AnnualInterestRate / DaysPerYear) and each permits 360, 364, 365, or 366 days per year, depending on the payment intervals, leap year, the contract language, etc. The difference is how the lender handles interest payment shortfall.

Let's say the contracted (or understood) method says that $120 interest is due but the borrower remits a payment of only $100. The actuarial method tells you to amortize negatively and add the unpaid $20 to the outstanding principal...i.e., compounding. The U.S. Rule prohibits compounding and requires the lender to hold the unpaid $20 as a non-interest-bearing receivable...like an escrow. When the next payment is made, the lender's posting order is old interest, then current interest, and finally principal.

1. No
2. I don't believe GA specifies. We use US Rule in-bank, however.
3. Have just been using a basic 30 day month [keeping it as simple as possible]
4. $ooooo.oo monthly, currently at 6% [36 mos/72 month amort] = $483.16/month/36 mos

I prefer to use the US Rule on how non-paid interest/arrears is handled. I'm not looking to punish, just keep it somewhat fair on everyone involved.

Maybe I'm on the wrong track here. This seems to focus on setup calculations. Your initial post says you want "something that might account for [sporadic payments in bits & pieces] on a simple interest-type basis." To me, "accounting for payments" means servicing.

It's fine to generate a strict monthly payment schedule--but mainly as a guideline for payments--you don't want the borrower's "sporadic payments in bits & pieces" to cause frequent defaults. EXCEL's PMT() function gets you a monthly amortizing payment and FV() gives you the dollar amount to add to the final payment in order to get a balloon.

After the loan closes, standard U.S. Rule servicing will apply whatever the borrower decides to pay whenever the payments come in. If this will be done outside of a capable servicing system, EXCEL can serve as a flexible device to handle cradle-to-grave interest calculations and principal reductions. Is this what you need?

I had set up an amortization based on specified terms. They have paid sporadically and so what I'm after is the servicing end of things....keeping up with 'bits and pieces' by hand is cumbersome and I'm trying to avoid mistakes as much as possible.
So yes, I believe your last paragraph sums it up.

OK, here's what you need.

Start a new EXCEL SS. It won't contain user-defined functions unless you want to get fancy. The engine is described below. You can dress it up any way you like--this is just the minimum it takes to get your job done.

Type labels in the following cells:
A1: Annual IR %
A2: Payment Date
B2: Days
C2: $Payment
D2: New Interest
E2: Matured Interest
F2: Unpaid Interest
G2: Principal
H2: Balance

Change the background color of cells B1, A3, and H3 to bright green. These are the loan values you know at setup. B1 is already labeled. A3 is the date of consummation and H3 is the original principal amount. If you'd rather add more rows and move the input from A3 and H3 to rows above the table, that's fine. Just be sure to set A3 and H3 equal to the cells where you enter the data and switch the shading from A3 and H3 to the new input cells.

Beginning at row 4, Columns A and C will be used to enter actual dates and dollars as payments are received from the borrower. Shade these columns a different shade of green to signify input. If you want to pre-fill "scheduled" payments, you can--but you'll key actual values over these initial targets as the borrower remits the sporadic payments. Payment values can't be negative and Payment Dates must be sequential.

Now key in the formulas:
C1: =B1/36500 Note: if you want to enter the Annual IR as a decimal value instead of a percentage, delete the two zeros here.
B4: =A4-A3
D4: =ROUND(H3*$C$1*B4,2) Note: Only $C$1 is an absolute reference
E4: =D4+F3
F4: =IF(C4>E4,0,E4-C4)
G4: =C4-E4+F4
H4: =H3-G4

Select cell A4, hold the shift key and left mouse button down, drag down to cell H23, and then let go of the mouse button and shift key. While this area is highlighted, hold down the Ctrl key and hit the letter "d." That sets the sheet up for 20 payment transactions. If you need more, fill down more.

In order to stop new interest accruals if the Balance goes negative, I usually replace cell D4 with the following before filling down. =IF(H3>=0,ROUND(H3*$C$1*B4,2),0)

Before using this sheet, compare its results with values you have worked out manually.

Woot! I will work on this as soon as I get a chance...THANKS! !

Oh this is FANTASTIC! THANK YOU so very much! You are da MAN!!!

Glad it's what you needed.

Canned Am schedule software almost always calculates interest by the actuarial method using monthly (not daily) simple interest. That causes small differences, but the bit hit comes when a loan runs into a condition that would cause negative amortization if you follow the actuarial method. Although it has its time and place, neg am isn't viewed as consumer-friendly & is typically avoided...by using the U.S. Rule. If you study both types of am-schedule generators, the U.S. Rule is what happens in columns E and F of my SS.

I got warped up on the payment not being fully satisfied, instead of applying what little bit was actually over the interest due and it had me all messed up on 'reality'. Yes, they are behind on payments, but have managed to satisfy the interest due...hopefully, they will get with the program sooner than later...and quit hurting their own self.

My 'by hand' calcs were creating neg am because of the principal due not being satisfied, I guess, is the best way I can put it.

This is a misnomer unless your note is worded along the lines of "$100 per month plus accrued interest." A plain vanilla SI note won't be specific about the payment of principal--it's whatever's left over after interest is paid current and any late charges, fees, etc. are paid.

When the borrower remits too little to cover all the interest that's accrued and neg am is not allowed, the descriptive term I like to use is "non-amortization."

Thanks again...so much...it all fell into place in my head now. Of course, now it makes me curious how it's handled at bank level when 'partial payments' are made.

Yes, that's one of the times the U.S.Rule vs actuarial difference is obvious. Also, you can see it when there's a long delay from one payment to the next. Long maturities and high interest rates increase the chances of non-amortization (because there's not much more than interest in the payments in the first place.) From the consumer protection perspective, non-amortization (planned as well as unplanned) isn't a big deal, but it always eats into the bank's yield on the loan.

Great thought-provoking discussion!