Quarterly Interest Calculating Formula

**befriend_my** · 10-21-2009, 03:54 AM

Hi ,

I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.

But I want to make it compact , as d one I created is long enough.

A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest

Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?

Please let me know the formula , and help me to make my calculator a bit smart.
Regards

**mohitspamz** · 10-21-2009, 02:48 PM

Hey there!
Compound interest always confuses me with its wicked ways... i'll still give it a shot though...
For Quarterly Compunding...

A=P*(1+(R/400))^4n

So... Now U have P as ur initial Principal Amount (A3)
R is the ROI (E3)
n is the period... the tricky part... N is basically period in years... so dat is No. of days/365.. this is (D3/365)

So now u have a formula:

=A3*(1+(E3/400))^(4*(D3/365))

This is the total Amount due at the end of N days...

Hope this helps! (IF i got the formula correct :P)

Regards
Mohit

**befriend_my** · 10-22-2009, 06:41 AM

No sir,
This formula is not correct.its resulting in "0"

Any one please help..its not impossible for you friends for sure.

I want to add that interest can be for 1 day to any number of days.

**mohitspamz** · 10-22-2009, 08:04 AM

I tried on my sheet... works perfectly.... as long as A=P*(1+(R/400))^4n is the correct formula for interest compunded quarterly....
F3 calculates the total amount due after N days (from 0 days to wotever no. of days)...

**darkyam** · 10-22-2009, 09:05 AM

Is E3 the annual percentage rate or annual percentage yield? If the former, and it is multiplied quarterly, use =-FV(RATE(365,0,A3,-A3*(1+E3/4)^4),D3,0,A3). If the latter, use =-FV(RATE(365,0,A3,-A3*(1+E3)),D3,0,A3).

**befriend_my** · 10-22-2009, 10:44 AM

Originally Posted by darkyam

Is E3 the annual percentage rate or annual percentage yield? If the former, and it is multiplied quarterly, use =-FV(RATE(365,0,A3,-A3*(1+E3/4)^4),D3,0,A3). If the latter, use =-FV(RATE(365,0,A3,-A3*(1+E3)),D3,0,A3).

E3 is annual rate of interest , not yeild.
Will try now , hope it works....

**befriend_my** · 10-22-2009, 11:32 AM

Hi darkyam, I tried the annual rate of interest formula but it results in 8100 for 100 invested for 365 days @8%. How come such a big return.

The return r for 1st qtr=102.00
2nd qtr=104.04
3rd qtr=106.12
4th qtr {ie for a yr.}=108.24

Financial institutions calculates interest on 360 days a year base in india.

I m using =c4*e3%+c4
to calculate each quarter.
Where c4 is amount,
e3 is annual % of int i divided by 4.

I calculate on daily basis too.. Will send you that sheet as attachment tomorrow as m replying from mobile now.

Hope you will get better idea to help now..
Regards

**darkyam** · 10-22-2009, 11:36 AM

Works just fine for me. If India uses a 360 day year, adjust the 365 I used accordingly, but that's not the issue here.

**befriend_my** · 10-26-2009, 03:09 AM

Hi darkyam,
Thanks for helping....
Its solved....

**vinaradha** · 02-10-2012, 06:17 AM

The solution was more help full.

I have found one more simple solution as below.

Quarter Annual = P * (1 + R / 4) ^ (N * 4).

And

Annual = P * (1 + R) ^ N.

For each Quarter specific
= P * (1 + R / 4) ^ <NUMBER_OF_QUARTE>.

Hope this will be usefull.

Regards,
Thiru