formulaTotal interest earned in a financial year on Term Deposits based on date of opening

[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 given financial year which ends on 31st March 2013.

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?

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.

[mahaveersomani]

will you provide all data in a sample workbook Please

so we can complete your worksheet with formula.......because it will be easy.

Regards
CA Mahaveer Somani
9785216211

[hackforensic]

Hi Mahaveer,

As per your request I have attached a worksheet with all the data.

What I'm after is the total interest earned each financial year

Interest earned every quarter is compounded.

[hackforensic]

Can anyone help me?