Excel, trying to split rent for the number of days in a month, per month for the whole year

Question

So I am trying to calculate rent for each month. The problem I am facing is with the fact that starting date and ending date could be in the middle of a month.

For example:

Rent = 10,000
Starting date = 15/04/2020
Ending date = 10/10/2020

I want my excel Sheet to split accordingly as below: First sample row of the excel

In my excel sheet, I have different amounts for rent and various start/end dates. This is what I have tried but failed miserably:

> =$F3/(12*DAY(EOMONTH(H$1,0)))*(IF(MONTH($C3)<=MONTH(H$1),IF(MONTH($C3)=MONTH(H$1),(EOMONTH($D3,0)-$D3),DAY(EOMONTH(H$1,0))),0))

Where F is where the RENT is, H1 is January, C3 is start date, D3 is end date. I'm hoping it to work when I autofill the formula across the whole sheet.

Please help!

@Hooded0ne I tried your formula for all examples in my sheet and there seems to be an issue for some cases:

1. When start date is 2019 and end date is 2020. This should only show rent till April.
2. Same case as above, December 2020 is only till the 14th, it should only calculate for the first 15 days

Blockquote

Answer 1

Does this work for you?

=MAX((MIN(EOMONTH(K1,0),$D$3)-MAX(K1,$C$3)+1)/DAY(EOMONTH(K1,0))/12*$F$3,0)

Edit: It should work for multiple years, if you have a specific example, please add it to your original post.