• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

query - calculating effective annual interest rate

M

Miss P

New Member
i need to calculate effective annual interest rate from a series of interest rates that are compounded on a daily basis

date rate
25-apr 7.83%
26-apr 7.76%
27-apr 7.32%

following step by step calculation, i get the rate as 7.64% but any way that excel can directly give me this numbers? assuming 365 days in a year.
 
Last edited by a moderator:
Hi ,

Excel does have a function called EFFECT ( which if you have Excel 2003 or earlier , needs the Analysis Toolpak to be installed ) , which returns the effective annual interest rate based on the number of compounding periods , but I am not able to understand the data you have posted ; probably if you can upload a sample file , it will help.

However , you say that following a step by step calculation , you get a particular rate ; are you talking about a manual calculation done using pen and paper , or are you talking about a calculation which you have done using Excel itself ? If it is Excel , then what is the advantage to be gained by replacing a series of calculation steps by one function ? If it is a series of manual calculations , then if you upload a file with the data , and explain the manual calculation , the same steps can be put into Excel using Excel formulae to get the result you want.

Narayan
 
Thanks for replying. I will try to explain my query in detail again.
I have an instrument that gives me interest that is compounded on a daily basis. The rate of interest is not fixed and changes daily. So I have following interest rates for day 1, day2, and day 3
Day1 - 7.83%
Day2 - 7.76%
Day3 - 7.32%
Now, if I assume that I had invested 100million initially, I receive the following interest :
Day 1 - 21452.05 (which is 7.83% X 100 mio X 1/365)
Day 2 - 21264.83 (which is 7.76% X [100 mio + 21452.05 interest recd on day 1] x 1/365)
Day 3 - 20063.36 (which is 7.32% X [100 mio + 21452.05 interest recd on day 1 + 20063.36 interest received on day2] x 1/365)

Now if I total I receive an interest of 62780.25 for 3 days with interest on interest also considered, this effectively amounts to 7.64% effective annual interest rate (62780.25 / 100 mio) x (365/3)

What I need is the formula to get this 7.64% directly.

thanks.
 
You must log in or register to reply here.
Back
Top