Calculating interest due on late loan payments

[andy]

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 are:

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.

 

[GS]

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