• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP with Date Question

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!
 

Attachments

  • Generic Mortgage Schedule Worksheet.xlsx
    56.8 KB · Views: 7
Perhaps this can help.
[O13]=DATEDIF(O11,EDATE(E11,12*E9),"Y")&" Yr(s) "&DATEDIF(O11,EDATE(E11,12*E9),"YM")&" Mth(s)"
 

Attachments

  • Copy of Generic Mortgage Schedule Worksheet.xlsx
    56.8 KB · Views: 8
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?
 

Attachments

  • Generic Mortgage Schedule Worksheet - 3.xlsx
    63.5 KB · Views: 2
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.
 

Attachments

  • Copy of Generic Mortgage Schedule Worksheet - 3.xlsx
    41 KB · Views: 10
Back
Top