calculate interest between two dates

[Arvind Khanna via OfficeKB.com]

i had a unique problem. i have to calculate interest on some amount with
relation to some period of fiscal year. for ex

interest on 100 from 1/7/2000 to 31/1/2005(dd/mm/yyyy)

when rate of interest is
1/4/2000 to 31/3/2001 is 12%
1/4/2001 to 31/3/2002 is 11%
1/4/2002 to 31/3/2003 is 10%
1/4/2003 to 31/3/2004 is 12%
1/4/2004 to 31/3/2005 is 8%

i want to enter the begning date and the end date and amount rest formula
has to do.

kindly help
Arvind

 

[Duke Carey]

from my response to you a week ago:
==============================================
For each row
1) subtract the earlier date from (1+ the later date) to get the # of days
at that interest rate
2) divide the number of days by the # of days in the year
3) multiply the result by the interest rate times the principal amount
4) sum your results
==============================================

your reply to that:

this does not solve my problem. i wish to enter the 1st date (7/5/2000) and
last date (today or any date ) and the amount on which interest is to be
calculated at simple rate.
==============================================

Sorry about what you WANT to do, but you still NEED to determine how many
days at each rate. In other words, you MUST do some work to get your answer