PDA

View Full Version : Solved: Mortgage loan spreadsheet problem



hiflier
02-07-2011, 07:44 PM
I've written a program (attached) that asks for principal, downpayment, interest and term and creates an amortization sheet and a loan payment sheet. When using the loan payment sheet, sometimes people miss a monthly payment. When they do, it computes interest and reverse amortizes the principal. So it may show 0 for payment, 600 for interest and -600 for principal. The problem is that this way our company has to show the 600 as income even though we didn't receive anything. On the Oakwood Loan Payments worksheet, the November payment shows interest that is more than the payment. It should show 600 for interest, 0 for principal, with the extra interest being carried over. I can make it work in VBA with a button, or I can make this work for one month using Excel formulas, but if they miss two payments I'm clueless how to do the carryover. Thanks for your help.

IBihy
02-09-2011, 09:23 AM
Hello,

I'm not really sure right now if this is an Excel problem alone. And I'm actually clueless about American accounting rules. But, naively, possibly, I see a logic problem in the case of a missing payment. Why does the interest, that should have been income this month but wasn't due to a missing payment, go "into the books"? Especially with the Sarbanes-Oxley Act in place. Maybe the books (ledger?) need a separate account for missing interest income which will correct the "profit & loss" statement?

Then there is still the case of multiple (more than 1) missing payments. Again, allow me the naive question: "Is there a maximum boundary for the amount of missing payments, or will each payment missed automatically extend the mortgage period in months?"

Puzzled about accounting,

Isabella.

IBihy
02-09-2011, 09:48 AM
Addendum: Does multiple missing payments refer to say, missing payment in August and in September as well? If so, a simple guess would be to save the results on the mortgage from the missing payment in August and repeat the calculations for September.
But that's just a guess. Maybe you should ask a financial math expert how this is to be handled.
BTW, no bad feelings, having had a quick glance at the code, I found a few neglections, stuff that could be done better, without actually working at the problem.

Isabella

IBihy
02-09-2011, 11:13 AM
Addendum: on the Oakwood Loan Payment sheet it appears as if a payment for October is missing. So your workbook has a logical problem here. How about discerning between interest due and interest received? The interest received is realized income and can make its way into the ledger.

Please respond and let me know if my thinking is wrong.

Isabella

hiflier
02-12-2011, 07:43 PM
Thanks for responding. First, if you have any suggestions about coding I'm always interested in learning. As to the problem, you mentioned a missing payment for October. What happened in this example is that payments are due on the first. They made the Aug and Sep pmts early, then delayed the Nov pmt until the 15th. So the calculated interest for the Nov pmt was more than the $600 that was paid. A correct handling of this would be to allocate $600 to interest, $0 to principal, and carry the extra $217.36 in interest (that wasn't paid) over to the next month. So your last comment about interest due vs received is on the mark. I'm just having difficulty visualizing the way to do it. The interest due is carried over to the next month, and if the combination of interest from that month and unpaid interest from the previous month is more than the amount paid for that month, it's carried over again and so forth. I'm still working on how to make that happen automatically when a new payment is entered. I can do it manually, but I don't want to. Thanks again for your interest in my problem.

Bob Phillips
02-13-2011, 03:24 AM
You need to show us how it would be done manually if your accounting rules as we have no idea. So, show an example where one month is missed completely, another where two are missed, another where the payment is late in the correct month, etc.

Bob Phillips
02-13-2011, 03:35 AM
BTW, as a small aside, you should never use " " as a default value in a formula, because you create a non-empty cell which will trip you up somewhere else. You should use "", no space between the quotes, or better use 0 for a numeric value cell.

hiflier
02-13-2011, 09:28 AM
OK, thanks for the tip. I've attached a demo of the program. To do this manually, I use a button to run the following code:

Sub Calculate()
'
' Find the first and last row of data
For i = 8 To 19
If Range("a" & i).Value > 0 Then FirstRow = i: Exit For
Next
LastRow = Val(Mid(Range("A" & FirstRow).End(xlDown).Address, 4))

'I use the term Normal interest to mean interest that is due for the period
'(principal * interest rate * number of days since last payment. I use
'Computed interest to mean the final interest that I will give to our
'accountant

For i = FirstRow To LastRow
If Range("a" & i) > 0 And Range("d" & i) >= 0 Then 'a row with a payment

'put the normal interest plus carryover into the Interest cell
'column y has the previous month's carryover

Range("e" & i) = Range("g" & i - 1) * Range("c" & i) * Range("w" & i) + Range("y" & i - 1)

'Column z is normal interest for the period. I use it to compare total
'computed interest
'for the year to see if the carryover computations are correct
'I'll get rid of it in final version

Range("z" & i) = Range("g" & i - 1) * Range("c" & i) * Range("w" & i)

'Now if the normal interest is greater than the payment, carry over 'the excess and put it in column y. Then change column e to just the
'payment amount

If Range("e" & i) > Range("d" & i) Then
Range("y" & i) = Range("e" & i) - Range("d" & i)
Range("e" & i) = Range("d" & i)
End If
End If

'Now the next time it loops, it will add what we just put into y
'into the interest due and compare it to the payment again
Next
End Sub

Refer to the attached spreadsheet. There are two years of data displayed. The top year is correctly computed using the above code. The bottom shows interest owed on the payment date regardless of payment amount.

So in April the top shows 0 for interest even though the true interest is $39.59 shown in the bottom example. The $39.59 is carried over. For May, the interest should be another $38.72, but the top shows $50, recapturing some of the carryover. It isn't until October that we finally recapture all of the interest owed, and the November interest is the same on both sheets (except for the effects of the negative amortization on the bottom sheet).

As I said, I can do this manually. What I want is for this to be automatic. I don't want to have to hit a button because if I forget to hit the button before I print the sheet it will be incorrect. I want it to compute when the payment is entered. I tried a number of event triggers and can't find one that works properly.

Sorry for the long post. And thanks for your help with this.

hiflier
02-14-2011, 11:28 PM
Thanks for your help. I figured it out, that I needed to use the Change event to run the calculations. I have another problem with it but I'll start a new thread for that. So thanks for your help with this.