Calculate quarterly paid interest for days base deposit

[abmati]

Hi friends, to find the maturity amount of a fix deposit we use the following formula:
A = P x (1 + r/n) ^ n*t
“A” =maturity amount.
“P” = investment amount.
“r” = annual interest rates.
“n” = the number of times the interest is compounded. (For instance, n is equal to 4 for quarterly compounding, SBI using it)
“t” = the investment tenure.
If we put a fix deposit Rs. 1,00,000 (P) for 2 years (t), @7.00% (r), in SBI, FD interest is payable quarterly, so n = 12/3 = 4
So A = 100000(1+7%/4)^4*2 = 214,372
Now, my question is if we keep the same fix deposit for 400 days tenure, @ 7.1%, how we calculate the maturity amount
Please help me to find the formula for the same.
Thanks...

 

[Cubist]

=FV(7%/4,400/91,-100000,0,0)

 

[Dave Patton]

What do you mean by fix deposit.

If there is only 1 deposit and no quarterly payments, consider the following:

T240203a.xlsm
	A	B	C
2	7.00%	100,000.00	$107,924.07
1h

Cell Formulas
Range	Formula
C2	C2	=FV(A2/4,400/91,0,-B2,0)

 

[abmati]

What do you mean by fix deposit.

If there is only 1 deposit and no quarterly payments, consider the following:

T240203a.xlsm
	A	B	C
2	7.00%	100,000.00	$107,924.07
1h

Cell Formulas
Range	Formula
C2	C2	=FV(A2/4,400/91,0,-B2,0)

Thanks Dave Patton,
Fix deposit means a deposit that you can keep in a bank for a fix period of time and can't break it before the tenor of deposit. If you do break it, will loose 0.5% or 1.0% depend on the amount of deposit. It's ok, I know if no quarterly payable interest. But my bank created the fix deposit of 100,000 @ 7.1% and shows maturity amount 108,022 for 400 days. They calculate the compound interest added every quarter (3 monthly). So I need the formula for that.

 

[abmati]

I found the solution, but indirectly. Tenor is 400 days, so first we calculate the compound interest for a year (365 days) then add the interest of remaining 35 days (400-365) on the new amount. So the calculations would be as under:
a) maturity amount after a year = 100000(1+7.1%/4)^4*1 = 107291.28
b) now interest for 35 days = 107291*7.1%/365*35 = 730.46
c) Maturity amount for 400 days = 107291.28 + 730.46= 108,021.75 -> 108,023
which is matching with the Bank's calculations.
We can combined these 2 formulas:
t1 = int(days/365)
t2 = Deposit Tenor - t1
A = (P x (1 + r/n) ^ n*t1) +B
B = A * i / 365 * t2

 

[Dave Patton]

N.B. You changed the rate to 7.1%

With Excel

T240203a.xlsm
	A	B	C	D
4	Rate	Amount	Days
5	7.10%	100,000.00	400	$108,021.75
1h

Cell Formulas
Range	Formula
D5	D5	=LET(y,FV(A5/4,INT(C5/365)*4,0,-B5,0),y+(y*(C5-365)*A5/365))

 

[Cubist]

=100000*(1+7.1%/4)^(400/91)

 

[abmati]

N.B. You changed the rate to 7.1%

With Excel

T240203a.xlsm
	A	B	C	D
4	Rate	Amount	Days
5	7.10%	100,000.00	400	$108,021.75
1h

Cell Formulas
Range	Formula
D5	D5	=LET(y,FV(A5/4,INT(C5/365)*4,0,-B5,0),y+(y*(C5-365)*A5/365))

Yes, % is 7.1%, that is fine. it's ok for 400 days.
So we can put the formula if days >365,
=LET(y,FV(A5/4,INT(C5/365)*4,0,-B5,0),y+(y*(C5-(INT(C5/365)*365))*A5/365))
But this formula is still not valid if the difference of days more than quarter 1,2 or 3 (+90) as compound interest for the quarter(s) to be added.

Thanks Dave Patton

 

[Dave Patton]

Please post an example of what you mean.
N.B. You can post a concise example with the forum's tool named XL2BB.

 

[vsuram]

N.B. You changed the rate to 7.1%

With Excel

T240203a.xlsm
	A	B	C	D
4	Rate	Amount	Days
5	7.10%	100,000.00	400	$108,021.75
1h

Cell Formulas
Range	Formula
D5	D5	=LET(y,FV(A5/4,INT(C5/365)*4,0,-B5,0),y+(y*(C5-365)*A5/365))

thats nice. This is for completed year and remaiining days.
Now, likewise can we have a formula to calculate maturity amount, for completed quarters between two given dates; compound interest for completed quarters and simple interest for the remaining days