Dynamic formulas for Prepayment Table

[JEB85]

Hi,

I'm trying to come up with a formula that will calculate the expense for the period, based on a start and end date for a service, a rate per day, and the various month end dates.

In this case, the rate per day would be £55.56 which would need to be expensed over the 02/05/2022 - 30/07/2022 with the first month end date being 23/04/2022, the second month end date being 28/05/2022 etc.

I have manually keyed in what the values should be each month, i'm just looking for a formula i can use to more efficiently populate the table?

The days at the bottom of the table are just for reference.

Amount

Start Date

End Date

Days

Rate per Day £

23/04/2022​

28/05/2022​

25/06/2022​

23/07/2022​

27/08/2022​

24/09/2022​

22/10/2022​

Total

5,000​

02/05/2022​

30/07/2022​

90​

55.56​

0​

1,500​

1,556​

1,556​

389​

5,000​

Days

27​

28​

28​

7​

90​

Thanks

 

[JEB85]

Have solved now, thanks