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

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

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

3. ## 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. ## 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. ## Re: How to spread recurring bill over the years having lease start and end date

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

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

8. ## 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. ## Re: How to spread recurring bill over the years having lease start and end date

Here it is
I hope it works this time

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

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

12. ## 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. ## 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.
Thanks

14. ## 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. ## 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.
Thanks

Page 1 of 2 1 2 Last

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