Allocation of loan payments first to late charges

[silvanet]

I lent money to a friend on an agreement that he'd pay it back in monthly payments and if he was late there would be a late charge added. I want to make a spreadsheet to track the payments made and the accumulation of late charges.

We agreed that payments would go first to pay the late charges and the balance would be credited toward the loan. We agreed on an interest rate and calculated the monthly payments on the loan to be $400 over 26 months. So, the total due with the simple interest is $10,400. He's agreed to pay that off in 26 monthly $400 payments.

I just don't want any arguments later on how the payments were credited in case he gets behind. So far we are very friendly and agreeable on this. I just want to keep things clear.

Can anyone help me figure a formula to begin with the total due on the loan, add any late charges if they accumulate, and allocate the payments clearly first to the charges?

I realize this is not as simple as it first sounds because if he is late one month and then only pays the regular monthly amount, he will be first paying the late charges and then his next payment will not fully cover the next amount due.

I don't want this to become ridiculously burdensome on him, but I want financial matters to be clear between us. Maybe it is best not to continue to accrue late charges if he actually just makes a payment, but if he's late with the regular payment I do want to add the charge.

I can work pretty well with Excel, but I'm no financial genius. Thanks for anyone's help in advance.

 

[BadgerRoad]

You have two options. One is to collect the late charge if it occurs in addition to the regular payment so the loan remains on schedule. The second option is to take the late charge out of the payment which will stretch the loan out to a term greater than 26 months until it is paid off. This is all pretty simple as long as you don't run into a situation where the late charges throw the loan into negative amortization. That shouldn't happen with a short term loan. If it does happen it gets messy.
The easiest thing to do is to create an amortization schedule which shows how the loan pays down month by month. You can include late charges when and if they occur and see the effect. Amortization schedules are easy to create and probably available somewhere on line. I have one I can provide you if you can't find one.

 

[silvanet]

To keep things simple I have decided to make a schedule of payments and keep a separate record of each late charge as it occurs. I'll then just forget about accruing the late charges along the way and crediting payments first to pay off the late charges. That was my idea to make sure he does not take them seriously, but I guess that is just too much work to calculate. At the end of what would have been the payoff if there had not been any late charges, I'll sum up the late fees and tack them on the end. Thanks anyway. I wanted a way to calculate the monthly payments first to the late charges and then to the principal of the loan.