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.

  • I don't need something complex, but I do want something that handles compounded interest and varying payment amounts. – SirPentor Nov 23 '15 at 23:15
up vote 4 down vote accepted

Depending on your interest rate (monthly here), you need something set up like this -

enter image description here

Basically, you need to calculate the debt based on the previous debt with a formula like this -

enter image description here

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.

  • 1
    You're close, but this doesn't handle compound interest correctly. The typical way to do it would be to convert an annual interest rate to a daily factor: (1+i)^(1/365). Then calculate the number of days for the last outstanding principle and add the interest: P*(daily_factor)^N. Then subtract the payment to leave a new principle amount. The question implies just charging interest based on the actual payment amounts and timing rather than penalties. – fixer1234 Nov 24 '15 at 17:09
  • This is precisely what I was looking for. Thank you! – SirPentor Nov 24 '15 at 18:29

Here'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.

[image 1 - actual appearance]

[image 2 - formulae]

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.

https://www.dropbox.com/s/hh3i2goug6rvdfu/Loan%20Repayment%20Calculator.xlsx?dl=0

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

  • It's been nearly a year since the original question was posted — and two answers were posted in the first 24 hours. Is your answer any better than those? How? Can you post the layout and formulas in your answer? People are reluctant to download files from unknown users. – Scott Nov 14 '16 at 4:37
  • I tried both of the first suggestions, but they didn't really work for me. Yes, I do think mine is better, despite its ugly appearance. I don't actually expect anyone to download it, but I'm making it available, if anyone wants to mess with it. – Calipete Nov 14 '16 at 6:36
  • What didn't work for you about them? Consider using apostrophes in order to make "formula code" in your answer to make it easier to identified. It would be displayed like this. – Seth Nov 14 '16 at 9:30

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.