IF formula

[Ali_Toronto]

Hello Everyone,



It’s my first time here, so I would like to thank everyone in advance for bearing with me and helping me out.



Here’s what I’m trying to do … in as simple terms as I can explain … the problem is much complex …



EXAMPLE:

Client has to pay $30,000 (or whatever)

He has 10 months to pay (or whatever)

His monthly payment amount is consistent

How do I calculate his last payment using a formula?



Since I can’t post a spread sheet – here’s how I have it setup



--- A3 TO B14 ---

TOTAL PAYMENT

BASE PAYMENT

PAYMENT 1

PAYMENT 2

PAYMENT 3

PAYMENT 4

PAYMENT 5

PAYMENT 6

PAYMENT 7

PAYMENT 8

PAYMENT 9

PAYMENT 10



--- B3 TO B7 ---

$30,000.00

$9,000.00

=B4

=B4

=IF(SUM($B$5:B6)<$B$3,$B$4,($B$3-(SUM($B$5:B6))))



--- B7 TO B14 --- DRAG AND FILL FORUMLA ABOVE



I’m usually pretty good at performing my own research for excel problems, but I’ve really been stuck on this one for a while – I’ve tried many different formulas, possibilities … but I can’t seem to figure it out ….



In advance, please be nice … I’m no expert at excel.

 

[Legacy 143009]

I didn't get it quite. Is it something like this? B5:

=($B$3-$B$4)/COUNTA(A$5:A14)

 

[jdellasala]

This includes 365 functions, but that was just to make it easier to put together. The important thing is the use of the PMT function:

Book1
	A	B
1	Total Amount:	30,000.00
2	Number of Payments:	4
3	Annual Interest Rate:	0
4	Payments:	7,500.00
5
6	Pmt 1:	7,500.00
7	Pmt 2:	7,500.00
8	Pmt 3:	7,500.00
9	Pmt 4:	7,500.00
Sheet2

Cell Formulas
Range	Formula
B4	B4	=PMT(B3,B2,-B1)
A6:A9	A6	="Pmt "&SEQUENCE(B2)&": "
B6:B9	B6	=SEQUENCE(B2,,B4,0)
Dynamic array formulas.

 

[GraH]

Hi,

Not sure I totally get it from your description. And I now see you say you are on Office 2016, but that might be stand-alone or 365 subscription.
Hesitated to go for PMT, but someone else already did it.

Book1
	A	B	C	D	E	F
1
2		To Pay	$ 30.000,00
3		Periods	10
4		Base Payments	$ 3.000,00
5
6		Payments	Amount	Remaining
7		Payment 1	$ 3.000,00	$ 27.000,00
8		Payment 2	$ 3.000,00	$ 24.000,00
9		Payment 3	$ 3.000,00	$ 21.000,00
10		Payment 4	$ 3.000,00	$ 18.000,00
11		Payment 5	$ 3.000,00	$ 15.000,00
12		Payment 6	$ 3.000,00	$ 12.000,00
13		Payment 7	$ 3.000,00	$ 9.000,00
14		Payment 8	$ 3.000,00	$ 6.000,00
15		Payment 9	$ 3.000,00	$ 3.000,00
16		Payment 10	$ 3.000,00	$ -
17
Sheet2

Cell Formulas
Range	Formula
C4	C4	=C2/C3
B7:B16	B7	="Payment "&SEQUENCE(C3)
C7:C16	C7	=C4*SEQUENCE(C3,,1,0)
D7:D16	D7	=C2-SUBTOTAL(109,OFFSET(C7,,,SEQUENCE(C3)))
Dynamic array formulas.

 

[Legacy 143009]

Hi,

Not sure I totally get it from your description. And I now see you say you are on Office 2016, but that might be stand-alone or 365 subscription.
Hesitated to go for PMT, but someone else already did it.

Book1
	A	B	C	D	E	F
1
2		To Pay	$ 30.000,00
3		Periods	10
4		Base Payments	$ 3.000,00
5
6		Payments	Amount	Remaining
7		Payment 1	$ 3.000,00	$ 27.000,00
8		Payment 2	$ 3.000,00	$ 24.000,00
9		Payment 3	$ 3.000,00	$ 21.000,00
10		Payment 4	$ 3.000,00	$ 18.000,00
11		Payment 5	$ 3.000,00	$ 15.000,00
12		Payment 6	$ 3.000,00	$ 12.000,00
13		Payment 7	$ 3.000,00	$ 9.000,00
14		Payment 8	$ 3.000,00	$ 6.000,00
15		Payment 9	$ 3.000,00	$ 3.000,00
16		Payment 10	$ 3.000,00	$ -
17
Sheet2

Cell Formulas
Range	Formula
C4	C4	=C2/C3
B7:B16	B7	="Payment "&SEQUENCE(C3)
C7:C16	C7	=C4*SEQUENCE(C3,,1,0)
D7:D16	D7	=C2-SUBTOTAL(109,OFFSET(C7,,,SEQUENCE(C3)))
Dynamic array formulas.

aww.. yess.. He was asking for the remaining

 

[GraH]

For version 2016 with legacy functions

Book1
	A	B	C	D	E
1
2		To Pay	$ 30.000,00
3		Periods	10
4		Base Payments	$ 3.000,00
5
6		Payments	Amount	Remaining
7		1	$ 3.000,00	$ 27.000,00
8		2	$ 3.000,00	$ 24.000,00
9		3	$ 3.000,00	$ 21.000,00
10		4	$ 3.000,00	$ 18.000,00
11		5	$ 3.000,00	$ 15.000,00
12		6	$ 3.000,00	$ 12.000,00
13		7	$ 3.000,00	$ 9.000,00
14		8	$ 3.000,00	$ 6.000,00
15		9	$ 3.000,00	$ 3.000,00
16		10	$ 3.000,00	$ -
17				$ -
18				$ -
19				$ -
20				$ -
21				$ -
22				$ -
23				$ -
24				$ -
25				$ -
26				$ -
27				$ -
28				$ -
29				$ -
30				$ -
31
Sheet2

Cell Formulas
Range	Formula
C4	C4	=C2/C3
B7:B30	B7	=IF(ROW()-ROW($B$6)<=$C$3,N(B6)+1,"")
C7:C30	C7	=IF(B7="","",$C$4)
D7:D30	D7	=$C$2-SUM($C$7:C7)

 

[GraH]

aww.. yess.. He was asking for the remaining

I can't count the number of times I actually misunderstood the thread... Glad to see it's not only happening to me

 

[jdellasala]

@GraH , Yea, it happens!

Book1 (version 2).xlsb
	A	B	C	D
1	Total Amount:	30,000.00
2	Number of Payments:	5
3	Annual Interest Rate:	0
4	Payments:	6,000.00
5
6		Pmt Amt	Remaining	Alt Remaining
7	Pmt 1:	6,000.00	24,000.00	24,000.00
8	Pmt 2:	6,000.00	18,000.00	18,000.00
9	Pmt 3:	6,000.00	12,000.00	12,000.00
10	Pmt 4:	6,000.00	6,000.00	6,000.00
11	Pmt 5:	6,000.00	0.00	0.00
Sheet2

Cell Formulas
Range	Formula
B4	B4	=PMT(B3,B2,-B1)
A7:A11	A7	="Pmt "&SEQUENCE(B2)&": "
B7:B11	B7	=SEQUENCE(B2,,B4,0)
C7:C11	C7	=BYROW(B7#, LAMBDA(Rw, $B$1-SUM(B7:Rw)))
D7:D11	D7	=$B$1-SUM($B$7:B7)
Dynamic array formulas.

Included a second non 365 solution as well.