I don't think you can use the CUMPRINC function, because it assumes that the
loan will be paid off in the number of periods that you specify. That isn't
the case in your situation.
If the amount of the monthly payment is known, you can set up an amortization
schedule with, say Balance due in column D, Payment in column A, columns B and
C contain the split of the payment into interest and principal.
Put these headers in row 1: Payment, Interest, Principal, Balance
Put the initial balance in D2
Put the payment in A3:A50
The formula for interest, in B3, is ) =ROUND(D2*0.04/12,2)
The formula for principal, in C3, =A3-B3
The formula for the new balance, in D3, =D2-C3
Copy the formulas down. With an initial balance of 4000 in D2, payments of 75
in A3:A50, I get a balance of 795.83 after the 48th payment.
If you are confident that the results are correct, you can get by with just 1
column showing the remaining balance. If you put the initial balance in, say
B1, and in B2 the formula =ROUND(B1-(75-B1*0.04/12),2) and copy it down for a
total of 48 payments. 75 is the payment amount.
I would use a 2nd column, say A, for the payment dates or payment number.
If you want to experiment with different payment amounts, you could put the
amount in another cell and replace the literal 75 in the formula with an
absolute reference to the cell containing the payment amount.
