• 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 Allocation

ranjit1964

New Member
Hi MASTERS
Attached sheet is about allocation annual rental value across months at monthly value &
fractional value at its start or end period.
Thanks in advance
Raj
 

Attachments

Please try at Q3
=YEARFRAC(MAX(Q$2,$B3),MIN(EDATE(Q$2,1),$C3+1))*12*(MIN(EDATE(Q$2,1),$C3+1)>MAX(Q$2,$B3))*$D3/12
or
=IFERROR((DATEDIF(MAX(Q$2,$B7),MIN(EDATE(Q$2,1),$C7+1),"ym")+DATEDIF(MAX(Q$2,$B7),MIN(EDATE(Q$2,1),$C7+1),"md")/DAY(EOMONTH(Q$2,0)))*$D7/12,0)

1-31 Jan is 31 days
15-31 Jan should be 17 days/31 days
 

Attachments

John Jairo V

Well-Known Member
Hi to all!

I think that year must be calculated over 360 days. If so... check this formula:
=$D3*MAX(,DAYS360(MAX($B3,Q$2),MIN(1+$C3,1+EOMONTH(Q$2,0))))/360

Check file. Blessings!
Blessings!
 

Attachments

ranjit1964

New Member
Please try at Q3
=YEARFRAC(MAX(Q$2,$B3),MIN(EDATE(Q$2,1),$C3+1))*12*(MIN(EDATE(Q$2,1),$C3+1)>MAX(Q$2,$B3))*$D3/12
or
=IFERROR((DATEDIF(MAX(Q$2,$B7),MIN(EDATE(Q$2,1),$C7+1),"ym")+DATEDIF(MAX(Q$2,$B7),MIN(EDATE(Q$2,1),$C7+1),"md")/DAY(EOMONTH(Q$2,0)))*$D7/12,0)

1-31 Jan is 31 days
15-31 Jan should be 17 days/31 days
Thank you Excel Wizard it works well.
 

ranjit1964

New Member
Hi to all!

I think that year must be calculated over 360 days. If so... check this formula:
=$D3*MAX(,DAYS360(MAX($B3,Q$2),MIN(1+$C3,1+EOMONTH(Q$2,0))))/360

Check file. Blessings!
Blessings!
Thank you John,
360 days is new perspective and it works.
But in the row 7 sample fraction jan-20 value should be 5940.85
For rows 3 ,4 & 5 effectiveness of formula is confirmed in cross checks summed in cell AE & AF.
Regards
Ranjit
 

Attachments

John Jairo V

Well-Known Member
Thank you John,
360 days is new perspective and it works.
But in the row 7 sample fraction jan-20 value should be 5940.85
For rows 3 ,4 & 5 effectiveness of formula is confirmed in cross checks summed in cell AE & AF.
Regards
Ranjit
Hi, Ranjit!

If you work with 360-year, all months must be 30 days (including February). Is like my calculation works with 16 days and not with 17 days. Blessings!
 
Top