Mortgage calculator: finish date?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dr. Zhivago - 17 Aug 2006 18:51 GMT

I have a mortgage calculator which calculates the outstanding amount on a
daily basis. This gives me a huge column of figures, which eventually turns
negative somewhere around the middle, depending on how much I am repaying!

I would like to put a single cell at the top of the spreadsheet which
identifies the 'Mortgage Liberation Day" by referencing the cell where the
outstanding balance drops below zero.

I've tried VLOOKUP, but can't seem to get the formula right. Any help will
be much appreciated! - Column A is entirely formatted as dates, and column F
is the outstanding balance.

Reply to this Message

Niek Otten - 17 Aug 2006 19:02 GMT

If you really mean BELOW zero (not equal to zero):

=INDEX(A2:A50,MATCH(0,F2:F50,-1)+1)

If you mean <= zero, test for 0 and go one row up if necessary

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a mortgage calculator which calculates the outstanding amount on a
| daily basis. This gives me a huge column of figures, which eventually turns
[quoted text clipped - 7 lines]
| be much appreciated! - Column A is entirely formatted as dates, and column F
| is the outstanding balance.

Reply to this Message

Dr. Zhivago - 17 Aug 2006 19:26 GMT

Well, of course, as SOON as I posted my query, I found the answer (after
several hours of searching earlier!). If it's of help to anyone else, I've
pasted it here, and my very great thanks fo to N HARKAWAT for resolving my
own problem!

Answer
=INDEX(A4:A1000,MATCH(TRUE,(B4:B1000<0),0))
array entered(ctrl+shift+enter)

assuming range a4:a1000 holds the dates and b4:b1000 has the amounts

>I am doing some mortgage calculations. Based on any number of variables
>and
[quoted text clipped - 8 lines]
>
> Thank you

> I have a mortgage calculator which calculates the outstanding amount on a
> daily basis. This gives me a huge column of figures, which eventually turns
[quoted text clipped - 7 lines]
> be much appreciated! - Column A is entirely formatted as dates, and column F
> is the outstanding balance.

Reply to this Message