Can I get excel to automatically calculate interest which is
Calculated daily
charged monthly?
EDIT
After help from John_Ru and WillieD24 this has been achieved. see the attached file
"copy of homeloan Test - new CF rules-final"
Once you enter the date then the details a highlighted square appears (conditional formating - please see that area for details) and for Repayment you enter figure and for Interest it calculates the value (see following)
Please note column B is the date.
To achieved this first I calculated the month column H (Green), =IF(B14="","",MONTH(B14))
Then I calculated the interest for the number of days between activity Column I (Green)
Interest calculated for days between activity, =IF(B16="","",IF(C16="","",(G15*($C$3/365)*(B16-B15))))
Finally the Interest is calculated by Column E =IF($B18="","",IF($C18="Interest",SUMIF($H$14:$H$163,"="& H18,$I$14:$I$163),""))
so basically it calculates as:
Calculate number of days between activity(Bx-By)
Then all indiviual interest amounts for month are added together
I will add an Amortization Schedule for comparison etc at a later date
EDIT
I have now uploaded the completed file with Amortization Schedule included.