Calculate Monthly Bill Cycle/Period in Excel

BigLeviathan on 02/10/2017 - 21:19
Last edited 02/10/2017 - 21:47

Hi,

Let's say I lend money to my mate for 1 year on 31/10/2016 and I expect him to pay on monthly basis.

If I calculate it manually, it will be something like this:
1st month - payment due on 30/11/2016
2nd month - payment due on 31/12/2016
3rd month - payment due on 30/01/2017
…and so on…

Is there a way to know the next payment due date automatically using excel?

Thanks

Financial

Comments

  • doweyy on 02/10/2017 - 21:26
    +2

    Put 31/10/2016 into cell A1.

    Then in Cell A2 put the function =EOMONTH(A1,1)

    Then spread that function across/down until the last month you need :)

    • BigLeviathan on 02/10/2017 - 21:30

      Thanks! This is what I after :)

  • Grande Kintama on 02/10/2017 - 21:27

    Why not pick a date that is constant like maybe the 1st of each month for example.

    • BigLeviathan on 02/10/2017 - 21:31

      I like to be fair with my mate, and consider it is happening at the end of the month, it might confuse myself as some February does not have 30 or 31 :)

  • the cheeky dropbear on 02/10/2017 - 21:27
    +1

    A1: "31/11/2016"
    A2: "=EOMONTH(A1,1)"
    A3: "=EOMONTH(A2,1)"
    A4:

    Just drag down the formula from there

    Note: Without the quotation marks.

    • BigLeviathan on 02/10/2017 - 21:30

      Thanks! This is what I after :)

  • nocure on 02/10/2017 - 21:27
    +3

    Nvm, I read the title wrong ;)

    • steff on 02/10/2017 - 21:35
      +1

      hahaha i came here thinking wtf is this too!

  • BigLeviathan on 02/10/2017 - 21:45

    Wait a minute, if I start to lend the money on 23/10/2016, using EOMONTH function seems not giving the correct result?

    A1 = 23/10/2016
    A2 = EOMONTH(A1,1) ==> resulted in 30/11/2016
    A3 = EOMONTH(A2,1) ==> resulted in 31/12/2016

    • doweyy on 02/10/2017 - 21:52

      Oh whoops, that's a harder formula to figure out then, I probably can't help unfortunately.

    • NotJustChickenDinner on 02/10/2017 - 21:52

      EOMONTH is end of Month, which was correct when you said 31/10/16

    • bobbified on 02/10/2017 - 22:58
      +1

      Your example has 31/10, 30/11 and 31/12, but you're not looking for the end of month?

      Is it the same date for each month that you're after? For example 15/06, then 15/07, etc? That'd be the EDATE function. It would simply add a specified number of months to whatever date you start with.
      Used like this (without quotations) "=EDATE(A1,X)"
      where "A1" contains your starting date and "X" is the number of months to add.

      If that's still not what you're after, your might need to be a little more clear in your description.

Login or Join to leave a comment
Login Join