Calculate interest per month (to include exchange rates)

From | End | Principal | %
02-Jun-09 | 18-Jun-09 | 35000 | 0.015
19-Jun-09 | 23-Aug-09 | 25000 | 0.013
24-Aug-09 | 31-Dec-09 | 5000 | 0.017

Current practice, to find out total for Jun, I need to manually track down 30-Jun-09 as end of the month. First count the interest from 02-Jun-09 to 18-Jun-09 (35000 * 17/365 days * 0.015). Later count interest from 19-Jun-09 to 30-Jun-09 (25000 * 13/365 days * 0.013). As such, two separate calculation needed to find out the total for Jun.

While for Jul total = 25000 * 30/365 * 0.013
For Aug total = I need to repeat the calculation method as in Jun

It has been time consuming to manually track down the days in certain month.

I wonder if there's a formula available to auto calculate total for each month from Jun up to Dec.

Thanks.

Hi Alv, and welcome to the forum.

You should be able to calculate each month's interest with a formula based on one given by Daddylonglegs in this thread: http://www.excelforum.com/excel-work...-end-date.html

See the attached spreadsheet. I created a column for each of the months (F:Q) and a totals column in E. The formula used in F2:Qnn is the same (just put into F2, then copy to the rest).

=$C2*$D2*(MAX(0,MIN($B2,EOMONTH(F$1,0))-MAX($A2,F$1)+1)/365)

Hopefully this setup will work for you.

I am thinking to include currency exchange rates into the interest calculation. I updated the exchange rates into Sheet2. Exchanges rates could be updated anytime for certain period.

To quote example as per the excel file attached:
Interest calculation for each month for the period of 19-Jun-09 to 23-Aug-09
Jun = 10.68
Jul = 27.60
Aug = 20.48

Existing formula =$C3*$D3*(MAX(0,MIN($B3,EOMONTH(K$1,0))-MAX($A3,K$1)+1)/365)

During the same period, currency exchanges rates are updated 3 times:
02-Jun-09 to 19-Jul-09 = 1.222
20-Jul-09 to 18-Aug-09 = 1.245
19-Aug-09 to 30-Nov-09 = 1.340

The result suppose to be:
Jun = 10.68 * 1.222 (as only 1 exchange rates for this period)
Jul = 2 different exchanges rates for this period, as such, first 19 days of 27.60 * 1.222 while remaining days * 1.245

I wonder if possible to incorporate this currency exchanges calculation into the existing formula.

Or if this already exceed Excel capabilities?

Thanks.