Forum Discussion

Hussein_Mohamed's avatar
Hussein_Mohamed
Brass Contributor
Jan 15, 2024
Solved

excel formula assist

Dears, Could you please assist regarding a formula to perform the result as i attached in a sample.   Thanks in advance
excel
mathetes's avatar
mathetes
Gold Contributor
Jan 15, 2024
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.
  • Hussein_Mohamed's avatar
    Hussein_Mohamed
    Brass Contributor
    Jan 15, 2024

    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

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Jan 15, 2024

      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)
)

       

         

      • Hussein_Mohamed's avatar
        Hussein_Mohamed
        Brass Contributor
        Jan 15, 2024
        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)
  • Hussein_Mohamed's avatar
    Hussein_Mohamed
    Brass Contributor
    Jan 15, 2024

    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

Resources