Monthly payment convert to daily payment

[JDJong]

I have a few spreadsheets with monthly payment. but the start date and end date falls in certain days of the month, so i would like to have the exact daily payment instead of the monthly payment since the Dates include more than half of the month:

Here is a list of spread sheet

[TABLE="width: 236"]
<tbody>[TR]
[TD][/TD]
[TD]Monthly payment[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD="align: right"]16-2-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Date[/TD]
[TD="align: right"]19-5-2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-2-2013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-4-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-5-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-6-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-7-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-8-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-9-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-11-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-12-2013[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-2-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-4-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-5-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-6-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-7-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-8-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-9-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-11-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-12-2014[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-2-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-4-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-5-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-6-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-7-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-8-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-9-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-11-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-12-2015[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-2-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-4-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-5-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-6-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-7-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-8-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-9-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-11-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-12-2016[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-2-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-4-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-5-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-6-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-7-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-8-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-9-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-10-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-11-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-12-2017[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2018[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-2-2018[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2018[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-4-2018[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-5-2018[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-6-2018[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-7-2018[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

Thanks in adv.

 

[jproffer]

The easiest way to get daily pmts from monthly pmts is to multiply by 12, then divide that number by 365. Without more information, that's my best guess as to what you're looking for.

So in your case, (50000 x 12)/365 = 1643.84 per day.

Something tells me that's not what you're wanting though. Maybe some sample results along with the above would help.

 

[JDJong]

Hi,

I tried this formula to get the days that are valid within the month and falls in the start date and end date, but it doesn't seem to work.

=MAX(0, MIN(DateEnd, EOMONTH(A6, 0)) + 1 - MAX(DateBeg, A6))

Anybody please advise.

 

[JDJong]

since the start date is 16-2-2013. therefore from 17-2 to 28-2 is still valid for payment.
same goes for 20-5-2018 to 31-05-2018 is out of the end date. that is not valid for payment.