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.

Apr 9, 2023 in Others 183 views

## 1 answer to this question.

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.
• 63,420 points

## How to compare two excel sheets

How to compare two excel sheets , ...READ MORE

## Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

## Excel Tax Calculator

You can replace it with a set ...READ MORE

## Reading/parsing Excel (xls) files with Python

I highly recommend xlrd for reading .xls files. But there are ...READ MORE