Rent calculation based on each month's remaining days

[alexandros6600]

Dear Community,

Once again a big help request.

How can a fixed annual rent amount be calculated based on each month's remaining days (28,30 or 31)
But for the whole months of the rental period, the amount that should be charged is based on the (annual rental)/12.
For example the rental period is set from 14/06/2024 till 27/12/2024
In this period there are 17 days in June + 5 whole months + 27 days in December.

Please refer to the book attached

 

[AlanSidman]

I would use a slightly different approach and do it by the total number of days

Excel 2016 (Windows) 64 bit

	B​
19​	=B2/365​
20​	=DATEDIF(A2,D2,"D")​
21​	=B19*B20​

Sheet: Sheet1

 

[alexandros6600]

Dear AlanSidman,

Thank you for your response. At first I had also a similar approach like yours.
It gives a slight different amount 13.424,66 vs 13.411,71.
That is a "big" difference since it has to be calculated in the way described in the book.
For June the amount is divided by 30 - the total days of June e.g 2.083,33 €/30 = 69,44 € per day of June
whilst for December is divided by 31 - the total days of Dec e.g 2.083,33 € €/31 = 67,20 € per day of December

 

[p45cal]

In the attached, formula in cell C2:
=rent($A2,$E2,$B2,C$1)
Can copy down and across.
Plain year numbers in C1 and D1.
Cross checks in cells C8:C11 and C6.
Older Excel versions of the formulae not included (hard work compared with your last thread at https://chandoo.org/forum/threads/rent-calculation.55888/ )

 

[vletm]

caspianzephyr​

You should also open a new thread.
This thread belongs to @alexandros6600

 

[p45cal]

It would be nice to get some feedback from @alexandros6600 …