Link to home
Start Free TrialLog in
Avatar of Beeyen
Beeyen

asked on

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
Avatar of byundt
byundt
Flag of United States of America image

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
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
Avatar of Beeyen
Beeyen

ASKER

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)
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
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
Avatar of Beeyen

ASKER

I was looking for the compound daily interest on $30.00 for the date 06/02/2012 to 04/07/2015.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Beeyen

ASKER

Thank you for your assistance and taking the time to respond to my questions.