# Loan Simulator

1. ## 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.

Thax

2. ## Re: Help on Loan Simulator

Maybe this?

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

3. ## Re: Help on Loan Simulator

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

Regards,
Jaai

4. ## Re: Loan Simulator

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

5. ## Re: Loan Simulator

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)

6. ## Re: Loan Simulator

Awsome, its working perfectly.

Thanks a lot Zbor.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1