Excel Date calculation formula

New Contributor

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

@MTMTMTM 

 

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:

 

JoeUser_1-1657999474384.png

 

-----

PS.... You did not ask for help with calculating payment amounts, but now I see that you wrote "remainder?".  Try the following.

 

JoeUser_2-1658000314145.png

 

 

@MTMTMTM 

 

Try using the RandArray function in Microsoft Office 365 or 2021 to the the random dates.

DepositDue DateAmount    Total Amount DepositRate
1st7/20/2022$10,000.00    $50,000.00 1st20%
2nd10/14/2022$15,000.00      2nd30%
3rd8/11/2023$12,500.00      3rd25%
4th10/7/2023$5,000.00      4th10%
5th1/13/2024$5,000.00      5th10%
Final7/20/2024$2,500.00      Final5%
  $50,000.00       100%
           

Below are the formulae. You can edit them to suit your needs.

 

DepositDue DateAmount Total Amount DepositRate
1st44762=SUM(INDEX($H$2:$H$7,MATCH(A2,$G$2:$G$7,0))*$E$2) 50000 1st0.2
2nd44848=SUM(INDEX($H$2:$H$7,MATCH(A3,$G$2:$G$7,0))*$E$2)   2nd0.3
3rd45149=SUM(INDEX($H$2:$H$7,MATCH(A4,$G$2:$G$7,0))*$E$2)   3rd0.25
4th45206=SUM(INDEX($H$2:$H$7,MATCH(A5,$G$2:$G$7,0))*$E$2)   4th0.1
5th45304=SUM(INDEX($H$2:$H$7,MATCH(A6,$G$2:$G$7,0))*$E$2)   5th0.1
Final45493=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

DepositDue DateAmount Total Amount DepositRate
1st44762=SUM(INDEX($H$2:$H$7,MATCH(A2,$G$2:$G$7,0))*$E$2) 50000 1st0.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)   2nd0.3
3rd =SUM(INDEX($H$2:$H$7,MATCH(A4,$G$2:$G$7,0))*$E$2)   3rd0.25
4th =SUM(INDEX($H$2:$H$7,MATCH(A5,$G$2:$G$7,0))*$E$2)   4th0.1
5th =SUM(INDEX($H$2:$H$7,MATCH(A6,$G$2:$G$7,0))*$E$2)   5th0.1
Final45493=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

 

DepositDue DateAmount Total Amount DepositRate
1st7/20/2022$10,000.00 $50,000.00 1st20%
2nd9/8/2022$15,000.00   2nd30%
3rd4/20/2023$12,500.00   3rd25%
4th8/7/2023$5,000.00   4th10%
5th5/12/2024$5,000.00   5th10%
Final7/20/2024$2,500.00   Final5%
  $50,000.00    100%

@DonShearer 

This is great, so helpful, thank you so much!! Very generous response, most grateful!
THIS is awesome, thank you so much for the more than thorough response. First time in this forum, this is great!