I need a loan balance calculator/ tracker-

[Kev Williams]

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

[Nathan Johnson]

Most mortgages calculate interest monthly, not daily.

[Mike Henderson]

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

[Mike Chance in Iowa]

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.

[Kev Williams]

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

[Nathan Johnson]

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.