I'm going to lend someone money, using a standard amortization model. There are around six templates in Excel to calculate the amortization schedule.
But this is for a loved one and I know he will miss payments:) So I want to track how much he pays every month and have the spreadsheet handle the interest, etc.
I haven't found any templates with that kind of functionality. Any pointers?
Pointing me at a different application is probably fine. Pointing me at a web service won't be very helpful.
13 Answers
Depending on your interest rate (monthly here), you need something set up like this -
Basically, you need to calculate the debt based on the previous debt with a formula like this -
Now, if there are penalties for missing a payment, you can add those in by using a formula like this-
=(C2*$F$1)+(C2-B3)+50*IF(ISBLANK(B3),1,0)Where the fine is $50 for a missed payment.
If it's more complicated than that, you can use a formula like =ACCRINT for accruing interest.
2Here's one way you can manage it on your own. This is different than setting up an amortization payoff because it accommodates flexible repayment. If you'd prefer to adhere to a fixed monthly repayment of $111.25 or something, this is less useful. Otherwise...
Just add a line every month (I chose the 1st day of each month) and add a line every time there's a payment. That way you only enter the payments when made, and do nothing if no payment (except the monthly update on the 1st). If no payment is made, the balance just goes up.
You only enter data into the yellow cells. The white ones are formulae, that you copy down from the line above each time you add a line.
So Jan. 2 the loan started, effectively a negative 1000 payment. Feb. 1 you update the balance. Feb. 2 a payment is made, reducing the balance. Mar. 1 you update the balance. etc.
This is mildly imperfect because it rounds the interest to the penny, but it's essentially trivial. The interest method here is annual effective rate as stated, using i ^ (1/365) each day, also a trivial "imperfection."
You can still establish a scheduled monthly repayment amount with an amortization function such as PMT, but unless every payment is made on time, a flexible amortization approach such as below might work better for you.
[]
[]
It's been nearly a year since the original question was posted here. I found myself today looking for exactly this kind of calculator. Since I couldn't find one, I hunkered down and made my own. I called it The Ugly "I Loaned My Buddy Some Cash" Repayment Calculator. Below is a link to it. Feel free to check it out, critique, and improve it. If you have any questions about it, let me know.
User entry fields include Initial Loan Amount, Initial Loan Date, Interest Rate, Payment Date, and Payment Amount.
The Interest Rate is converted to a daily compounding factor with =D3/365.25, where D3 contains the user inputed interest rate in %.
A column called "# Days since last payment" contains =DATEDIF(A8,A9,"d") in ascending value.
Another column called "Same Day Balance" contains =IF(ISBLANK(A9),"",(E8*(1+$D$4)^C9)-D9) in ascending value and is used to calculate the next interest amount.
I was going to include a screenshot, but the system says, "You need at least 10 reputation to post images." I guess that's it.
Peter
3
