# 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.
Raj

#### Attachments

• 43.5 KB Views: 6

#### Excel Wizard

##### Member
=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

• 44.5 KB Views: 9

#### 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

• 42.5 KB Views: 4

#### ranjit1964

##### New Member
=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

• 42.1 KB Views: 1

#### 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!

#### ranjit1964

##### New Member
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!
Hi John,
Oh yes!
Thanks for clarifying.
Regards
Ranjit