Hi Loan Experts,

I loaned a friend 40,000.00 for starting business with promissory note requiring monthly interest payments of 300 (at 9% interest) for 2 years initial period at end of which the entire principal and pending interest was to be returned or note renewed with similar terms.

Friend paid as follows ...

1) paid for first 6 months regularly (300 x 6)
2) then missed interest payments for 5 months
3) then paid for 3 months worth of interest together (900.00) on the 12th month
4) and then totally stopped paying interest for next 18 months.
5) Then paid another 600
6) Then stopped for next 5 months

7) Now agrees to settle the account with full interest (including any compounding during unpaid times) and return of full principal.

My dilemma is how to go about calculating the compound interest during missed payments (i.e. over the original 300 interest per month and on the total principal with nominal rate (i.e. not the 9% original simple interest rate, but more like 2% or so) and at the same time apply lump sum payments in between missed payment periods to interest first and then principal.

Does anyone know if is there a loan calculator to handle this complex scenario?

It does not need to be perfect just fair enough to handle most of the above scenario as I have unfortunately made more than one such loans to more than one such missing payment (but ulttimately willing to pay) friends.

Any and all helpful replies are appreciated. Thanks in advance.

biszer1,

I believe your friend owes you $48,351 at the end of the 36th month. I'm not sure what you meant about the 2% or so these calculations assume unpaid interest is added to the principal and also accrues at 9%.

This is done fairly easily in Excel using the PMT function. If you want a copy of the spreadsheet let me know.

Tim Lawless
Unionwide Financial Services
timlawless@unionwide.com
---------------------------------------
Here are my calculations:

Interest Rate 9.00%
Amount financed $40,000
Term in months 36
Amort Payment Total
25
Month Due Paid Balance
1 300 300 40,000
2 300 300 40,000
3 300 300 40,000
4 300 300 40,000
5 300 300 40,000
6 300 300 40,000
7 300 --- 40,300
8 302 --- 40,602
9 305 --- 40,907
10 307 --- 41,214
11 309 --- 41,523
12 311 900 40,934
13 307 --- 41,241
14 309 --- 41,550
15 312 --- 41,862
16 314 --- 42,176
17 316 --- 42,492
18 319 --- 42,811
19 321 --- 43,132
20 323 --- 43,456
21 326 --- 43,781
22 328 --- 44,110
23 331 --- 44,441
24 333 --- 44,774
25 336 --- 45,110
26 338 --- 45,448
27 341 --- 45,789
28 343 --- 46,132
29 346 --- 46,478
30 349 --- 46,827
31 351 600 46,578
32 349 --- 46,928
33 352 --- 47,279
34 355 --- 47,634
35 357 --- 47,991
36 360 --- 48,351

In this case I would do just like Tim did and create a spreadsheet. By the way, I created a spreadsheet and I also get the same number {$48,351}.

To Tim and LoanUniverse Admin,

Thanks a bunch to both of you for your quick responses I am quite surprised (quite pleasantly that is) by the speed and good samaritan spirit which exists on the forum boards. btw: are you the "TL" in cooperative development topic? if so I thought that was a very creative arrangement to get a apartment in cities.

Tim,

Your reply post was quite helpful. It was nice of you to do the entire spreadsheet as I tried on my own but didn't quite succeed the same way as your results a) was not sure which one fit my needs as there are many excel functions like PMT i.e. IPMT, PPMT, ISPMT couldnot properly figure out how to adjust the missed payments and get the whole amort schedule c) was only getting the function inserted into one cell

so can you please send me the spreadsheet as offered by you to my email id at "biszers@joimail.com" and some quick tips to use it say with a different interest rate. Thanks in advance.

As for the 2% issue what I meant was that (using the spreadsheet example you posted) for the missed payment amounts 2% interest would apply instead of 9% until those missed payments are paid up (just to keep the "friendly" in the friendly loan arrangement and not ruin relations for delay in payments due to genuine business cycle difficulties for frienmds having full willingness to pay eventually)

I am not sure if I made myself clear so one more try i.e. if the friend missed payment #6 of 300 it would be added to the principal as you showed but the interest applied to it in the subsequent months would be 2% (instead of 9% little help for my loss of funds use but nor too much hurt to friend) until that amount is paid, also it would not change the 300 due for 7th month or any other subsequent month that the regular 300 payment is missed but just that every time a payment is missed it starts accruing 2% compound interest while the original 9% monthly payment still applies. does that make sense and if it does then is there a way to handle that in excel?

Just to make it a bit more friendly (but a bit more complex) I have also thought of giving some friends a break altogether and stop the clock on compounding and / or even lowering or eliminating the original interest, any thoughts on if and how to handle those scenarios. I will certainly owe you atleast a beer if you are ever in NYC area for your earlier and subsequent replies if any.

So Thanks in advance.

biszer1
biszers@joimail.com

btw: are you the "TL" in cooperative development topic? if so I thought that was a very creative arrangement to get a apartment in cities.

FYI- one of the most useful applications i've ever suggested to individuals involved in real estate transactions is the HSH homebuyer's calculator. It's free, and once you download it you can run amortizations, bi-weekly's, payments, etc. try http://www.hsh.com/hbcalc.html and see if it works for you.

If I understand you correctly then you want the 2% to apply to the past due interest balance. Take a look at this spreadsheet It might give you some ideas.

Frankly, I never use those functions in Excel and try to visualize the problem. Then when creating tables I just copy the formula down. {Ctrl-D} is my friend.

You certainly could figure out what this "alternative" would be, or you could simply say, "the obligation is $48,xxx, but I want to give you a break, and thusly will forgive $5,000 (or whatever amount), leaving an obligation of $43,xxx."

A little less complex - and perhaps more charitable. Good luck to you.