Hi,
I'm attaching a spreadsheet and I'm hoping somebody can help me with it.
I'm keeping a lien book that shows how much each home pays in principal and interest (P&I) each year. Based on the type of the house (townhome, duplex, etc.), the original amount of principal is different between the homes and once the property sells, the principal amount that the owner owes changes, with the owner usually paying less than the original amount of P&I and the developer picking up the remainder of the P&I.
Once each home sells, the developer likes to pay its remaining portion of P&I in one lump sum, so the first sheet in the spreadsheet shows the amount of principal paydowns remaining plus six months of interest. It's that six months of interest that's giving me trouble.
If you look at the first example, House 1, on the first sheet, the vlookup formula that I'm using should bring back $191.22 in column I, but instead it's pulling in -$278.15, no clue why. The formula is using vlookup to pull in the sum of the Owner's Portion of Interest plus the Developer's Portion of Interest less the Owner's Portion of Interest.
This is really a spreadsheet you need to look at rather than my explaining it, but I was hoping that somebody would be able to offer a solution to getting my interest due correct.
I did try combining an index formula with a vlookup formula, and while I could get the total interest due to calculate correctly in one cell and the developer's interest due to calculate correctly in another cell, when I tried putting the formulas together, Excel was giving me some crazy number that wasn't correct.
If there's a way to get a match formula to behave similarly to vlookup and look at all of the possible rows (rather than stopping at the first one it matches to), then I believe I could successfully use index and match together, but I don't believe that's possible.
Any help at all is greatly appreciated. Thank you!
Amanda
P.S. As added info, when I was prepping the example spreadsheet for this forum by removing any personal information, I found that deleting subsequent rows that shouldn't have had anything at all to do with the prior homes was affecting my interest due numbers. Something screwy is definitely going on, but heck if I know what.
Bookmarks