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

Hello,

I am having difficulty in understanding how formula's work for a loan amortization. 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 have searched through the posts to find assistance in doing this on my own to no avail. I have tried to self-learn the fx's of IPMNT, IF, (many others) and what/how to use the formula so that the cells recognize when a payment is late, and if/how much late fee's to apply. 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

Once those sum's are calculated, I need to have the respective total amounts shown in each respective cell that is in the Note Summary area. I have worked on this for weeks; now, after countless attempts and restarts, I have learned much however, I must confess that I may have the computing sums, balance, and applied payments completely wrong at this point.

I have attached a file that has all the details; the file does not have the formulas needed so that I can provide the ending balance YTD.

Any help would be very much appreciated.

Hello!

Thank you for your response to my quest in trying to figure this out.

I would first like to provide a short summary that address’s your (welcomed) exclamation points:

A ‘friend’ fell on hard times that I later learned were due to sexual harassment in the work place (got fired) and a violation of a restraining order (got put in jail). Unaware of all the details, I believed the (very) sad story. A promissory note was created of which the terms and conditions were produced by him. The funds were used to pay mortgage on a commercial property he owned that was in foreclosure in addition to purchase a business that would generate immediate cash flow; the business also included a residential apartment which provided him a home since his was foreclosed on. In addition to the funds, I produced a resume for him (no typing skills to speak of) that provided him w/full time employment with a very good salary.

On a side note - I was hit by a habitual drunk driver (5 DUI's) that left me w/a severe brain injury (TBI). Part of the neurological permanent effects is that I have head tic’s - this is especially severe under stress. When the stress is in the red zone, the right side of my body stops responding - leg drags, arm (right handed) is limp. Another permanent effect is that my short term memory is obliterated - I recently learned that I have all the signs for dementia and am at risk for early dementia.

Unbeknownst to me at the time, he was writing checks from my account (of which I have copies of). Payments on the loan were originally scheduled to start Oct 15, 2012 however, no payments were being made. After a little sleuthing, I became aware that the business was doing very well and because many of the tenants give him cash for rent, he was pocketing loads of cash (which is untraceable for the IRS). He also purchased vehicles (Volvo’s) and other high priced items. I sent him a demand for payment of which was ignored. I retained an attorney and went through all the legal red tape.

The judge ordered mediation at which time the debtor gave me an ultimatum: “Accept my terms for the repayment or I will file bankruptcy and you will never see a dime”. One of those terms included that I do not press criminal charges against him for the checks he wrote (I showed him the copies of my checks w/his handwriting). The terms also included (contrary to the Agreement) that he would pay no attorney fees. Those too were at my costs :-(

This money was my life savings - because of my TBI, I can only work from home and only on a part-time basis (and yes, he knew this). I have since discovered he has done the same scheme with several others - to an excess of $800,000.00 - nearly all of whom were seniors or those w/disabilities including his +90 year old parents (! This all deserves an exclamation point!). The one that wasn’t a senior (or w/disability) was another person that also fell for the sad story. She too lost everything including her home (foreclosed on).

I did meet with the local D.A. (seeking criminal charges was just as important as getting my savings back) of which I was told that regardless of an agreement which states I cannot pursue criminal claims, no office (attorney) can dictate who the D.A. prosecutes. I wouldn’t have agreed to the new terms if criminal action could not be taken but thankfully I was assured by the D.A. that my signature on the agreement would not prohibit me from pursuing criminal action against him.

That said, your questions and my answers about the NEW terms of the loan after mediation:

1. Loan amount to repaid starting in Aug 2014. Is it still $49,449.95?(!)
Yes. His terms included an interest free loan from 2012 through July 2014 (no interest until July 2014).

2. Loan amount to repaid starting in Aug 2014.
Repayment start date was July 7th and on the 7th of every month thereafter.

3. Term of the loan in months, Is it still Jan 2016?(!)
A: The interest rate is 7% - no late fees. The term is from July 2014 - Jan 2015
(7 months). (No surprise given the amount of cash he pockets on a weekly
basis)!

4. Date of the last payment. Is it still Jan 2016?(!)
A: The last payment date is Jan 7, 2015

5. Monthly payment. Is it really $418?(!)
A: Yes, yes it is (!) I get sick every time I think it.

6. Final balance of the loan ("balloon payment"). Is it still zero?(!)
A: The full amount is due Jan 7, 2015.

Again, No surprise given the amount of cash he pockets on a weekly basis!

In addition to his terms, I required a “good faith gesture” in the form of an extra payment in the amount of $1,000.00 due on July 21, 2014 (my home was in foreclosure). I also insisted that upon any default of this “new agreement”, the amount owed will be increased to the terms of the prior Agreement (interest rate, late fee’s, etc). I was able to put a deed of trust against the commercial building that I saved from foreclosure in addition to a confessed judgment, and a guaranty - all of which was detailed in the Settlement Agreement.

Unbelievably, he has so much cash now that he contacted my attorney last month to ask me that if I would reduce the amount owed by $5,000.00 he would be happy to pay it all in full immediately. My response was unequivocally “NO”.

As I am reading/providing this information, I read your text 1 paragraph at a time and respond (1 paragraph at a time) - this helps me (and the reader) to write/think as logically as I can and I just read your part about my comments on sheet 2 -

Ha!
I take much longer than I used to in writing and getting my thoughts out- That sheet was used to help me post my thread and I was supposed to delete it after I was successful in posting it. My short term really stinks.

Old Terms of loan:
Amount: $49,449.95
Interest rate: 7%
Duration: 36 months
Loan start date: January 17, 2013
First pay date: February 15, 2013
Subsequent pay date: 15th each month
Scheduled pmnt amount: $1,526.87
Maturity Date: October 15, 2015
Late Fee’s apply 1%

New Terms of loan:
Amount: $49,449.95
Interest: 8%
Duration: 7 months
Loan start date: July 7, 2014
First pay date: July 7, 2014
Maturity Date: January 7, 2015
Subsequent pay date: 7th each month
A one time payment of $1,000 on 7/21/2014
No Late Fee’s apply


***Old terms go in effect upon default of new terms*** he has defaulted by not making the payment on time.


I haven’t yet reviewed your worksheet - but will do so today. Again, your response is most appreciated - especially the exclamation points !!!!!!

One more thing....In regards to the interest (compounded or otherwise), the old agreement states that "If a default should occur prior to the Maturity Date, the outstanding principal amount and the accrued interest and other charges shall bear interest at the highest rate allowed by law.

My state statutes states the following: "When the Note is in default, interest accrues from time of breach at the rate of eight percent per annum compounded annually for all moneys".

Additionally, the statue includes the following: "[My State of residence] currently has one of the higher general usury rates in the country -- for non-consumer loans. The general usury rate in this regard (for non-consumer loans) is set at 45% per annum. The usury rate established for consumer or personal loans is set at 12% in Colorado at the present time. (In fact, some of the western states currently do have some of the most liberal practices and statutes currently on the books when it comes to usury laws and personal loan interest rate limitations.)

The definition for non-consumer loans is: "non-consumer debt is that which is incurred for business purposes. “Business debt” is a debt incurred with a “profit motive”, a business purpose, or investment purpose".

This loan/Note was for business purposes; while I will consult w/my attorney, on the surface it appears that I am well within my rights to assess the highest interest rate by law - compounded annually".

Originally Posted by D794913

5. Monthly payment. Is it really $418?(!)
A: Yes, yes it is
[....]
6. Final balance of the loan ("balloon payment"). Is it still zero?(!)
A: The full amount is due Jan 7, 2015.

Based on the other terms of the new loan below, the final balance is =FV(8%/12,7,418,-49449.95), which is $48,819.11, assuming all other payments are on-time and paid in full.

Originally Posted by D794913

Please Login or Register  to view this content.

For the original loan, I believe the duration (36) is correct. That jibes with the monthly payment ($1526.87), which is =PMT(7%/12,36,-49449.95). But it's a moot point because the original loan is defunct.

Based on those facts, I have changed the suggested design. See the "Sheet1 (mod) v2" worksheet in the attached file "9 OCT 2014 AMORTIZATION mod v2.xlsx".

The key difference is: the last payment for the original loan is payment #17, which was due (but missed) on 15 Jun 2014. The payments of $418 and $1000 in July 2014 are for the new loan.

Thus, the old loan ends on row 36. The outstanding balance is $49,449.95 (no principal paid); the unpaid interest is $1940.94; and the unpaid late fees are $61.90.

The new loan starts on row 39. The terms of the new loan are in row 38. They are referenced by the formulas starting in row 39. Normally, those terms would appear in rows 1 through 6 instead.

For the 7 July payment, I believe "interest due" (I39) should reflect one month of accrued interest, since the loan was disbursed long before.

However, that is subject to interpretation and the terms of the mediation. You might want get clarification from your attorney or loan default mediator.

In any case, I believe the entire $1000 paid on 21 July applies to principal. So "interest due" (I40) is set to zero.

Originally Posted by D794913

My state statutes states the following: "When the Note is in default, interest accrues from time of breach at the rate of eight percent per annum compounded annually for all moneys".

I believe the effect of that is to charge interest on accumulated unpaid interest every 12 months.

This is reflected in the updated formula for "unpaid interest" in M20, for example, in the the "Sheet1 (mod) v2" worksheet, to wit:

=IF(C20="","",IF(MOD(A20,12)<>0,I20-J20,(I20-J20)*(1+$H$1)))

It is a moot point because: (a) unpaid interest for the original loan is forgiven by the terms of the mediated new loan; and (b) the term of the new loan is less than 12 months.

Apologies for the delay - I took the spreadsheet to my attorney to clarify the details.

Due to the default of the mediated new loan, the prior terms and conditions of the original loan is in full force and effect. Interest, late fees, etc. from the original loan are the numbers to use.

My understanding, albeit muddy..: The end balance's for each respective year is the beginning balance for the next year. The terms of 36 months goes to 24 months after the 1st year which means the scheduled amount to pay changes at the beginning for each year.

Attached is the "Sheet1 (mod) v32" worksheet in the attached file "9 OCT 2014 AMORTIZATION mod v3.xlsx". I am pretty sure I maintained the formula's EXCEPT for column 'O" Total Outstanding. In your last work (mod v2), the total outstanding reflected the original amount as opposed to the rolling balance forward (hopefully that makes sense).

I changed this so that the total outstanding balance was updated to include interest owed + late fees owed..

I am curious as to why you had the total outstanding column reflective of the original amount for each month even though the monthly amounts changed?

If summing all the respective amounts are incorrect to get the total outstanding, would you please explain.

Also, your advice of "PMT should be rounded, due to real-world constraints. It might not make a noticeable difference in such a short-term loan. But it is a "good practice" - was excellent. Thank you.

After looking at your work, where I thought I had some familiarity w/excel I see now that I'm still at the basic level. Your formulas have given me a great point of reference of where to start in trying to learn more.

Originally Posted by D794913

In your last work (mod v2), the total outstanding reflected the original amount as opposed to the rolling balance forward (hopefully that makes sense).

I changed this so that the total outstanding balance was updated to include interest owed + late fees owed.

It is a matter of interpretation of the facts. Of course, your interpretation is best as long as your attorney agrees, since you know the facts better than I.

Originally Posted by D794913

I am curious as to why you had the total outstanding column reflective of the original amount for each month even though the monthly amounts changed?

If summing all the respective amounts are incorrect to get the total outstanding, would you please explain.

I'm not sure I understand the question. It would be clearer if you showed specific formulas instead of talking in abstract terms.

Oh, perhaps you mean: in O15 for example, you replaced my SUM(L21:N21) with SUM(O14+M15+N15).

That is wrong. By adding O14, you are double-accounting for the amounts in M14 and N14. See my comment #2 below for details.

(Your expression works in O14 only by coincidence.)

Perhaps my design would be clearer if I had used the titles "C.u.m Unpaid Int" and "C.u.m Unpaid Late Fees" now in M12 and N12, where "C.u.m" means cumulative.

Sigh, I really mean to write the first 3 letters of the word cumulative. But the silly robo-censor replaces them because they look like a s.e.x term -- yet-another "nasty" word that the robo-censor does not like.

-----

Some comments on the new calculations.

1. The formula in I13 does not seem to belong. But it's harmless since nothing else depends on it.

2. It is arithmetically incorrect to sum columns G, I, M and N per year because the monthly values in those columns are cumulative. For example, 33.73 G15 is 15.27 in G14 for the "late" (unpaid) payment of 1526.87 due on 15-Feb-2013 (plus 1% compounded = 15.42, which is actually in N14) plus 18.31 for the "late" (unpaid) payment of 1830.75 due on 15-Mar-2013. So it would be wrong to do SUM(G14:G15), since that double-accounts for the G14 late fee.

3. Technically, the references to $D$8 starting in G29 and N29 and to $D$7 starting in I29 and M29 should be changed to $L$6 and $L$7 respectively. The latter are the APR [sic] and late penalty rate for the modified loan starting in 15-Feb-2014. By coincidence, they are the same as the original; so there is no computational error. But IMHO, the formulas should be changed for clarity and for flexibility.

4. The reference to D$6 starting in D44 should be changed to L$5, similar to D29:D40.

Aside.... Technially, I think $D$6 and $L$5 are better in this context. Looking back, I did use D$6; just a typo, I think. It is not that important since the effect is the same. But it is better communicates the intent and it is less error-prone as the design evolves.

Those are the issues that I spotted during a cursory check of your updated Excel file.