Compounding daily interest using excel

Experts,

I need to calculate compounded daily interest on an amount owed.
Can someone give me a excel formula. I am not a math please layman terms. I
know the principal of how it works but don't want to spend a day doing it by
calculator. Per the spreadsheet attached - Amount $30.00 @6% from 6/2/2012 to 4/07/2015

Let me know

Thanks
Copy-of-Compound-daily-interest.xlsx
BeeyenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtCommented:
If you borrow an amount on date B2, the amount owed with daily compounding is:
=C2*(1+G2/365)^(E2-B2)

If you owe a specific amount on date E2, the amount borrowed with daily compounding is:
=C2*(1-G2/365)^(E2-B2)
Compound-daily-interestQ28651487.xlsx
0
byundtCommented:
The general formula for the value to day for an amount borrowed some time ago is:
=Amount borrowed *(1 + Annual interest rate/Number of periods per year)^Total number of periods

In the above formula, the caret ^ is Excel's symbol for exponentiation. That's how Excel handles what would be for you a very lengthy series of calculations on a pocket calculator--one for each day. The exponentiation multiplies the value of a dollar with one day's interest on it by the number of days the amount is owed. The result of that exponentiation times the original amount equals the amount owed at some date in the future.

Note that I could have simplified the original formula by using F2 instead of (E2-B2):
=C2*(1+G2/365)^F2
0
BeeyenAuthor Commented:
Good Day,
Looking at my spreadsheet, the amount currently owe is $30.00. then the daily interest from 06/02/12 - 04/07/2015 is $25.29. Is this correct?  So, the total owed = $55.29?

If you owe a specific amount on date E2, the amount borrowed with daily compounding is:
=C2*(1-G2/365)^(E2-B2)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

byundtCommented:
Beeyen,
Using the numbers on your spreadsheet, if you currently owe $30,00, then the original amount was $25.29, and the daily interest is $30.00 - $25.29 = $4.71

Brad
0
byundtCommented:
Beeyen,
I am going to revise my formula for calculating the original amount to:
=C2/(1+G2/365)^(E2-B2)

The two formulas will give slightly different results with larger values in cell C2. The original formula is an approximation that happens to give exactly the same answer ($25.29) as the more accurate revised formula for the specified problem.

Brad
Compound-daily-interestQ28651487.xlsx
0
BeeyenAuthor Commented:
I was looking for the compound daily interest on $30.00 for the date 06/02/2012 to 04/07/2015.
0
byundtCommented:
If the original amount was $30.00, then the accrued interest from 06/02/2012 to 04/07/2015 would be:
=C2*((1+G2/365)^(E2-B2)-1)
=$35.59 - $30.00 = $5.59
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BeeyenAuthor Commented:
Thank you for your assistance and taking the time to respond to my questions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.