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.
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.
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, 2009
posted by a robot made out of meat at 4:13 PM on June 22, 2009
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.
posted by mullacc at 4:25 PM on June 22, 2009
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, 2009
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, 2009
posted by cjorgensen at 6:45 PM on June 22, 2009
Vertex42 has loads of spreadsheets for this, e.g. loan amortization.
posted by dhartung at 8:16 PM on June 22, 2009
posted by dhartung at 8:16 PM on June 22, 2009
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, 2009
posted by oblique red at 8:30 AM on June 23, 2009
« Older Lowering the rent for a roommate, but then... | Landlord require me to remove my bird feeder? Newer »
This thread is closed to new comments.
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, 2009