Please note in my example I have presumed the current investments is the sum of interest and principal. The logic is to use SUMPRODUCT function to 'count' all FALSE values from the comparison between current investments and remaining principals, which would the same as the total number of Keep Going in the Comment column, and then subtract this number by the total number of months to get the first month that the investment is break even. Need to give credit to as this was his idea. The logic is to use MATCH function to find the first Break Even or Better from the Comment column, and then use INDEX function to return the corresponding month number. =INDEX(MthNo,MATCH("Break Even or Better",Comment,0)) The logic is to compare the current investments with remaining principals, use MATCH function to find the first TRUE result, and then use INDEX function to return the corresponding month number. This is an array formula which needs to be confirmed by pressing Ctrl+ Shift+ Enter in the formula bar. You can use one of the following three formulas to return the month of break even: Comment being the comments ( Keep Going or Break Even or Better) in Column L.MthNo being the month numbers in Column A.CurrInv being the range of current investments in Column K.RemaPri being the range of remaining principals in Column G.Suppose you have the above mortgage amortisation schedule, and you have the following named ranges in your excel worksheet: I need cell A4 (I have labeled as "Pay off in months") to report the month number from column A when the number in column K first is >= the number in column G. I tried this in cell A4, but I don't know how to reference that one point where it goes from not true to true.
=IF(G11>=K11,"Keep Going","Break Even or Better") I put this in a column so I can see where the break even point is by just scrolling down looking at the result: Where the value in K becomes >= G.Īt that break even point cell I4 should report the month number. I trying to make a formula in cell I4 that will tell me where the "break even" point is. So, it's a decreasing number going down.Ĭolumn K is "current investment." So, this number goes up as payments are made. I'm making a mortgage amortization calculator thing.Ĭolumn A is the payment month.
0 kommentar(er)
