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
Bookmarks