[Solved] Math Teacher in Need of Assistance!

[MeesaWorldWide]

This might be a basic question, but I have just started teaching myself how to use Excel so I can teach my students how to use it.
I am trying to create basic amortization tables for credit card debt, and the attached file has 2 sheets with different credit debt tables (sheets are named Credit Card 1 and Credit Card 2). The first one I created by following a YouTube video tutorial, and I use the Excel function "NPER" to get 34.98 (meaning that the debt is paid off in 35 months). In the amortization table, the 'credit card balance' number drops below zero at the 'month 35' row, so that makes sense.
However, in the "Credit Card 2" sheet, I've set up the exact same situation with different numbers, and the NPER number I get is '64.28', so presumably I should expect the credit card balance to become negative in 'month 65'. But in the amortization table, it becomes negative in month 62, and I cannot figure out why.
Any help would be greatly appreciated!

 

[shamimarita]

Hello MeesaWorldWide,

This is a super common “why doesn’t NPER match my table?” situation, and it’s almost always one of these two things:

1. Payment timing mismatch (most common)

NPER() assumes payments happen at the end of each period by default (type = 0).
But many amortization tables accidentally behave like payments happen at the beginning of the month (type = 1) — especially if the first row applies the payment before calculating interest, or if the interest formula is referencing the wrong balance.

Fix: Make sure your NPER is explicitly using end-of-period payments:

=NPER(rate_per_month, -payment, balance, 0, 0)

• fv = 0 (paid off)
• type = 0 (end of month)

If your table is actually built as beginning-of-month payments, then use:

=NPER(rate_per_month, -payment, balance, 0, 1)

That alone can easily explain “paid off earlier than expected”.

2. Your amortization row formulas aren’t using the exact same inputs

Please check these in Credit Card 2:

Use this standard structure (and keep full precision; don’t round inside formulas)

Assuming:

• Previous balance is in E2
• Monthly rate is in $B$2
• Payment is in $B$3

Interest
=E2*$B$2

Principal
=$B$3 - F3

New Balance
=E2 - G3

Key checks:

• The payment cell should be an absolute reference ($B$3) everywhere.
• The interest must be based on the previous balance, not the new one.
• Avoid ROUND() inside the running balance math (rounding can shift payoff month).

Quick diagnostic (takes 10 seconds)
In month 1 of Credit Card 2:

• If you see interest calculated on the balance AFTER subtracting the payment, your table is using type=1 behavior, so NPER(type=0) will look “too long”.

 

[MeesaWorldWide]

Hello MeesaWorldWide,

This is a super common “why doesn’t NPER match my table?” situation, and it’s almost always one of these two things:

1. Payment timing mismatch (most common)

NPER() assumes payments happen at the end of each period by default (type = 0).
But many amortization tables accidentally behave like payments happen at the beginning of the month (type = 1) — especially if the first row applies the payment before calculating interest, or if the interest formula is referencing the wrong balance.

Fix: Make sure your NPER is explicitly using end-of-period payments:

=NPER(rate_per_month, -payment, balance, 0, 0)

• fv = 0 (paid off)
• type = 0 (end of month)

If your table is actually built as beginning-of-month payments, then use:

=NPER(rate_per_month, -payment, balance, 0, 1)

That alone can easily explain “paid off earlier than expected”.

2. Your amortization row formulas aren’t using the exact same inputs

Please check these in Credit Card 2:

Use this standard structure (and keep full precision; don’t round inside formulas)

Assuming:

• Previous balance is in E2
• Monthly rate is in $B$2
• Payment is in $B$3

Interest
=E2*$B$2

Principal
=$B$3 - F3

New Balance
=E2 - G3

Key checks:

• The payment cell should be an absolute reference ($B$3) everywhere.
• The interest must be based on the previous balance, not the new one.
• Avoid ROUND() inside the running balance math (rounding can shift payoff month).

Quick diagnostic (takes 10 seconds)
In month 1 of Credit Card 2:

• If you see interest calculated on the balance AFTER subtracting the payment, your table is using type=1 behavior, so NPER(type=0) will look “too long”.

Thanks for the suggestions. I have double-checked: I tried using the type zero by adding the ",0 ,1)" to the end of my NPER formula, but it didn't change anything.
I checked my month 1 row: I used the same formulas as you did for Interest, Principal, and New Balance. The only difference is that, for the Interest column, I used the APR interest rate instead of the monthly rate, so my formula looks like "E2*$A$2/12". But I tried changing it to reference the cell with the monthly rate and mothing changed.
I have spent several hours trying to figure this out and am getting a bit frustrated. Could you (or someone) actually lok through my file that I attached and see if anything looks off?

 

[shamimarita]

Hello MeesaWorldWide,

I found the exact cause of the mismatch in the Credit Card 2 sheet (and the same small issue exists in Credit Card 1).
The Problem:
Look at the Credit Card 2 sheet, row 8 (Month 1):

• Interest = 72 (correct: 3600 × 0.02)
• Principal = 28 (correct: 100 − 72)
• Balance = 3500 ← This is wrong

It should be 3572 (= 3600 − 28).

The formula in that first “Credit Card Balance” cell is subtracting the full Payment ($100) instead of the Principal column (only $28).

From Month 2 onward, the formulas are correct (they subtract Principal), but because Month 1 subtracted an extra $72, every future month’s interest is calculated on a balance that is $72 too low. Over 60+ months that compounds and makes the card pay off ~3 months earlier than NPER predicts (month 62 instead of month 65).

Exactly the same mistake is in Credit Card 1 (Month 1 balance shows 1327.21 instead of 1346.38), but because it’s only a 35-month schedule, the difference is too small to change the final month, so it “looked” correct.

(The Car Loan sheets are perfect — they subtract Principal every single month.)

Fix:
In both Credit Card sheets:

1. Go to the first balance cell after Month 1 (row 8 in both sheets).
2. Change the formula from=E7 - B8 (or whatever references the Payment)to=E7 - D8 (subtract the Principal column instead).
3. Press Enter.

That’s it. The rest of the table will automatically update.

After the fix:

• Credit Card 2 will reach ≤ 0 in Month 65 (exactly matches NPER 64.28).
• Credit Card 1 will stay the same (still Month 35).

 

[MeesaWorldWide]

Hello MeesaWorldWide,

I found the exact cause of the mismatch in the Credit Card 2 sheet (and the same small issue exists in Credit Card 1).
The Problem:
Look at the Credit Card 2 sheet, row 8 (Month 1):

• Interest = 72 (correct: 3600 × 0.02)
• Principal = 28 (correct: 100 − 72)
• Balance = 3500 ← This is wrong

It should be 3572 (= 3600 − 28).

The formula in that first “Credit Card Balance” cell is subtracting the full Payment ($100) instead of the Principal column (only $28).

From Month 2 onward, the formulas are correct (they subtract Principal), but because Month 1 subtracted an extra $72, every future month’s interest is calculated on a balance that is $72 too low. Over 60+ months that compounds and makes the card pay off ~3 months earlier than NPER predicts (month 62 instead of month 65).

Exactly the same mistake is in Credit Card 1 (Month 1 balance shows 1327.21 instead of 1346.38), but because it’s only a 35-month schedule, the difference is too small to change the final month, so it “looked” correct.

(The Car Loan sheets are perfect — they subtract Principal every single month.)

Fix:
In both Credit Card sheets:

1. Go to the first balance cell after Month 1 (row 8 in both sheets).
2. Change the formula from=E7 - B8 (or whatever references the Payment)to=E7 - D8 (subtract the Principal column instead).
3. Press Enter.

That’s it. The rest of the table will automatically update.

After the fix:

• Credit Card 2 will reach ≤ 0 in Month 65 (exactly matches NPER 64.28).
• Credit Card 1 will stay the same (still Month 35).

Thank you. That fixed it. I appreciate the help!

 

[shamimarita]

You are most welcome. Keep contributing in ExcelDemy forum.