Loans: How to apply late fees, interest, and principle amounts to respective dates.

Originally Posted by D794913

I loaned some money in the form of a Promissory Note. The loan is now in default and I am trying to create a spreadsheet that shows the details and respective balance's of the Note. The Note dictates a 3 year term, 7% compounded APR, a fixed scheduled payment amount and late fees of 1% compounded monthly. The late fees apply if it is not paid by the due date and/or the monthly amount is not paid in full.
[....]
I also need the cells to recognize the amount paid (regardless of when) and to apply the payments in a specific priority. The order of priority is:
#1 Late fee's
#2 Interest
#3 Principle

First.... There is much to this question that goes far beyond Excel usage. It depends on the interpretation of the terms of the loan. I believe that needs to be nailed down in order to be sure that any Excel design is correct.

Please specify the following terms of the loan after mediation.

1. Loan amount to repaid starting in Aug 2014. Is it still $49,449.95?(!)
2. Annual interest rate. Is it still 7%?
3. Term of the loan in months, or date of the last payment. Is it still Jan 2016?(!)
4. Monthly payment. Is it really $418?(!)
5. Final balance of the loan ("balloon payment"). Is it still zero?(!)

What you describe above are some of the original terms of the loan. But comments in Sheet2 indicate that new terms were negotiated as part of mediation. It is unclear exactly what the new terms are.

If the monthly payment is $418, the annual rate is 7% and the last payment is Jan 2016 (i.e. 17 payments) with a zero ending balance, the mediated loan amount is PV(7%/12,17,-418), which is just $6,746.33(!).

On the other hand, if all those terms apply, but the mediated loan amount is still $49,449.95, the ending balance after 17 payments is $47,141.91(!).

Neither seems likely. Hence the need for you to clarify the mediated terms of the loan.

-----

Second.... For the most part, you cannot use the Excel financial functions like IPMT in the design because the terms of your loan are outside the assumptions of those functions.

In particular, Excel financial functions assume that payments are made on-time and completely. So each payment is allocated to interest and principal according to regular annuity amortization. Interest paid is the outstanding balance times periodic rate; and principal paid is the payment less interest paid. (The regular payment is required to be more than the first-payment interest in order to avoid "negative amortization".)

-----

Take a look at the worksheet "Sheet1 (mod)" in the attached file for a possible design. Changes and additions are highlighted in yellow, based on a number of assumptions discussed below.

The following summarizes the formulas:

D6,  monthly payment:     =ROUND(-PMT(H1/12,D3,D1),2)
L19, original loan:       =D1
O19, total outstanding:   =SUM(L19:N19)
F20, payment due:         =IF(C20="","",D20+M19+N19)
G20, late fees due:       =IF(C20="","",N19+IF(OR(C20>B20+5,E20<D20),F20*$H$2))
H20, late fees paid:      =IF(C20="","",MIN(E20,G20))
I20, interest due:        =IF(C20="","",M19+L19*$H$1/12)
J20, interest paid:       =IF(C20="","",MIN(E20-H20,I20))
K20, principal paid:      =IF(C20="","",E20-H20-J20)
L20, ending loan balance: =IF(C20="","",L19-K20)
M20, unpaid interest:     =IF(C20="","",I20-J20)
N20, unpaid late fees:    =IF(C20="","",(G20-H20)*(1+$H$2))
O20, total outstanding:   =IF(C20="","",SUM(L20:N20))
D39, mediated payment:    418
L39, mediated loan:       =L37
O39, total outstanding:   =SUM(L39:N39)
D40, regular payment:     =$D$39

where in your original design:
D1:  original loan amount, $49,449.95(!)
D3:  original number of payments, 36
H1:  annual interest rate, 7%
H2:  late fee rate, 1%
B20: payment due date
C20: payment received date or "no payment"
D20: regular payment due, =$D$6
E20: payment received

D6, monthly payment. The result from PMT should be rounded, due to real-world constraints. It might not make a noticable difference in such a short-term loan. But it is a "good practice".

O19, O20 and O39, total outstanding. The sum of the ending loan balance, unpaid interest and unpaid late fees.

F20, payment due. The sum of the regular monthly payment, unpaid interest and unpaid late fees.

G20, late fees due. The unpaid late fees plus 1% of the payment due if: (1) there is no payment; or (2) the payment is received 5 days after the due date; or (3) the payment received is less than the payment due. Note that in Excel, any text in C20 is considered "greater than" the numeric value B20+5. So the condition OR(C20>B20+5,...) is shorthand for OR(ISTEXT(C20),C20>B20+5,...).

H20, late fees paid. The late fees due, or the payment received, if less.

I20, interest due. The unpaid interest plus interest on the previous ending balance. Note: In the US, APR is a simple rate, not compounded. So the monthly interest rate is 7%/12, as you calculated in your design in worksheet "Sheet1 (orig)".

J20, interest paid. The interet due, or the remainder of the payment received minus late fees paid, if less.

K20, principal paid. The remainder of the payment received minus late fees paid minus interest paid.

L20, ending loan balance. The previous ending balance minus principal paid.

M20, unpaid interest. The previous unpaid interest minus interest paid. I assume that unpaid interest does not compound. I believe that is typical of "closed-end" loans, especially if unpaid late fees compound as you specify.

N20, unpaid late fees. The previous unpaid late fees minus late fees paid. This amount is multiplied by 1+1% since the late-fee rate compounds.

Based on those formulas, I believe that as of the end of July 2014, the loan balance remains $49,449.95 (i.e. no principal paid), unpaid interest is $826.15, and unpaid late fees are zero.

In Sheet2, you indicate that the terms of the loan in default were renegotiated through mediation. The unpaid interest was forgiven; and the regular monthly payment became $418(!).

Those new terms are reflected in D39 and D40.

It is unclear what the mediated loan balance should be in L39. I set it to the balance as of the end of July 2014, =L37. But that seems unlikely.

-----

I hope this is a step in the right direction. Let me know if you have any questions or if you require changes that you don't know how to make.

If you do, please be sure to provide the terms of the mediated loan, as explained above.