Subtracting From Multiple Cells Until 0

[lga3q]

I am trying to manually set up an amortization chart without the use of Excel's built in macros (the assumptions in those macros don't work for me). I am trying to figure out a formula that will allow me to show how a particular payment has been applied to interest and then principal.

If A1 is the payment amount, B1 is the outstanding interest and C1 is the outstanding principal, I'd like D1 to show how much of A1 was subtracted from B1 until B1 equals 0 (i.e., how much of the payment was applied to interest), and then I'd like E1 to show how much is left of A1 after subtracting B1, which is then applied to C1. I hope that makes sense?

I guess another (maybe better) alternative is for D1 to show how much of A1 was subtracted from B1 until B1 equals 0, and then E1 can just show what is left of A1 after B1 is paid.

I am trying to figure out what formula to insert in D1 and E1.

Any help would be really appreciated!

 

[lga3q]

I wanted to clarify on my above post:

I'm trying to manually set up an amortization table that will help me track payments. I can't use any of the templates because so many of the elements are variable: the interest rate changes, the payment amounts and payment dates vary. I've figured out how to calculate the amount of interest accumulating per payment period, but am struggling to figure out how to track the application of payments to interest and principal.

Certain of the cells have predetermined fixed amounts and don't need a formula (e.g., date, number of days in the calculation period, interest rate, etc.); other cells need simple formulas that I can figure out, but I am struggling with two cells (G1 and H1):


A1. Date (Fixed)
B1. # Days in calculation period (Fixed)
C1. Interest Rate (Fixed)
D1. Daily Accrued Interest (C1*J1*(1/365.25))
E1. Accrued Interest ((D1*B1)+I1)
F1. Payment Amount (Fixed)
G1. Principal Paid (HELP?)
H1. Interest Paid (HELP?)
I1. Unpaid Interest (E1-H1)
J1. Declining Principal Balance (Fixed/Prior J1-F1)

Thanks for any help!!