How would I track loan payments in Excel?

Question

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.

Solution 1

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.

Solution 2

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.

[]

[]

Comments

[SirPentor]

I don't need something complex, but I do want something that handles compounded interest and varying payment amounts.

[fixer1234]

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.

[SirPentor]

This is precisely what I was looking for. Thank you!

[MicrosoftShouldBeKickedInNuts]

ty for clarifying attachment edit

[Scott - Слава Україні]

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.

[Calipete]

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.

[Seth]

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.