Loan Amortisation

Hi there,

I'm having a few problems in preparing a loan amortisation schedule without using a goal seek function that will calculate principal and interest, where the interest is calculated on an Actual/360 day basis and the principal and interest payment each month is kept reasonable constant (it can move slightly depending on the numbers of days in the month but I'm trying if possible to keep it constant).

I've seen some information which uses the PMT formula for calculating monthly payments but this takes the interest rate and does a more simply division by 12 - unfortunately when I use this function and calculate interest using the A/360 days the balance at the end of the loan term doesn't equal zero.

Can you please help!!

Thanks

Brad

To help with clarifying my problem I've attached my excel schedule.

To further clarify I don't want to change the interest but merely change the calculation of the principal amount such that I will always get a balance of zero at the end and the total debt service payment each month doesn't vary substantially although (it can vary slightly perhaps around some kind of number of days in month measurement).

I've highlighted the figure at row 79 that is not equating to zero at the moment.

If anyone can please help that would be brilliant.