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...
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!
