Formula for Total Interest earned in a financial year on Fixed Deposits on date of opening and maturity.

[hackforensic]

I opened multiple Fixed Deposits/Term Deposit on different dates in financial year 2012 - 13 for different terms. Interest is compounded quarterly and paid on maturity. Is there a way I can calculate interest earned on all FD's for a given financial year which ends on 31st March.


Example


Fixed deposit #1:


Amount - Rs. 1,00,000
Rate - 8.75%
Term - 1 year 16 days
Date of Opening - 10 June 2012
Date of Maturity - 26 June 2013



Fixed deposit #2:


Amount - Rs. 1,00,000
Rate - 8.75%
Term - 33 Months
Date of opening - 16 August 2012
Date of Maturity - 16 May 2015



Fixed deposit #3:


Amount - Rs. 1,00,000
Rate - 8.75%
Term - 28 Months
Date of opening - 18 Oct 2012
Date of Maturity - 18 Feb 2015



Fixed deposit #4:


Amount - Rs. 1,00,000
Rate - 8.75%
Term - 30 Months
Date of opening - 07 Jan 2013
Date of Maturity - 07 June 2015


Is there a formula in excel which will calculate compound interest for financial year 2012 - 13 based on dates of deposits?


What I mean is, instead of inputting 12 months, 2 years or such for term, a way to calculate term from date of opening and date of maturity and calculate the interest earned for whole quarters in a financial year which ends on 31st March.


As FD #1's first interest would be added on 10 Sept. second on 10 Dec, third on 10 March. So for financial year 2012-13, interest for 3 quarters should be accounted for even though the term of the deposit is 1 year and 16 days. Similarly for other FD's interest for whole quarter should be accounted for any given financial year plus remaining period if the FD is maturing in that financial year i.e. 1 quarter and 16 days interest in financial year 2013-14.


P.S.. I have attached excel sheet with all the data.. If more information is required please let me know.