Results 1 to 6 of 6

Thread: I need a loan balance calculator/ tracker-

  1. #1

    I need a loan balance calculator/ tracker-

    You may remember my mobile home thread- the short version at the moment: the granddaughter & SO are buying the mobile from us, we're the bank. I have the loan and interest and payment schedule taken care of. Now what I need is an excel program or calculation to track made payments and remaining interest owed in real time and 'real money'. I've found a bazillion loan payment calculators, but no matter what I search for, all I can find are basic amortization calculators. That's all find & dandy if they pay exactly the same payment on exactly the 1st day of each month. Since they may pay more, or less, and/or on different days, What I NEED is a simple ledger that I can just enter in their payments and when they pay them, and have the ledger adjust the loan balance based on the daily interest, like the bank does...

    TIA
    ========================================
    ELEVEN - rotary cutter tool machines
    FOUR - CO2 lasers
    THREE- make that FOUR now - fiber lasers
    ONE - vinyl cutter
    CASmate, Corel, Gravostyle


  2. #2
    Join Date
    Feb 2017
    Location
    Minnesota
    Posts
    422
    Most mortgages calculate interest monthly, not daily.

  3. #3
    If you look up the equations, you can make your own excel spreadsheet to do what you want. When doing daily interest, most people use 360 or 365 days for a year, ignoring the extra day in leap years.

    Your daily interest is just the annual interest divided by 360 or 365 (whichever you choose to use). Note that 3% interest is really 0.03 for calculation.

    You take the remaining balance times the daily interest, times the number of days since the last payment. In Excel you can just subtract two dates and you'll get the number of days between them.

    Deduct that interest amount from the payment, then deduce the remaining amount of the payment from the remaining balance.

    Mike

    [share your spreadsheet with them so they know how things are being done. You'll avoid questions later and they can watch the balance go down.]
    Last edited by Mike Henderson; 09-14-2020 at 3:17 PM.
    Go into the world and do well. But more importantly, go into the world and do good.

  4. #4
    We did something similar about 10 years ago with a loan for someone. Check out www.vertex42.com and you will find a bunch of excel files you can download. I recall the excel spreadsheet had several Tabs/Pages where you could set up the Schedule and track the Payment.
    I read recipes the same way I read science fiction. I get to the end and I think, "Well, thatís not going to happen."

  5. #5
    Quote Originally Posted by Nathan Johnson View Post
    Most mortgages calculate interest monthly, not daily.
    I'm not really concerned whether interest is compounded monthly or daily, I just need the journal to accurately compensate for early, late and extra payments...
    ========================================
    ELEVEN - rotary cutter tool machines
    FOUR - CO2 lasers
    THREE- make that FOUR now - fiber lasers
    ONE - vinyl cutter
    CASmate, Corel, Gravostyle


  6. #6
    Join Date
    Feb 2017
    Location
    Minnesota
    Posts
    422
    Quote Originally Posted by Kev Williams View Post
    I'm not really concerned whether interest is compounded monthly or daily, I just need the journal to accurately compensate for early, late and extra payments...
    Traditional mortgages calculating monthly typically include a Grace period for late payments.
    If a payment comes in on the 2nd, no recalculation necessary for next month's payment.
    If a payment is 2 days early, no recalculation necessary.

    I'm just saying it would be easier.

Posting Permissions

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