Excel Debt Payoff Calculator

Question

A weekly Excel debt retirement chart with a column for every week is what I'm making. Weekly dates appear in the header row (Friday payday). The front of the week has four columns: one for the name of the creditor, one for the due date in the month, one for the debt amount, and one for the payment amount. Monthly payments are made. After the loan is repaid, there should be no more payments. An illustration would be four payments spaced four weeks apart to pay off a debt in four months.

There is a distinct creditor in each row.

I got off to a good start by using

 =IF(AND(DATE(YEAR(G$1),MONTH(G$1),$C4)>=F$1,DATE(YEAR(G$1),MONTH(G$1),$C4)G$1),$E4,0) 

to have Excel display the payment date. Row 1 has the weekly dates, C4 is the date due (number, day of month), and E4 is the payment amount. I am eliminating payments made on the 29th, 30th, and 31st days to keep things simple.

PROBLEM --> How do I get the payment amount be set at zero after the debt is paid off? I've tried =(SUM($F5:INDEX(5:5,COLUMN()))) in a separate row and it correctly shows the total amount paid off in columns to the left. But I cannot use that to make the payment zero after payoff is complete because I keep getting circular logic errors.

Answer 1

You can change the formula in the payment amount column to include a check for whether the debt has been paid off or not to get the payment amount to be set at zero when the debt is paid off. You can change the formula as follows:

=IF($G4>0, IF(AND(DATE(YEAR(G$1),MONTH(G$1),$C4)>=F$1, DATE(YEAR(G$1),MONTH(G$1),$C4)<G$1), $E4), 0)

The first $G4>0 of this formula determines if there is any remaining balance on the debt. The formula determines the payment amount using the original method if the amount owed is more than zero. It sets the payment amount to zero in all other cases.