Use If function in projected rental revenue

[Bear]

Hi every expert and Luke M

I have tried a couple days after work and still cannot correct the if formula in the attached excel table. Hope to see if anyone can help.

I am preparing a budget rental revenue for shopping mall. The licence period and the 3-year rental are different for each tenant. I need to calculate each month rental revenue from each tenant, however, I cannot fix the rental transition period.

Example:
Licence Start day: 1/7/2022 (the licence start day can commence in any days of a month)
Licence End day: 30/6/2025
Yr 1 Licence: $10
Yr 2 Licence: $20
Yr 3 Licence: $30

Then the rental revenue starting from 1/7/2023 shall be $20

 

[srinidhi]

Hi,

Your formulae is taking Jan to Jan as 1 year, that's why its showing the 1st year amount. Just changed the date to 31st Dec 2021, its showing correctly.