Hi Tom,

You should be able to calculate daily interest with monthly payments by using the **CUMIPMT** function. It's functionally identical to the IPMT function, but with the added ability to return the cumulative amount paid between any two periods.

You could use CUMIPMT to calculate a 365-period loan (or however many days the loan is for) and then use its arguments to return the cumulative values for each month.

For example, to get the cumulative daily interest paid in the first 30 days of a 1 year loan for $100,000, you could use the following formula:

**=CUMIPMT(3%/365,365,100000,1,30,0)**

Unfortunately this method won't apply very accurately to a loan that spans several years because years can have an inconsistent number of days, making accurately calculating the interest rate per period very difficult. The only way I can see to create an accurate calculation of daily interest is to know exactly how many days (periods) the loan covers and what the interest rate is for each period.

You could calculate this assuming that years are 365 days long, but I think it's unlikely that your bank is doing this. It may be worth querying the interest payments with your loan vendor as they may be able to supply you with the number of periods and interest rates that they are using.

It's also possible that rounding may be a factor - they may be rounding up the interest rate for each period, for example.

You can find our video lesson explaining the PMT function in: Expert Skills Lesson 3-3 Create an Excel PMT function using the Insert Function Dialog.

For rounding, see the video lesson: Essential Skills Lesson 4-3: Format numbers using built-in number formats.

You may also be interested to look at some other threads from a user creating amortization schedules, which you can find here and here.

I'm sorry I wasn't able to provide an 'instant fix', but I hope this gives you a starting point to work from.

Please feel free to reply, and if you are able to find a way to calculate the daily interest rate for the loan I can provide assistance with integrating the CUMIPMT function to calculate the interest.