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

Interest calculation between two dates compounded annually

Exceldummy

New Member
Dear All,
I want to write a formula in excel to calculate interest for the following dates:
a) 31/03/2015
b) 30/06/2015
c) 31/02/2016

Data:

Loan amount: $1,000.00
Loan date: 7 April 2014
Interest Rate: 11.8% per annum
Compounding : every year on 7 April

Please can you help me with the calculation.

Thanks
Exceldummy
 

Chihiro

Excel Ninja
This link to calculate annual compound interest
https://support.microsoft.com/en-us/kb/141695

Just for reference, Intra year compound interest calculation
https://support.microsoft.com/en-us/kb/213907

From your description, Compound interest is only applied on Apr 7th of each year?
Then calculation would be
= 1000*(1+0.118)^ROUNDDOWN(("Calc Date"-"Loan Date")/365,0)
After 1460 years or so, it will not be accurate as Leap Date in Feb, will exceed 365.

c) would give #Value error as Feb 31, 2016 does not exist.
 
Top