I'm not a sophisticated user of Excel but am wondering, if it is possible,
how I can set up a work sheet to figure out how much interest is owed
when someone is paying me late on their mortgage payment.
For example, if I'm to receive the mortgage payment on the 1st of the
month but I don't actually receive it until the 15th of month - that's 14 days
that they are late - I need something to figure out how much [interest] I'm
owed for the 14 days and on an accumulating basis as they are always late.
Well, you don't say how the loan is structured; specifically how
interest is normally computed. Assuming a typical amortized loan, the
following might meet your needs:
interestDue =
loanBalance * dailyRate *
( max(currentDueDate, currentPaidDate) -
max(previousDueDate, previousPaidDate) )
dailyRate = annualRate / 365
This assumes that if the payment is early, the full interest is
charged up to the due date.
In the US, the divisor of dailyRate may be 366 in a leap year. But
the lender is permitted to always use 365, which favors the lender.
Note that that is interest due. Technically, interest paid is:
interestPaid = payment - principlePaid
principlePaid = min(loanBalance, payment - interestDue)
The point is: any payment in excess of the loan balance should be
considered a finance charge.
HTH.
