VLOOKUP with Date Question

[Mark Carver]

I am working on a generic mortgage worksheet that I will include in other real estate-related worksheets. However, I've hit a wall with a particular formula. Basically, I want to figure out how to determine how many years and months remain on a loan. I have attached my spreadsheet with the problem notated in cell L2. THANKS for your help!

 

[GraH - Guido]

Perhaps this can help.
[O13]=DATEDIF(O11,EDATE(E11,12*E9),"Y")&" Yr(s) "&DATEDIF(O11,EDATE(E11,12*E9),"YM")&" Mth(s)"

 

[Mark Carver]

Thanks. I will study this and give it a try.

 

[Mark Carver]

Guido, THANKS, you're a genius!
- Quick help with modification, if you will. On the attached spreadsheet where I inserted your formula, it works as long as the loan term is 30 years. However, if I shorten it to less than 30 years, say 15, I get VALUE#. The relevant cells are J12 (your formula), E9 (Loan term), and E12 (nearest today's date). I will eventually hide E12 by making the font white.
- Not directly related, but is there a way to conditional format the table to only the length of months dictated by the loan term?

 

[GraH - Guido]

Hi Mark,
The #VALUE! is caused by your formula in E12, which is an input for the formula in J11. You could use
[E12]=AGGREGATE(146,($C$17:$C$376)/($C$17:$C$376<TODAY()),1)
Hiding can be done by the custom format ";;;" by the way. But better is having such a formula stored in the name manager (F3, look on the ribbon on formula section). Both ways are in the file.
If using the name manager, there is no need for the formula in E12 at all.

Slightly adapted your conditional formatting rule to
=AND($B18<>"",MOD(ROW(),2)=0), deleted the outer grid borders.

Glad to have been of assistance.

 

[Mark Carver]

THANK YOU. You are a Godsend!