Calulate interest for a loan as payments are received

[Guest]

I am selling a home on contract for $132,900 with monthly payments of $1115
paid at 8% interest over 20 years. Start Date May 1, 2007. First payment due
June 1, 2007. Balloon payment due June 1, 2008. I need to create a
spreadsheet that will track the payments and calculate the intrerest and
credit the payment based on the date that I receive the payment. The payment
will be credited to the interest first and then principal.

Can anybody help me?

 

[Guest]

Perhaps this, or another, Excel Mortagage/Loan Amortization template will
help you?
http://office.microsoft.com/en-us/templates/TC010566201033.aspx

If you really need to get down to the amount of interest in a period based
on the number of days into the period you receive the payment, these should
at least be a good starting point - then you can get help here based on a
definite setup to calculate the interest/principal split of a payment on a
given date.

 

[Fred Smith]

Start a spreadsheet with the following columns:

Date, Opening Balance, Payment, Interest, Closing Balance.

Interest = Opening Balance * Interest Rate * (Date - Previous Date) / 365
Closing Balance = Opening Balance - Payment + Interest

Copy down as far as you need.

This will compound interest every payment, which I suspect is sufficient. If you
absolutely need to compound on some other basis, let us know.