Figuring Late Payment on Mortgage

[Bzzygrl]

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.

 

[ryguy7272]

Try these models (they are free):
http://www.vertex42.com/ExcelTemplates/simple-amortization.html

http://www.vertex42.com/ExcelTemplates/home-expense-calculator.html


Regards,
Ryan--

 

[Bzzygrl]

Thanks, Ryan, but I already looked at those and it doesn't do what I need. F
or 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 interested I'm owed
for the 14 days and on an accumulating basis as they are always late. Any
help that you can give would be great.

 

[joeu2004]

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.