+ Reply to Thread
Results 1 to 25 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
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    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
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    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

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

    No, my formula would not account for that. I can take another look at it to see how that might be accomplished.

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

    Updated formula to account for lease starting and ending in same year:
    Please Login or Register  to view this content.
    This formula is rather complicated for my tastes, despite accomplishing the purpose. I'm going to try and trim it down, but it should work for what you need.

  18. #18
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

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

    Have a try with this formula I used the DATEDIF function you also use for no of terms too.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    this formula also works if start and end year are the same.

    see attachment of working formula

    ___________________
    @Melvosh: you cannot post your formula when you have greater than or less then sign followed by number or text, this is a firewall protection to avoid script injection
    my formula works because it has the = behind the <= you can resolve by putting a space behind the "< "
    also look at this post about Sucuri issues https://www.excelforum.com/tips-and-...rkarounds.html
    Attached Files Attached Files
    Last edited by Roel Jongman; 05-16-2019 at 05:32 PM.

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

    Hey Melvosh Thanks alot.
    I haven't had a chance to try this yet as I am still in the process of building the whole model. I will apply this once I am in that part again and will let you know
    Thanks once again it was such a great help
    Mohammad

  20. #20
    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 alot Roel for your wonderful observation and a fantastic solution in the worksheet :D it really worked well in the given scenario
    Thank you for your help, much appreciated

    Mohammad

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

    Happy to help. FYI, I would personally go with Roel's formula in post # 18, it's much more compact.

  22. #22
    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 Melvosh. I would apply both the formulas in my worksheet to see which works the best .. I agree that Roel's formula is much more compact ..
    Thanks again guys ....
    Let me get back to you with the good news once it has been built up ..

  23. #23
    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 Melvosh and Roel.
    I applied Roel's formula and it work just superbly on the sheet.
    I want to thank you people from the bottom of my heart for coming up with such an amazing solution to this .
    Thanks a ton.

  24. #24
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

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

    your welcome, glad to been able to help..

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

    it was a great help.
    Thanks once again guys ..

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula for billing statement between bill cycle date and bill due date?
    By etonsketon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-27-2019, 11:17 PM
  2. How many years from start date formula
    By Rumpus24 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-26-2018, 06:58 AM
  3. Recurring bill statements every nth months
    By dpelican in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-29-2017, 12:53 PM
  4. spread revenue or value over months by using start date and end date
    By excelproject in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2014, 06:37 PM
  5. How to spread annual forecasts by month across fiscal years by variable start date
    By gbolanis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2013, 11:59 AM
  6. [SOLVED] How to determine how many fiscal years are present from start date to end date.
    By terrivega3500 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-07-2012, 01:25 PM
  7. help required to get start and end date of a recurring event
    By ajay_psingh25 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-20-2011, 07:08 AM

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