Loan Simulator

Hi Experts,

I'm trying to customize Loan Simulator sheet (as attached), therefore I'm finding a problem on payment date column. Where as the payment date should be the first date of next month against the Start date, eg: if start date is 17-May-11 than Payment date starts 1-June-11 on wards up to last payment(which is monthly basis). so second term should 1-Jul-11..... Need to customize current formula, which already there based on "Number of Payments in Months" I don't require that conditions anymore, payments shall be based on total months.

Appreciate your help on this.

Thax

Maybe this?

=IF(B25="","",IF(B25<=$D$16,IF(payments_per_year>1,DATE(YEAR(C24),MONTH(C24)+1,1))))

Thanks zbor for your great help. It works, i may need your help again to fnalize this task.


Regards,
Jaai

Hi,

I would like to seek your one more help,

It is if date is less than 15th of a month then the formula should return 1st date of next month or should return first date of the month after;

Eg: if the start date is on 17-May then payment date start from 01-Jul (instead of 01-Jun) and if the start date is on 13-May then the given formula works which is 01-Jun.

Appreciate your great help on this as well..

Thanks

Try:

=IF(B25="","",IF(B25<=$D$16,IF(payments_per_year>1,DATE(YEAR(C24),MONTH(C24)+MIN(2,1+INT(DAY(C24)/15)),1))))

Hint: if you want to include 15th in first month then change red number to 16 (or for n-th day change it to n+1)

Awsome, its working perfectly.

Thanks a lot Zbor.