Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating interest and balance owing on debt (Ex

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating interest and balance owing on debt (Ex

    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

  3. #3
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating interest and balance owing on debt

    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.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating interest and balance owing on debt

    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).

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating interest and balance owing on debt

    How about this sheme? [I assumed 365 days per year so the daily interset is 6.25/36500.]

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •