# IF formula



## Ali_Toronto (Dec 29, 2022)

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.


----------



## Flashbond (Dec 29, 2022)

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

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


----------



## jdellasala (Dec 29, 2022)

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:
Book1AB1Total Amount: 30,000.002Number of Payments: 43Annual Interest Rate: 04Payments: 7,500.0056Pmt 1: 7,500.007Pmt 2: 7,500.008Pmt 3: 7,500.009Pmt 4: 7,500.00Sheet2Cell FormulasRangeFormulaB4B4=PMT(B3,B2,-B1)A6:A9A6="Pmt "&SEQUENCE(B2)&": "B6:B9B6=SEQUENCE(B2,,B4,0)Dynamic array formulas.


----------



## GraH (Dec 29, 2022)

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.
Book1ABCDEF12To Pay$ 30.000,003Periods104Base Payments$   3.000,0056PaymentsAmountRemaining7Payment 1$   3.000,00$ 27.000,008Payment 2$   3.000,00$ 24.000,009Payment 3$   3.000,00$ 21.000,0010Payment 4$   3.000,00$ 18.000,0011Payment 5$   3.000,00$ 15.000,0012Payment 6$   3.000,00$ 12.000,0013Payment 7$   3.000,00$   9.000,0014Payment 8$   3.000,00$   6.000,0015Payment 9$   3.000,00$   3.000,0016Payment 10$   3.000,00$                -17Sheet2Cell FormulasRangeFormulaC4C4=C2/C3B7:B16B7="Payment "&SEQUENCE(C3)C7:C16C7=C4*SEQUENCE(C3,,1,0)D7:D16D7=C2-SUBTOTAL(109,OFFSET(C7,,,SEQUENCE(C3)))Dynamic array formulas.


----------



## Flashbond (Dec 29, 2022)

GraH said:


> 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.
> Book1ABCDEF12To Pay$ 30.000,003Periods104Base Payments$   3.000,0056PaymentsAmountRemaining7Payment 1$   3.000,00$ 27.000,008Payment 2$   3.000,00$ 24.000,009Payment 3$   3.000,00$ 21.000,0010Payment 4$   3.000,00$ 18.000,0011Payment 5$   3.000,00$ 15.000,0012Payment 6$   3.000,00$ 12.000,0013Payment 7$   3.000,00$   9.000,0014Payment 8$   3.000,00$   6.000,0015Payment 9$   3.000,00$   3.000,0016Payment 10$   3.000,00$                -17Sheet2Cell FormulasRangeFormulaC4C4=C2/C3B7:B16B7="Payment "&SEQUENCE(C3)C7:C16C7=C4*SEQUENCE(C3,,1,0)D7:D16D7=C2-SUBTOTAL(109,OFFSET(C7,,,SEQUENCE(C3)))Dynamic array formulas.


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


----------



## GraH (Dec 29, 2022)

For version 2016 with legacy functions
Book1ABCDE12To Pay$ 30.000,003Periods104Base Payments$   3.000,0056PaymentsAmountRemaining71$   3.000,00$ 27.000,0082$   3.000,00$ 24.000,0093$   3.000,00$ 21.000,00104$   3.000,00$ 18.000,00115$   3.000,00$ 15.000,00126$   3.000,00$ 12.000,00137$   3.000,00$   9.000,00148$   3.000,00$   6.000,00159$   3.000,00$   3.000,001610$   3.000,00$                -17  $                -18  $                -19  $                -20  $                -21  $                -22  $                -23  $                -24  $                -25  $                -26  $                -27  $                -28  $                -29  $                -30  $                -31Sheet2Cell FormulasRangeFormulaC4C4=C2/C3B7:B30B7=IF(ROW()-ROW($B$6)<=$C$3,N(B6)+1,"")C7:C30C7=IF(B7="","",$C$4)D7:D30D7=$C$2-SUM($C$7:C7)


----------



## GraH (Dec 29, 2022)

Flashbond said:


> 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 (Dec 29, 2022)

@GraH , Yea, it happens!
Book1 (version 2).xlsbABCD1Total Amount: 30,000.002Number of Payments: 53Annual Interest Rate: 04Payments: 6,000.0056Pmt AmtRemainingAlt Remaining7Pmt 1: 6,000.0024,000.0024,000.008Pmt 2: 6,000.0018,000.0018,000.009Pmt 3: 6,000.0012,000.0012,000.0010Pmt 4: 6,000.006,000.006,000.0011Pmt 5: 6,000.000.000.00Sheet2Cell FormulasRangeFormulaB4B4=PMT(B3,B2,-B1)A7:A11A7="Pmt "&SEQUENCE(B2)&": "B7:B11B7=SEQUENCE(B2,,B4,0)C7:C11C7=BYROW(B7#, LAMBDA(Rw, $B$1-SUM(B7:Rw)))D7:D11D7=$B$1-SUM($B$7:B7)Dynamic array formulas.
Included a second non 365 solution as well.


----------

