interest calculations

[vsuram]

cumulative time deposits in indian banks​

I am trying to develop a worksheet for cumulative term deposits. I seek a few clarifications, in the context of Indian banks
a. Mostly, the banks show "accumulated interest till date" on their inquiry screens. How is this computed as the rate is per annum and compounding is quarterly.
b. Banks also accept cumulative deposits for incomplete quarters too, like 400 days, 333 days etc. how is interest calculated for broken periods
c. Is interest compounded 3 months from date of deposit or at calendar quarters? if a deposit is opened on say 12th May is compounding done on 12/8, 12/11 etc or 30/6, 30/9 etc.
d. Tax is deducted at source on every 31/3 for the interest earned during the Financial Year. What do they do to the broken period if interest gets compounded at quarterly rests from date of
deposit.
e. For premature closures, a lower rate is applied for the entire period the deposit is run. How is TDS done in the earlier years at a higher rate adjusted when a lower rate of interest is being
paid?

 

[vsuram]

This is a calculator I am trying to develop.
To calculate maturity amount of a given principal, at given Rate of Interest , for a duration between any given two dates, when the frequency of compounding is quarterly, the steps are:
1. calculate the number of completed quarters and balance number of days (which are less than a quarter).
2. Calculate maturity amount using FV formula for completed quarters, and on this amount calculate interest for the balance number of days using ptr/100.
3. Final maturity is calculated by adding the two.
The crucial step is calculating completed quarters and balance days. In the following table I did it in a round about way. Calculatedno of months between the two dates, got quarters by dividing by 3, then found out the quarter end date by EDATE function, and got balance days by subtracting this from maturity date.

Is there a more elegant way to do this.

STDRMATCAL.xlsx
	A	B
1	STDR MAT CAL
2
3	Start date	13-04-2023
4	Mat Date	13-04-2025
5	Principal	3600000
6	ROI	8.90%
7	months	24
8	qrs	8
9	quarter end	13-04-2025
10	bal days	0
11	Mat Amount	4292985.83
12		4292986
Sheet1

Cell Formulas
Range	Formula
B7	B7	=DATEDIF(B3,B4, "m")
B8	B8	=INT(B7/3)
B9	B9	=EDATE(B3,3*B8)
B10	B10	=B4-B9
B11	B11	=LET(y,FV(B6/4,B8,0,-B5),y+(y*B6/365*B10))
B12	B12	=B11

 

[vsuram]

another eg with balance days

STDRMATCAL.xlsx
	A	B
1	STDR MAT CAL
2
3	Start date	13-04-2023
4	Mat Date	25-04-2025
5	Principal	3600000
6	ROI	8.90%
7	months	24
8	qrs	8
9	quarter end	13-04-2025
10	bal days	12
11	Mat Amount	4305547.22
12		4305547
Sheet1

Cell Formulas
Range	Formula
B7	B7	=DATEDIF(B3,B4, "m")
B8	B8	=INT(B7/3)
B9	B9	=EDATE(B3,3*B8)
B10	B10	=B4-B9
B11	B11	=LET(y,FV(B6/4,B8,0,-B5),y+(y*B6/365*B10))
B12	B12	=B11