Excel Debt Payoff Calculator

0 votes

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 by Kithuzzz
• 38,000 points
408 views

1 answer to this question.

0 votes
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.
answered Apr 9, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
0 answers

How to compare two excel sheets

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

Jul 18, 2021 in Others by Sri
• 3,190 points
806 views
0 votes
1 answer

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

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,603 views
0 votes
1 answer

csv to excel conversion

So Basically u have to follow few ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
657 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

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

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
890 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,933 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,976 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,690 points
970 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,637 views
0 votes
1 answer

Excel Tax Calculator

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

answered Oct 15, 2022 in Others by narikkadan
• 63,600 points
584 views
0 votes
1 answer

Reading/parsing Excel (xls) files with Python

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

answered Sep 21, 2022 in Others by narikkadan
• 63,600 points
766 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP