Rent Calculation

[alexandros6600]

Dear Excel fellows,

I would like some help regarding the rental calculation of the amount which is received annually (based on daily charges).



In column A is the start date of the rental
In column B is the annual rent amount
In column C,D,E,F is the cumulative amount which shall be received for every year
eg. For the year 2024 in C2 based on the rental starting date A2, C2=(194 days in 2024)*(10.000,00usd/365=27,4)=5.315,6 usd

In column G is the end date of the rental

In column F is s the cumulative amount which shall be received for the year 2027
eg. For the year 2027 in Cell F2 based on the rental ending date G2, F2= (235 days in 2027)*(10.000,00usd/365=27,4)=6.439,6 usd

If the start date is after one of the depicted years, the total amount should be null eg Cell C3
likewise,
if the end date is before one of the depicted years, the total amount should be null eg Cell F3
For every cell in columns C,D,E,F that a full yearly(365 days period) is charged then it should return the whole annual amount of the Cells in Column B.

Is there a formula that can calculate the above?

 

[p45cal]

In the attached:
If you have Office365, formula in C2:
=myRent($A2,$G2,$B2,C$1)
copied down and across. This is a lambda formula (see it in Name Manager), you get hints for what goes where:


For earlier versions of Excel, see formula in C8:

=(MAX(0,MIN($G2,DATE(C$1,12,31))-MAX($A2,DATE(C$1,1,1))+1)/(DATE(C$1+1,1,1)-DATE(C$1,1,1)))*$B2

copy across and down.
Note that I changed the top row in columns C:F to only contain year number (no text).
Doesn't agree with your expected results in I2, J3 and L2 but I think my results are correct (this is maybe because I also calculate the number of days in each year and there are 366 days in 2024, a leap year).

 

[alexandros6600]

A million Thanks won't be enough!

Thank you for your answer and the two way solution.

Much appreciated