# 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
###### Who is Participating?

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

Mechanical EngineerCommented:
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
Mechanical EngineerCommented:
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
Author 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)
Mechanical EngineerCommented:
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

Mechanical EngineerCommented:
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.

Compound-daily-interestQ28651487.xlsx
Author Commented:
I was looking for the compound daily interest on \$30.00 for the date 06/02/2012 to 04/07/2015.
Mechanical EngineerCommented:
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

Experts Exchange Solution brought to you by