Solved

Yet again, one more issue discovered on this spreadsheet

Posted on 2011-09-22
17
229 Views
Last Modified: 2012-05-12
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
Comment
Question by:binaryman101
  • 10
  • 7
17 Comments
 
LVL 7

Expert Comment

by:BusyMama
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

by:BusyMama
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

by:binaryman101
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

by:BusyMama
ID: 36582726
Sorry about that.

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

by:binaryman101
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

by:BusyMama
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.

How about this:

=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

by:binaryman101
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

by:BusyMama
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:binaryman101
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

by:BusyMama
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

by:BusyMama
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

by:BusyMama
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

by:binaryman101
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

by:BusyMama
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

by:binaryman101
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

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

by:binaryman101
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now