Variable Loan Repayment Schedule

This is a "doozy" for me.

We buy a piece of equipment on any given date in the year.
Here is the details of the payment plan.

1. It is financed over 3 years.
2. Payments are made every 6 months.
3. We pay 15% for the first 5 payments and 25% for the final payment
4. Each payment includes the principal part + interest for the previous 6 months.
5. The interest rate changes on each 6 month anniversay.

I have hundreds of these contracts all on different days/months/years. I need to be able to properly determine a months interest expense, accrued interest, and principal balance of each contract and in sum.

So lets say we finance 100,000 on 1/16/08 at 4% Interest.

January's expense is 100,000 * .04 / 360 * 14.
February's expense is 100,000 + January's Interest Expense * .04 / 360 * 30
etc....
Then on 7/16/08 the interest expense changes to 5%

July's interest is 100,000 + previous months interest expense * .04 / 360 * 16 + 100,000 *.85 * .05 / 360 * 14.

Is this an Excel application or an Access applicaton. I am struggling badly. Or there any services out there I could pay to do this for me that you are aware of?

Thanks for any response.


I am exhausted trying to figure this one out.

If you are interested, I can design a spreadsheet for you that will do this.

PM or email me if you're interested.