Calculating interest and balance owing on debt (Ex

Topic

New Reply

I am trying to calculate how much is owing on a debt after calculate interest and payment made.

The original debt was $13,521.99 on March 14, 2000. The yearly interest rate is 6.25%. If a payment of $40327 was made on June 25, 2000, how do I calculate the total amount owing on that date including interest and then calculate the amount owing?

See attached spreadsheet.

Thanks for any suggestions.

Reply | Quote

Replies

How often is interest accumulated? Annually, Quarterly, Monthly, Weekly or what?

When are payments due? (it seems like quarterly). Is there a penalty for late payments (in case you had monthly payments and you missed 2 of them).

Steve

Reply | Quote

Interest is calculated daily. The debtor pays whatever money he has whenever he can. So there isn’t a due date or payment schedule per se. I’ve added in the new few payments to the spreadsheet so you can see how it is looking. What I’m looking for is something that calculates the interest from the date of judgment to the date of payment. Then add that to the balance owing less the amount paid.

Reports will be sent to debtors advising them of what they owe after their last payment. Rather than calculate the interest manually and then add it to their outstanding balance, I thought it would be easier to write a formula but I don’t know how.

Thanks again for any suggestions.

Reply | Quote

Have a look at the attached s/sheet.

It (implicitly) assumes that interest will be compounded monthly – but that will depend on the statute / judgment to be applied.

To extend it forward, just select the last two or three rows in column A & B and drag them down the page – Excel will fill in the series of month-end dates – keep going until you are up to the last date you need. Insert any payments and the applicable dates below that – the text in column B is conditionally-formatted blue and bold for payments so they will stand out. If the interest rate changes, include that like any other transaction. When you have all the transactions entered sort the s/sheet by date (click Data | Sort – it will default to sorting by ascending values in the “date” column since that is what I have done last. The sort will put the payments in the right order with all the interest accrual / compounding entries. Copy the formulas in columns D and E down as far as you need, and the ‘interest rate’ formula – just be careful to put in any rate change as required (if applicable).

Reply | Quote

How about this sheme?

In B3, enter (the outstanding balance):
=IF(E3=””,””,B2+E3-C3)

Copy B3 to B4:B whatever

In E3 (Interest for the period)
=IF(ISBLANK(D3),””,+B2*(1+6.25/36500)^(D3-A2)-B2)

In E4:
=IF(ISBLANK(D4),””,+B3*(1+6.25/36500)^(D4-D3)-B3)

Copy E4 to E5:E whatever

In F3 (running total of interest charges)
=SUM($E$2:E3)
Copy F3 to F4:F whatever

Steve

Reply | Quote