Solved

Yet again, one more issue discovered on this spreadsheet

Posted on 2011-09-22
234 Views
LOL...i have asked so many questions about this spreadsheet.  I have attached it, if it is locked for some reason, the password is "loan".  When a payment is made in the data tab, and it is less than the interest accrual account, then it does not report accurately in the report tab under interest paid and principal paid for that payment C and D17 respectively. How do I get that to show accurate reports when payment is not larger than the interest owed on that date of payment.  There is an example payment in the monthly report tab now on 1/2/2012.
The formulas in this spreadsheet have now officially become too complicated for me to understand unless I were to spend many hours staring and processing.  Overload...reboot...reboot!
Copy-of-Amortization-on-a-Simple.xls
0
Question by:binaryman101
• 10
• 7

LVL 7

Expert Comment

ID: 36582277
It's me again ... and this IS very complicated!

If we put this in cell C5, does it fix both the interest and the principle, but mess up the principle balance?  Or ... what is still messed up, I guess:

=IF(B17<D11,IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0)-1,7)-B17),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)-B17),IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0)-1,7)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)))

0

LVL 7

Expert Comment

ID: 36582300
What I forgot to say is ...

This checks to see if the payment is smaller than the approx monthly payment in D11.  If it is, it does the normal calculation but subtracts the payment from the interest.

I was wondering if there was something we could change on the data sheet instead, but assuming it is all correct I guess there won't be.
0

Author Comment

ID: 36582359
I'm not sure where to put that formula?  You said C5, but i'm assuming you were talking about C17, but that didn't work either.  You're also right.  The principal balance does mess up when the payment is less than the interest owed.  Somehow it should read \$100 went to interest and 0 went to principal without messing up the principal balance. Everything still works in the data sheet correctly.  If it would be easier somehow I can add a column beside the other hidden columns in the data tab that would allow for a different equation.  I'm definitely stumped.
Thanks again for all of the help.
0

LVL 7

Expert Comment

ID: 36582726

Try this in C17

=IF(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)>0,IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0)-1,7)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)),IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)))

And this in D17

=IF(B17-C17>0,B17-C17,0)

For C17 this is looking to see if the Interest Accrual on the date is > 0.  If so then it doesn't do the normal thing, instead it returns the matching value of interest accrued from the data sheet.

For D17 it is checking to see if what would normally go in the cell is > 0 and if so it does the normal thing but if not it just puts in a \$0.  That also fixes the principle balance.

Make sure you check it out really well!
0

Author Comment

ID: 36582821
The principal in D17 seems to be working correctly, but C17 is still being a pain.  This cell has bothered me since I started creating this document.  Essentially, if the payment is made that is less than the total interest owed, the payment is applied to the accrual interest plus that day's interest.  The schedule shows the correct numbers, but the formula in cell C17 doesn't give me what I need. I feel like we are really close.
0

LVL 7

Expert Comment

ID: 36582919
Yeah, that's a tricky one.  Every time I look at it I have to remind myself what it is doing.

I think I forgot to subtract the amount paid there.

=IF(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)>0,IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0)-1,7)-B17),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)),IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)))
0

Author Comment

ID: 36582971
Dang...still didn't work.  That time it said that more went to principal than the interest paid.  This is definitely tricky considering everything is tied together like a crazy person mimicking a pretzel.  Very bad metaphor, but you get my point.
0

LVL 7

Expert Comment

ID: 36583071
I'm sorry, can you upload the most current spreadsheet now ... go ahead and leave the last bad formula in there, I want to make sure I have the most current again before I start messing.  :)

Perhaps this is why I am not a loan specialist ....
0

Author Comment

ID: 36583277
Lol....I'm no specialist either, but I do think I am special now and again..lol
Sorry it took so long to respond...multitasking to the brink.
It is attached.
Copy-of-Amortization-on-a-Simple.xls
0

LVL 7

Expert Comment

ID: 36586503
Sorry I didn't get back to you last night on this.  Here's another shot ... I think I'm going to call in reinforcements if this doesn't work.  :P

C17=IF(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)>0,IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0)-1,7)-B17),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)),IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)))

I think I had the wrong column number in the first statement, where I am looking for the amount of interest accrual.  I originally had MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)>0
now I have
MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)>0

The 7th column is where the interest accrual is, not the 6th.
0

LVL 7

Expert Comment

ID: 36586993
As I look at this again ... I think I am not putting the right number in C17.  I was putting the interest that was still owed, not the amount that was applied to the interest.

I'll look at it again.
0

LVL 7

Expert Comment

ID: 36587030
C17

=IF(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)>0,IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),5),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)),IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)))
0

Author Comment

ID: 36587660
That's ok for not getting back to me last night.  The good news is that with the new formula, it works when the payment amount is less than the interest owed.  But, I put in a payment two days later that was over the amount of interest owed, and the report only said that I paid that days interest and not the total interest account.  If that makes sense.
I think you have the updated file, if not I can send it.  If you put 100 for the payment on the 10/5 it works fine, but if you put 1550 on 10/7 it shows the error I'm talking about.  The schedule works fine still, but the cells in row 17 don't vibe well.....oh, and ps....i had some serious dejavu while typing this response.
0

LVL 7

Expert Comment

ID: 36588391
Yeah, now my stubbornness is really starting to kick in on this one ... :)

C17 = IF(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)=0,IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0)-1,7),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),5)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),5))

I have tried every scenario that gave us problems before, and they all look OK to me.  So, how about if you take a shot at "breaking" it?
0

Author Comment

ID: 36588609
This last one you gave me worked and I got all excited....But then i checked to see what would happen if I made a payment on the first date, and for some reason it shows that all of the payment went to the interest paid and none on the principle.  All other cells work fine except for the first date of the loan.

I gotta say, your stubbornness is very appreciated.  What do you mean by "breaking" it?...is that sarcasm or should I literally take a hammer to my pc for the aggravation it has caused us both...lol.
0

LVL 7

Accepted Solution

BusyMama earned 500 total points
ID: 36588659
Haha!  No, I don't want you to use a hammer on your PC.  But you DID manage to break the spreadsheet again ... :P

How about this?  All I did was add an IF statement to the beginning that says, if the date in Cell A17 is NOT equal to the first date on the data sheet (Cell B18), then work the interest out, but if the date in Cell A17 IS equal to the first date on the data sheet then set the Interest to 0.

That seems like it is correct, but the data sheet doesn't adjust the same way ... so let me know.

C17 = IF(A17<>'Loan Data'!B18,IF(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),7)=0,IFERROR(INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),6)+INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0)-1,7),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),5)),INDEX('Loan Data'!\$B\$18:\$K\$11017,MATCH(A17,'Loan Data'!\$B\$18:\$B\$11017,0),5)),0)
0

Author Closing Comment

ID: 36588729
With our combined cerebral efforts we have VICTORY!!!!!!!!!!!!!!!  I had to modify the last formula you gave me slightly so that it would pay the interest for the first day, but oh ya....I'm freakin excited right now.  A+ for you BusyMama!!!

I'm going to start a new question though....during our last battle with the interest formula, I realized that I need a way to calculate payments that go solely to the principle.....so i'm just saying it might be another headache, but i'm giving you the heads up.  Thanks again.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.