Need help calculating days in month for interest expense

On a monthly basis we keep track of interest expenses. We have a file with Settlement Date and Maturity date and need to calculate how many days interest will be paid for the given month. ExAMPLE- Calculate days of interest in May

Settle Date Maturity Date Number of Days In May
3/4/2016 5/05/2016 May 1-May 5 is 4 days

Is there a formula that can be used instead of manually counting days. Interest is calculated on a monthly basis. Thanks for your help in advance.

Does the DAY() function work for you? Something like:
Days In May: =DAY(5/05/2016)-1 =4

Hi, Thanks for the help. That works only when maturity is falling in the same month. For example is maturity was 6/2/16 then interest paid in may would be 31 days. Is there another formula to keep track.

You can subtract/add dates together. Just treat them like numbers. At the very base level, Excel treats dates as numbers (going back to 1900). So if you said )(6/2/16 - 5/5/2016 + 1), the result would be 28.

If you need to find the beginning or end of a month, you can use EOMONTH() to get the end of the month and then add 1 to get the 1st of the next month.

I will try that but I don't think it will get me what I'm looking for. Basically even if the maturity roles into mid June I only want to know the number of days interest was paid in May since that's the working month which would be 31 days. And in June I would calculate the the days in June till maturity and etc

Can you upload a workbook with an example?

Sure here is an example assuming that we are calculating interest expense for the month of May only. I use to manually just do the math but a formula would make things much easier.

Maturity Date Number of days interest Paid in May
05/03/2016 2
05/05/2016 4
05/05/2016 4
05/06/2016 5
05/09/2016 8
05/19/2016 18
05/24/2016 23
05/31/2016 30
06/06/2016 31
06/06/2016 31
06/06/2016

See attached sheet

Thank you so much! this is very helpful! got to learn something new.

No problem! Remember to mark this post as SOLVED