Quarterly Interest Calculating Formula

1. Quarterly Interest Calculating Formula

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

2. Re: Quarterly Interest Calculating Formula

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

3. Re: Quarterly Interest Calculating Formula

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

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

4. Re: Quarterly Interest Calculating Formula

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)...

5. Re: Quarterly Interest Calculating Formula

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).

6. Re: Quarterly Interest Calculating Formula

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....

7. Re: Quarterly Interest Calculating Formula

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

8. Re: Quarterly Interest Calculating Formula

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.

9. Re: Quarterly Interest Calculating Formula

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

10. Re: Quarterly Interest Calculating Formula

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1