Hi everyone,
I am trying to make a table that will have as many rows as a certain cell value. I have a cell that someone could input a number. In my case it is the number of payments for a loan. I have a couple if statements to change the cells around if they are paying by monthly or monthly. The code is below. However I have my table set up for 360 payments, which would be a thirty year loan. I want to make the table regenerate itself if someone would like a longer or shorter loan.
=IF($F$11="No",(IF(DATE(YEAR(K10),MONTH(K10)+1,DAY(K10))<=DATE(YEAR($F$10),MONTH($F$10)+$F$8,DAY($F$10)),DATE(YEAR(K10),MONTH(K10)+1,DAY(K10)),"Payment is Over")),IF(DATE(YEAR(K10),MONTH(K10),DAY(K10)+14)<=DATE(YEAR($F$10),MONTH($F$10),DAY($F$10)+($F$8*(14))),DATE(YEAR(K10),MONTH(K10),DAY(K10)+(14)),"Payment is Over"))
F11 is either Yes or No. This is the answer to is this a bimonthly payment.
K10 is the previous payment date.
F10 is the payment start date.
F8 is the user input for the length of the loan.
The formula either increases the month by 1 or increases the days by 14 depending on if it is a bimonthly payment or not.
The problem is in the false condition of the if statements. I did not know what to put so I made it a text to let someone know that the payment is already over, however I would like the cells after this to have no value. and the table end at the ending date.
Any help would be appreciated as I am new to using functions in Excel.
Thanks,
Kody
Bookmarks