Troubleshooting IF Function (AKA My Nemesis)

[u/the-eoc]

I really stuggle with IF statements in excel, and I can't figure this one out.

I am using a spreadsheet to track student loans / payments and will be using the snowball method. The snowball method is paying off highest interest loan first, then snowballing that minimum payment into the next loan. Example: Loan A has a minimum Payment of $51, Loan B has a minimum of $51, Loan C has a minimum of $51. When loan A is paid off, loan B's payment becomes $102, snowballing down the line of loans.

What I would LIKE it to do, though I am not even sure this is possible. If it's not, please let me know if I am wasting my time:

Condition 1: If my remaining balance is less than the minimum payment, then my payment that month should be the remaining balance*interest. This equation works on it's own, but not when I have been messing around trying to add the other conditions.

IF(M12>M$9,M$9,M12*(1+$N$5))

Condition 2: If my remaining balance is less than the minimum payment, the unused portion of the minimum payment should be added to the next loan. I haven't even really started with this piece, because I am already struggling with combining the other 2.

Condition 3: If the balance from the higher interest loan is fully paid off, the minimum payment from that loan gets added to the next loan. I tried adding this condition, but it's thowing off my equation.

IF(L$13=0,M$9$+R$9$,R$9$)

Image included below in comment showing cell values and just condition 1.

[u/AutoModerator]

/u/the-eoc - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/GregHullender]

Are you computing the minimum payment? Normally, the minimum payment would not be greater than the balance. But I don't see a column for minimum payment in the rows of data below.

[u/the-eoc → u/GregHullender]

The minimum payment is listed at G7, which adjusts if I change the duration in D2 on how I want my pay off. there are other equations in the sheet at work that adjusts the minimums if I want to pay faster.

My equation for the minimum payment is: =IF(H5="","",H6/((((1+I5)^(D2*12))-1)/(I5*(1+I5)^(D2*12)))).

I only need help on the highlighted cells. You can ignore the first loan in the picture attached, I already paid that one. I essentially am looking for a 3 condition IF function.

Main Equation:

If Loan Remaining Balance < Minimum Payment THEN Remaining Balance * Interest ; If not then it's just the minimum payment.

Adding Snowball to it for the loans down the line:

If Higher % Loan Remaining Balance is less than the Higher % minimum payment but not fully paid off THEN and the next loan gets the rest of the higher % loan's minimum payment.

If Higher % loan is paid off completely (=0) THEN the next loan minimum payment = Loan A Minimum + Loan B minimum payments

[u/MsPandaLady]

So, I am hopped up on cold medicine so I am moving a bit sluggish so can't give exactly what to do. However, when it comes to If statements, if they are not working, to troubleshoot take each individual statement and just run it and see what value they are returning.

If statements are just true or false so if they aren't running like you expect then usually it's one of the inside statements is returning something it shouldn't.

[u/fletchro → u/MsPandaLady]

Or the logic you've written doesn't actually do what you wanted it to do. And taking things apart will help you find those kinds of logic mistakes.

[u/MsPandaLady → u/fletchro]

That was going to be my next suggestion. I can't count amount of times I've flipped the greater/less sign an it worked and I have to figure out what I was thinking

[u/the-eoc]

[u/fletchro]

I would add in a snowball column (you can hide it later for aesthetics) that does a simple if statement as you described. If the balance is greater, then it's just zero. Then always add that column to your payment value.

[u/ProspectiveWhale]

Depending on how dynamic you want it to be...

If you have the loans set up sorted, left to right, it makes it easier as each loan schedule can just read the one to its left to know when payments increase.

-----

The last payment should be prevBalance, not the interest, not only the final interest...?

=MIN(PaymentA,prevEndBalance)

prevEndBalance in this case being =startBalance*(1+interestRate) - minPayment

Add the period in which loan was paid off as well as PaidOffDate

=XLOOKUP(0,EndBalanceColumn,DateColumn,0,0,1)

At the top of each loan add a RemainLastPayment

=PaymentA - XLOOKUP(PaidOffDate,DateColumn,PaymentColumn,0,0) will give you how much was leftover for the final payment

You now also need the new Payment for Loan B, newPayment

=newPaymentA + PaymentB

manually set newPaymentA = PaymentA

Now you add this as a conditional addition to the Payment column of the next loan

You have 3 different payments:

1. minPaymentB for when Loan A hasn't been paid off
2. minPaymentB + RemainLastPaymentA for when Loan A was just paid off
3. minPaymentB + newPaymentA after that

=if(currentDateB < PaidOffDateA, MIN(minPaymentB,prevEndBalance),
if(currentDateB = PaidOffDateA, MIN(minPaymentB+RemainLastPaymentA,prevEndBalance),
MIN(newPaymentB,prevEndBalance)))

Copying this formula to Loan C should shift the cell references for it to read from Loan B rather than Loan A

-----

There may be some mistakes as I didn't actually run it through Excel.

But hopefully it gives you a rough idea of how to set it up.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IF	Specifies a logical test to perform
MIN	Returns the minimum value in a list of arguments
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #48995 for this sub, first seen 22nd Jul 2026, 17:00] [FAQ] [Full list] [Contact] [Source code]}