Payment schedule for dodgy debtor

June 22, 2009 3:52 PM

How to determine how much I'm owed by someone who's not going to pay regularly ? (in Excel preferably).

I lent $x as a favour to a person I knew on the basis they would make six monthly payments of $y - the idea was they were paying me an interest payment of 7%.

It's pretty clear now that rather nice picture of life is not going to be how it is.

In the best case what's going to happen is that they're going to make more than six payments and they're not going to be monthly (the worst case is I get an expensive lesson in life).

I want a model I can use to show how much is outstanding at the end of each month and then add the interest onto the principal.

There's an awful lot of 'if you borrow this much how much will the bank charge' stuff out there but I need something which will cope with irregular payments and recalculating the principle based upon the interest received.

Something in Excel would be great but just a desciption of how it should be done would do.

posted by southof40 to computers & internet (7 comments total)

How much they're going to end up paying you depends on how quickly they pay it back.

The easiest way that I can think of to do what you want is to use daily compounding.

.0185383 % daily is (to 4 digits) the same as 7 % annual.

So, every time they hand you a check, take the amount that they owed you after the last check and multiply it by 1.000185383^number of days since last payment. That's their previous principal + interest. Then subtract their payment. That's how much money they still owe.
posted by a robot made out of meat at 4:08 PM on June 22 [1 favorite has favorites]

Oh, and write and have them sign a contract acknowledging the debt and interest rate. Life is easier with contracts.
posted by a robot made out of meat at 4:13 PM on June 22

I'd just charge monthly interest and keep it simple. Ignore the precise payment day within a month and just calculate interest based on the balance at the beginning of the month (that is, Beginning Balance * 7%/12 = Interest)

Add the interest to the total balance and subtract any payments made during the month to come up with the end-of-month balance. Then use that as your starting number for the next month.

Below is an example spreadsheet assuming some random payments. All you have to do is input the payment amount for a given month. Then just add a new row each month.

Month		Beg. Balance		Interest		Payments		End Balance
Jun-09											1,000.00  
Jul-09		1,000.00  		5.83  			0.00  			1,005.83  
Aug-09		1,005.83  		5.87  			(100.00) 		911.70  
Sep-09		911.70  		5.32			0.00			917.02  
Oct-09		917.02  		5.35  			(200.00)	 	722.37  
Nov-09		722.37  		4.21 			0.00		  	726.58  
Dec-09		726.58  		4.24  			0.00		  	730.82  
Jan-10		730.82  		4.26  			0.00	  		735.08  
Feb-10		735.08  		4.29  			0.00 		 	739.37  
Mar-10		739.37  		4.31  			(500.00)		243.68  
Apr-10		243.68  		1.42  			0.00  			245.11  
May-10		245.11  		1.43  			0.00  			246.54  
Jun-10		246.54  		1.44  			0.00  			247.97  

posted by mullacc at 4:25 PM on June 22

If this is a friend, and you do not want to keep said fiend, then by all means do this. If not, just take the payments as you get them. Piling on a vig will just stress you both out and make your friend resentful and likely to avoid you (and paying you).
posted by cjorgensen at 6:45 PM on June 22 [1 favorite has favorites]

Vertex42 has loads of spreadsheets for this, e.g. loan amortization.
posted by dhartung at 8:16 PM on June 22

I used a spreadsheet very much like mullacc's to calculate how much I still owed on some money I borrowed from my dad. Worked out fine for us.
posted by oblique red at 8:30 AM on June 23

Thanks for all the answers - much appreciated.
posted by southof40 at 3:09 PM on June 24