Forum Discussion
Excel Date calculation formula
Good Morning, I'm new to this forum. I have an intermediate knowledge of Excel (Mac Office 365) and need help with a date formula. Basically, it goes like this: I have to create a payment schedule. I know the date the first payment will be made, and the date the last payment will be due. In between, I will either have 2, 3, or 4 payments. I would want the formula to take the first and last payment dates, and populate the cells in between with the due dates for the other payments. It would look something like this (total 6 payments):
Due Date Amount
Grand Total 50,000
1st Deposit. 7/20/22 (20% of G.Total)
2nd Deposit Date? (30%)
3rd Deposit. Date? (25%)
4th Deposit. Date? (10%)
5th Deposit Date? (10%)
Final Payment. 7/20/24. remainder?
Thanks!
MTMTMTM
5 Replies
Try using the RandArray function in Microsoft Office 365 or 2021 to the the random dates.
Deposit Due Date Amount Total Amount Deposit Rate 1st 7/20/2022 $10,000.00 $50,000.00 1st 20% 2nd 10/14/2022 $15,000.00 2nd 30% 3rd 8/11/2023 $12,500.00 3rd 25% 4th 10/7/2023 $5,000.00 4th 10% 5th 1/13/2024 $5,000.00 5th 10% Final 7/20/2024 $2,500.00 Final 5% $50,000.00 100% Below are the formulae. You can edit them to suit your needs.
Deposit Due Date Amount Total Amount Deposit Rate 1st 44762 =SUM(INDEX($H$2:$H$7,MATCH(A2,$G$2:$G$7,0))*$E$2) 50000 1st 0.2 2nd 44848 =SUM(INDEX($H$2:$H$7,MATCH(A3,$G$2:$G$7,0))*$E$2) 2nd 0.3 3rd 45149 =SUM(INDEX($H$2:$H$7,MATCH(A4,$G$2:$G$7,0))*$E$2) 3rd 0.25 4th 45206 =SUM(INDEX($H$2:$H$7,MATCH(A5,$G$2:$G$7,0))*$E$2) 4th 0.1 5th 45304 =SUM(INDEX($H$2:$H$7,MATCH(A6,$G$2:$G$7,0))*$E$2) 5th 0.1 Final 45493 =SUM(INDEX($H$2:$H$7,MATCH(A7,$G$2:$G$7,0))*$E$2) Final =$H$8-SUM(H2:H6) =SUM(C2:C7) 1 Note:
Because the i used the RandArray function the dates keeps changing so you can copy the dates and paste as values in the same cells or you can even use macro to do that.
Also, remember to format the Due dates column as date.
Other contributions or inputs are welcome
Hope you find this helpful
Thanks
@DonShearer
Please below is the formula for the Due Dates using RandArray function.
For the Due Dates column as date
Deposit Due Date Amount Total Amount Deposit Rate 1st 44762 =SUM(INDEX($H$2:$H$7,MATCH(A2,$G$2:$G$7,0))*$E$2) 50000 1st 0.2 2nd =SORT(RANDARRAY(COUNTA(A3:A6),1,B2,B7,TRUE)) =SUM(INDEX($H$2:$H$7,MATCH(A3,$G$2:$G$7,0))*$E$2) 2nd 0.3 3rd =SUM(INDEX($H$2:$H$7,MATCH(A4,$G$2:$G$7,0))*$E$2) 3rd 0.25 4th =SUM(INDEX($H$2:$H$7,MATCH(A5,$G$2:$G$7,0))*$E$2) 4th 0.1 5th =SUM(INDEX($H$2:$H$7,MATCH(A6,$G$2:$G$7,0))*$E$2) 5th 0.1 Final 45493 =SUM(INDEX($H$2:$H$7,MATCH(A7,$G$2:$G$7,0))*$E$2) Final =$H$8-SUM(H2:H6) =SUM(C2:C7) 1 Deposit Due Date Amount Total Amount Deposit Rate 1st 7/20/2022 $10,000.00 $50,000.00 1st 20% 2nd 9/8/2022 $15,000.00 2nd 30% 3rd 4/20/2023 $12,500.00 3rd 25% 4th 8/7/2023 $5,000.00 4th 10% 5th 5/12/2024 $5,000.00 5th 10% Final 7/20/2024 $2,500.00 Final 5% $50,000.00 100% - This is great, so helpful, thank you so much!! Very generous response, most grateful!
If you want equally spaced payments, then with 7/20/2022 in B3 and 7/20/2024 in B8, enter the following into B4 and copy into B5:B7:
=ROUND($B$3 + ROWS($B$4:B4)*($B$8-$B$3)/5, 0)
Format appropriately. My results:
-----
PS.... You did not ask for help with calculating payment amounts, but now I see that you wrote "remainder?". Try the following.
- THIS is awesome, thank you so much for the more than thorough response. First time in this forum, this is great!
