calculate balance given varying payments?

[Jerry]

I need to calculate the remaining balance on a loan given:
- fixed starting value
- fixed interest rate over the life of the loan
- size of payment can vary
- date of payment can vary (typically once/month, could be several in a
month, might be a month with no payments)

Basically given the starting balance and interest rate I just want to
record the payments as they come in.

Financial calculations (especially in Excel) aren't my specialty so any
help is appreciated.

 

[merjet]

Suppose cells A1:C5 look like this:

interest_rate: 0.05
date payment balance
1/1/2007 10000.00
1/28/2007 100 9936.16
2/20/2007 105 9861.75

The formula for cell C4 is =C3*(1+C$1)^(INT(A4-A3)/365)-B4
The formula for cell C5 is =C4*(1+C$1)^(INT(A5-A4)/365)-B5

Copy the formula for as many dates and payments as needed.

Hth,
Merjet