+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 25

How to spread recurring bill over the years having lease start and end date

  1. #1
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Unhappy How to spread recurring bill over the years having lease start and end date

    Hi there. I am trying to solve a problem and couldn't find a single answer yet.
    I have a lease start and end date and i need to spread the recurring bill over the life of lease considering number of months in each year.
    Here is the case
    Lease start date: 1-Apr-2019
    Lease end date: 31-Mar-2021
    Recurring Bill: say $1000
    I am trying to build a formula that would spread $1000 over the life of lease based on number of months in the year.
    In 2019, it would be 9 months so the result would be $1000 x 9 = $9000
    In 2020, it would be 12 full month so that result would be $1000 x 12 = $12,000
    In 2021, which is the lease expiry year, it only has 3 months so the result should be $1000 x 3 = $3000.
    I have numerous lease to be spread over the years and I am not sure how to build a model that would do this for me so I had to manually spend a lot of time to do this.
    I used DATEDIF() to get number of months and have been trying several trick but nothing worked.
    I tried looking at the results on google but the only thing i found was monthly spread and not yearly.
    I would appreciate if someone can respond on this.

    Thanks
    Mohammad

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    1,949

    Re: How to spread recurring bill over the years having lease start and end date

    I'd probably do something more like:
    Please Login or Register  to view this content.
    Which would be "number of days in term" times $12k per year divided 365.25 days per year.

    Logically simplier, and it prorates for partial months. But I dunno how exactly you want to handle, say, a lease that starts on Apr 15 or whatever.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    HI Ben thanks a lot for your response but its not that simple as i thought :D....
    I want to built a model where the formula would automatically calculates the number of months left in each year and then allocate the amount accordingly.
    So for the first year of 2019 since its starting from April so the total number of months in that year is 9 so the billing of that year would be 1000 x 9 - 9000.
    In the 2nd year, the lease continues so it will be full 12 months and the 3rd year only has 3 months in that year.
    this is what i am trying to achieve. Attachment 624684

  4. #4
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Ben i am not looking for days here its just the month that i would like to have in my calculation.

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    1,949

    Re: How to spread recurring bill over the years having lease start and end date

    Quote Originally Posted by fasqi View Post
    this is what i am trying to achieve. Attachment 624684
    This attachment is working, can you reload it?

  6. #6
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Attachment 624688

    Here it is Ben
    Please let me know if its working ..

  7. #7
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to spread recurring bill over the years having lease start and end date

    Unfortunately the paper clip is not working for attachments. In order to upload, click Go Advanced, then Manage Attachments. Click Browse to find your file, click Open, then click Upload. Click Close this window, and your file will be attached when you submit your reply.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  8. #8
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Thanks a lot Melvosh, i was actually trying a way to do that :d... you have explained it well
    THank you again

  9. #9
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Here it is
    I hope it works this time
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to spread recurring bill over the years having lease start and end date

    See attached workbook with formulas in K7:N8. Does that give you the results you're looking for?

    I would post the formula straight in this message, but for some reason I keep getting blocked.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    That was so quick Melvosh, this is exactly what I have been looking for.
    Thank you so very much Melvosh.
    I guess adding the formula in the sheet was more helpful then posting it here :D..
    Thanks again it was a great help and you solved it so quickly.
    Much appreciated
    Mohammad

  12. #12
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to spread recurring bill over the years having lease start and end date

    You're welcome, happy to help, thanks for the rep!

  13. #13
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Melvosh can you please explain the three IFS used in the formulas. I got the first one, but 2nd and 3rd Ifs where you have used 12 in both of them, I am little unclear about those.
    Appreciate your feedback.
    Thanks

  14. #14
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to spread recurring bill over the years having lease start and end date

    I can try, although for some reason I still can't use CODE tags, or any formulas, in my replies.

    The first IF:
    If the year in column K (or L, M, N) is equal to the year of the start date, subtract the start date from the last day of this column's year in months. This is how many months the lease has in the start year.

    The second IF:
    If the year in this column is equal to the year of the end date, subtract the number of months between the two dates from 12. This is how many months the lease has in the end year.

    The third IF:
    If the year in this column is between the year of the start date and the year of the end date, use 12 months. This is for any years where the lease runs the full year.

    And finally:
    If all of those are false, use 0.

    Hope that helps explain how it's working!

  15. #15
    Registered User
    Join Date
    05-16-2019
    Location
    Canada
    MS-Off Ver
    365
    Posts
    14

    Re: How to spread recurring bill over the years having lease start and end date

    Melvosh it was very helpful thanks again for this.
    However i like to know if the lease starts and ends in the same year say (start at 1-Apr-19 and end at 31-May-19) like in two months, would this formula pick that up too, because while validating the numbers I am not getting the right amount for some of the leases that starts and end in the same year.
    Appreciate your feedback
    Thanks
    Mohammad

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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