Modelling a loan in excel

CAN1976

I have a Barclayloan, How can i accurately model the monthly interest and outstanding balance? Is it as simple as interest = balance * 0.075 / 12, or is it applied daily

What would be the best way to represent an overpayment?

Tixy

I do them as a monthly calculation, although in truth its probably a daily interest calc.

I just have an extra column to enter any overpayments above the usual monthly payment. (On mine I assume that the overpayment is the last day of the month, so that the interest is calculated before that overpayment, so that I am likely to overestimate the balance rather than underestimate).
For my purposes its accurate enough, although you could do a daily spreadsheet. If you do that then don't forget to factor in how long an overpayment takes from you paying it to it actually being credited to your loan account, which is likely to be a day or 3 I would imagine.

CAN1976

Tixy wrote: »

I do them as a monthly calculation, although in truth its probably a daily interest calc.

I just have an extra column to enter any overpayments above the usual monthly payment. (On mine I assume that the overpayment is the last day of the month, so that the interest is calculated before that overpayment, so that I am likely to overestimate the balance rather than underestimate).
For my purposes its accurate enough, although you could do a daily spreadsheet. If you do that then don't forget to factor in how long an overpayment takes from you paying it to it actually being credited to your loan account, which is likely to be a day or 3 I would imagine.

The reason I was asking was, I had yesterday made an overpayment of £200, but the balance I checked today only reduced by 130. Just been on the phone to them, and what is happening is my overpayment is first applied to interest accrued since the 1st (regular payment date) But this means that when my regular payment goes in on July 1st, less of it will go to interest as I have already paid some.

Happy now, was worried I was getting some kind of overpayment fee.