Excel issue

Occasional Contributor

Dears,

Have a greet day,

i have an issue with an amount i need to divide it into smaller amount every quarter after deduct the down payment Ex)

Total amount 1,000,000 

Jan - feb - march - Apr  ,etc

i have to put amount on jan and skip feb and march and put the second amount in april Etc, till consuming the total amount.

 

Thanks in advance

11 Replies

@Hussein_Elsayed1605 

 

You have not provided sufficient details for an intelligent solution.

 

Re: ``after deduct the down payment Ex) Total amount 1,000,000 [....] i have to put amount on jan``

 

Is 1,000,000 the amount before or after the down payment?

 

Is the "amount on jan" the down payment?

 

Usually, the down payment is irrelevant.

 

Re:  ``skip feb and march and put the second amount in april Etc``

 

Without limits, one solution is to "put" $1 in April, $1 in July, $1 in Oct etc for nearly 1 million quarters.

 

Ridiculous, I know.  The point is:  without some constrains or additional information, there are infinite solutions.

 

What do the amount 1,000,000 and "down payment" represent?  Why are you making payments every quarter?  For example, is this a loan?  Is it an asset value that you are depreciating?  Over what period of time do you want to decrease the amount?  Is there "interest" involved?  Etc, etc, etc.

 

If this is a loan, please specify:  the amount of the loan (1,000,000?), the rate of interest (preferrably quarterly; otherwise annually); the number of quarters over which to amortize the loan (i.e. pay interest and decrease the principal).  And how should we determine the amount paid in Jan?  Is that even relevant, if the loan amount is $1,000,000?

 

 

@Joe User 

Thank you Mr. Joe for your quick reply.

Just sales figures (projected). If I have a unit will be sold over 10 years quarterly payment and there is a down payment xx will deduct from the total unit value and the rest will be divided over the period. How can I do this with a formula

 

N.B: the time line will be monthly ? jan - feb - march, Etc.

I hope you got my issue

 

Thanks

@Hussein_Elsayed1605 

=IF(OR(A7="january",A7="april",A7="july",A7="october"),($B$3-$B$4)/($B$5*4),"")

Does this return your expected result?

loan distribution.JPG 

Dears,

Attached is the sample of my issue, i hope find a formula to help get the target as shown in the attached.

 

Thanks in advance

@Hussein_Elsayed1605 

 

Thanks for attaching an example file that demonstrates the result that you are looking for.  That is the quickest way to get what you need.

 

The following shows one approach.  Click the image to enlarge.  See the attached Excel file for details.

 

JoeUser_1-1665992206273.png

 

B8:   =IF(AND(1<=COLUMN(B2)-$B$14, COLUMN(B2)-$B$14<=$B$12, MOD(COLUMN(B2)-$B$14, 3)=0), $B$13, 0)
B11:  =IFERROR(INDEX(A4:JI4,B14)-INDEX(A6:JI6,B14), 0)
B13:  =IFERROR(B11/INT(B12/3), 0)
B14:  =IFERROR(MATCH(TRUE,INDEX(ISNUMBER(A6:JI6),1,0),0), 0)

 

 

 

The formulas in B11, B13 and B14 are for convenience and efficiency.

 

However, I believe that the additional data in B12 (payment term in months after the down payment) is necessary.

 

The formula in B14 determines the column number of the down payment.  The use of INDEX(ISNUMBER(A6:JI6),1,0) is a trick to avoid needing to array-enter the formula.  Effectively, it is ISNUMBER(A6:JI6).

 

FYI, I made some subtle changes to the cell formats, primarily so that zero appears to be blank.

 

Also, you had a mysterious unexplained value in JJ6.  I changed it to =SUM(B6:JI6), which is similar to JJ4 and JJ8.

 

@Hussein_Elsayed1605 

 

In F12:

 

=LET(Start, MATCH(9.99999999999999E+307, $A$4:$DJ$4), Total, INDEX($A$4:$DJ$4, Start), DownPayment, INDEX($A$6:$DJ$6, Start), Payment, (Total-DownPayment)/36, Payment*(MOD(COLUMN()-Start,3)=0))

 

Fill to the right.

@Hans Vogelaar 

@Joe User 

Thank you for your support, 

attached is the full version that include the sales figures per month, i couldn't apply the formula provided.

as i have many sales figures in a different months and need your usual support to advice me about the proper formula to divide the amount after excluding the down payment over the given year.

 

Thanks

@Hussein_Elsayed1605 

You have lost me completely.

@Hans Vogelaar 

@Joe User 

I am very sorry for the misunderstanding of my question. I just wanted to help me in making an equation on excel on a form with all the data attached to it, but I cannot modify the equation or make an equation in a simpler way to reach the desired result.

 

I am sorry again for the miss-leading of my question again.

 

Thanks in advance

@Hans Vogelaar 

@Joe User 


I am very sorry for the misunderstanding of my question. I just wanted to help me in making an equation on excel on a form with all the data attached to it, but I cannot modify the equation or make an equation in a simpler way to reach the desired result.

 

Sorry again for the misleading of my question.

 

Thanks