excel formula assist

[Hussein_Mohamed]

Dears,

Could you please assist regarding a formula to perform the result as i attached in a sample.

Thanks in advance

[#2]

You've had nearly 60 views and no replies until this one. I suspect that the reason for this lack of response is that--though your request may be perfectly clear to you--we can't read your mind. You'd be far more likely to get a response if you spelled out what you're trying to do, what the context is, what those results are meant to mean. Otherwise you're in effect asking us to reverse engineer, without knowing the context and, perhaps more important, without the invested interest.

[#3]

@mathetes 

sorry for the confusion i have made.

I would like a payment plan with the following details: The first payment percentage will be determined at the beginning of the sales contract. The second payment, after a three-month interval, will have a specified percentage. The remaining amount will be divided equally over the designated number of years (in assum sheet), with payments made quarterly. Please note that the down payment percentage will be applicable from the start of the sales contract until the end of the specified sales years. The months between each quarter will have a payment value of zero.

i will attach the source file contain the original formula that i need to modify.

Thanks in advance

[#4]

@mathetes 

sorry for the confusion i have made.

I would like a payment plan with the following details: The first payment percentage will be determined at the beginning of the sales contract. The second payment, after a three-month interval, will have a specified percentage. The remaining amount will be divided equally over the designated number of years (in assum sheet), with payments made quarterly. Please note that the down payment percentage will be applicable from the start of the sales contract until the end of the specified sales years. The months between each quarter will have a payment value of zero.

i will attach the source file contain the original formula that i need to modify.

Thanks in advance

[#5]

@Hussein_Mohamed 

I think your first workbook might be a better example even if some of the details are not clear. I created this formula without seeing your revised workbook which might be a bit too granular for someone not familiar with the data set.

=LET(
    down_payment, down_payment_per * sales,
    contract_payment, contract_payment_per * sales,
    months, years * 4 - 1,
    quarterly_amt, (sales - (down_payment + contract_payment)) / months,
    quarterly_payments, EXPAND(quarterly_amt, , months, quarterly_amt),
    payment_arr, HSTACK(down_payment, contract_payment, quarterly_payments),
    resized_payment_arr, TOROW(EXPAND(payment_arr, 3, , 0), , 1),
    cols, COLUMNS(resized_payment_arr),
    dates, DATE(YEAR(start_date), MONTH(start_date) + SEQUENCE(, cols, 0), 1),
    VSTACK(dates, resized_payment_arr)
)

[#6]

Thanks alot , can i do this if i have a list of lines for sales (jan, feb, mar, etc) and let the date rang at header only (not repeated)

[#7]

@Hussein_Mohamed 

It's do-able. My concern with 12 months is for each month the final month that a payment is made is going to shift 1 to the right.

If the plan is 8 years and payments are made quarterly, can each of those months be paid off in 8 years if we follow the same pattern of: down payment (5% of sales), contract payment (10% of sales), quarterly payments?

[#8]

Of course, the formula will naturally shift to the right to accommodate different sales months. can the formula provided adapted to handle different sales months?

[#9]

@Hussein_Mohamed 

I created a Lambda to generate the full 12 months:

'PaySched(salesmonths,sales,downpayment,contractpayment,years)
=LET(
    n, COUNTA(salesmonths),
    payments, years * 4 - 1,
    width, years * 12 + (n + 2),
    start_date, MIN(salesmonths),
    dates, DATE(YEAR(start_date), MONTH(start_date) + SEQUENCE(, width, 0), 1),
    GetPayments, LAMBDA(a, v,
        LET(
            sales_amt, XLOOKUP(v, salesmonths, sales),
            down_amt, sales_amt * downpayment,
            contract_amt, sales_amt * contractpayment,
            remaining, sales_amt - (down_amt + contract_amt),
            quarterly_payment, remaining / payments,
            resized, EXPAND(EXPAND(quarterly_payment, , payments, quarterly_payment), 3, , 0),
            quarterly_arr, TOROW(resized, , 1),
            upfront, HSTACK(down_amt, 0, 0, contract_amt, 0, 0),
            padding, EXPAND(0, , MONTH(v) - 1, 0),
            all_payments, HSTACK(upfront, quarterly_arr),
            outcome, IF(MONTH(v) = 1, all_payments, HSTACK(padding, all_payments)),
            IFERROR(VSTACK(a, outcome), 0)
        )
    ),
    Payment_Schedule, REDUCE(dates, salesmonths, GetPayments),
    IF(
        ROWS(salesmonths) = ROWS(sales),
        HSTACK(VSTACK("", salesmonths), Payment_Schedule),
        "Please be sure the number of months selected equals the number of sales figures selected."
    )
)

[#10]

@Patrick2788 

Thank you Mr. Patrick for your valuable assist, when i add lines by modifying the range i notice the below, is it correct or i did something wrong as i have to add a forecasted sales till 2026 by month

Thank you

[#11]

Are you trying to find totals all the way across for each month? You may have to include the workbook.

[#12]

I don't need to show the total at the end of each month. I just want a breakdown of the installments per month

[#13 · Accepted solution]

@Hussein_Mohamed 

If you're looking to 'staircase' the payments like this:

The attached workbook will continue to do this even if you go beyond 12 months.

[#14]

@Patrick2788 

Thank you for such a wonderful contribution 

[#15]

You're welcome!

[#16]

@Patrick2788 

I would like to express my deep gratitude for your generosity and assistance in my task.

You are successfully facilitating a challenging task that took me a significant amount of time to accomplish