• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rent Calculation

alexandros6600

New Member
Dear Excel fellows,

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

1705592443286.png

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?
 

Attachments

  • rental calculation.xlsx
    10.9 KB · Views: 1
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:
1705600227341.png

For earlier versions of Excel, see formula in C8:
Code:
=(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).
 

Attachments

  • Chandoo55888rental calculation.xlsx
    12.6 KB · Views: 5
Back
Top