Forum Discussion
excel formula assist
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.
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
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)
)
- 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)
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?
- 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?