+ Reply to Thread
Results 1 to 6 of 6

Loan Simulator

  1. #1
    Registered User
    Join Date
    05-06-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    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. #3
    Registered User
    Join Date
    05-06-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    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. #4
    Registered User
    Join Date
    05-06-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    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. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    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. #6
    Registered User
    Join Date
    05-06-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Loan Simulator

    Awsome, its working perfectly.

    Thanks a lot Zbor.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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