Amortization schedule formulas

[eechristaylor]

I have a formula to amortize a total dollar amount that is located in C52. I only want this to calculate when the payment is less than C52. So that when the loan amount is reached the monthly payment is then showed as $0. I was using PMT($B$46/12,$C$46,$C$52)) where B46 is the rate and C46 is the term.

=IF((SUM($P$46:Q46)+PMT($B$46/12,$C$46,$C$52))<$C$52, PMT($B$46/12,$C$46,$C$52),0)

So if C52 equals 100k and the rate is 6% and term is 14 months in month 15 and 16 the number should be $0. it continues to show the$7414 and continues on. How do I get it to see the amount and show zero once the term is reached?

I also tried this formula:

=IF(SUM($P$46:S46)<$C$52, PMT($B$46/12,$C$46,$C$52),0)

Any help would be appreciated.

 

[Dave Patton]

Consider the following

Mortgage - Canadian.xlsm
	A	B	C	D	E	F
1	Start Date	1-Jan-23
2	Principal	100,000.00
3	Rate	6.00%
4	Monthly	0.50%
5	Term	14
6	Payment	$7,413.61
7
8	Payment Num	Date	Payment	Interest	Extra Payments	Balance
9		1-Jan-23				100,000.00
10	1	1-Feb-23	7,413.61	500.00		93,086.39
11	2	1-Mar-23	7,413.61	465.43		86,138.21
12	3	1-Apr-23	7,413.61	430.69		79,155.29
13	4	1-May-23	7,413.61	395.78		72,137.46
14	5	1-Jun-23	7,413.61	360.69		65,084.54
15	6	1-Jul-23	7,413.61	325.42		57,996.35
16	7	1-Aug-23	7,413.61	289.98		50,872.72
17	8	1-Sep-23	7,413.61	254.36		43,713.47
18	9	1-Oct-23	7,413.61	218.57		36,518.43
19	10	1-Nov-23	7,413.61	182.59		29,287.41
20	11	1-Dec-23	7,413.61	146.44		22,020.24
21	12	1-Jan-24	7,413.61	110.10		14,716.73
22	13	1-Feb-24	7,413.61	73.58		7,376.70
23	14	1-Mar-24	7,413.58	36.88		0.00
24	15	1-Apr-24	-0.00
1cc

Cell Formulas
Range	Formula
B4	B4	=B3/12
B6	B6	=ROUND(PMT(B4,B5,-B2),2)
A9:A176	A9	=SEQUENCE(B5*12,,0,1)
B9:B24	B9	=EDATE($B$1,A9)
C10:C24	C10	=MIN($B$6,F9+D10)
D10:D24	D10	=ROUND($B$4*F9,2)
F9	F9	=B2
F10:F24	F10	=F9-C10+D10-E10
Dynamic array formulas.

 

[eechristaylor]

Consider the following

Mortgage - Canadian.xlsm
	A	B	C	D	E	F
1	Start Date	1-Jan-23
2	Principal	100,000.00
3	Rate	6.00%
4	Monthly	0.50%
5	Term	14
6	Payment	$7,413.61
7
8	Payment Num	Date	Payment	Interest	Extra Payments	Balance
9		1-Jan-23				100,000.00
10	1	1-Feb-23	7,413.61	500.00		93,086.39
11	2	1-Mar-23	7,413.61	465.43		86,138.21
12	3	1-Apr-23	7,413.61	430.69		79,155.29
13	4	1-May-23	7,413.61	395.78		72,137.46
14	5	1-Jun-23	7,413.61	360.69		65,084.54
15	6	1-Jul-23	7,413.61	325.42		57,996.35
16	7	1-Aug-23	7,413.61	289.98		50,872.72
17	8	1-Sep-23	7,413.61	254.36		43,713.47
18	9	1-Oct-23	7,413.61	218.57		36,518.43
19	10	1-Nov-23	7,413.61	182.59		29,287.41
20	11	1-Dec-23	7,413.61	146.44		22,020.24
21	12	1-Jan-24	7,413.61	110.10		14,716.73
22	13	1-Feb-24	7,413.61	73.58		7,376.70
23	14	1-Mar-24	7,413.58	36.88		0.00
24	15	1-Apr-24	-0.00
1cc

Cell Formulas
Range	Formula
B4	B4	=B3/12
B6	B6	=ROUND(PMT(B4,B5,-B2),2)
A9:A176	A9	=SEQUENCE(B5*12,,0,1)
B9:B24	B9	=EDATE($B$1,A9)
C10:C24	C10	=MIN($B$6,F9+D10)
D10:D24	D10	=ROUND($B$4*F9,2)
F9	F9	=B2
F10:F24	F10	=F9-C10+D10-E10
Dynamic array formulas.

Thanks but I am not looking at the data in this way all i am only looking at the monthly payment as below:

Mo 13

Mo 14

Mo 15

Mo 16

$7,414

$7,414

$7,414

$7,414

I need Month 15 and 16 to show as "0"

 

[Dave Patton]

Please post your information with the forum's tool named XL2BB.

 

[Dave Patton]

Mortgage - Canadian.xlsm
	P	Q
46	$7,413.61	$7,413.61
47
1cc

Cell Formulas
Range	Formula
P46	P46	=PMT(0.06/12,14,-$C52)
Q46	Q46	=LET(s,SUM($P$46:P46),p,PMT(0.06/12,14,-$C52),IF((s+p)<$C$52,p,MAX(0,($C$52-s))))

What do you want to show with the last payment?
Compare this with my first post.

 

[Dave Patton]

No feedback or information.
Try either suggestion.

Mortgage - Canadian.xlsm
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
46	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61
47	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61	$7,413.61
48
1cc

Cell Formulas
Range	Formula
P46:AC46	P46	=LET(s,SUM(O$46:O46),p,ROUND(PMT(0.06/12,14,-$C52),2),IF((s+p)<$C$52,p,MAX(0,(($C$52-s-CUMIPMT(0.06/12,14,$C$52,1,13,0))*(1+0.06/12)))))
P47:AC47	P47	=(COLUMNS($P$46:P46)<15)*PMT($B$46/12,$C$46,-$C$52)