Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating interest due on late loan payments
I lent the amount of 50 000.00 to somebody. We agreed that the
following repayments would be made on the dates below: Payments and due dates a 2012/05/05 : 10 000.00 2012/06/05 : 10 000.00 2012/07/05 : 10 000.00 2012/08/05 : 10 000.00 2012/09/05 : 10 000.00 Only the following repayments were made: 2012/06/13 : 10 000.00 2012/07/13 : 10 000.00 2012/09/11 : 5 000.00 2012/10/13 : 5 000.00 2012/11/21 : 5 000.00 2013/07/28 : 2 000.00 So, 13 000.00 is stiill outstanding (excl interest). With regards to non-payments the borrower agreed to the following conditions: "In the event that the balance not be paid by the due dates, such amount shall bear interest at a rate equal to 12% per annum, compounded monthly in arrears, and reckoned from the effective date until the date of final payment of the purchase price, both dates inclusive. The interest due from time to time will be paid monthly on the first day of each and every month." How do I setup the above loan in Excel so that I can calculate the interest that is due on the amount outstanding ? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating interest due on late loan payments
I use a Per Diem rate on the amount due something like this...
In the InterestArrears col: =PerDiemAmnt*(TODAY()-DueDate) ...where PerDiemAmnt is calculated by dividing the annual interest rate by days/year, and multiplying AmountPastDue by the result as a percent... So PerDiemAmnt = AmountPastDue*(AnnualRate/DaysPerYr)% In your scenario, though, you need a separate col for entering the short payment amount (AmountPastDue) for each DueDate, and run separate calcs for each short payment amount so that you also track interim payments correctly without messing up your payment history... The 1st payment was due 05/05 but paid 06/13 and so $10K is late for the days between DueDate and DatePaid. Same goes for each subsequent DueDate. HTH -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating interest payments on a loan for an indefinite period | Excel Discussion (Misc queries) | |||
Calulate interest for a loan as payments are received | Excel Discussion (Misc queries) | |||
loan payments - Interest only? | Excel Discussion (Misc queries) | |||
Simple interest, multiple payments, loan balance | Excel Worksheet Functions | |||
Keeping track of loan payments and interest | Excel Discussion (Misc queries) |