amortisation

[sna]

Hi Dear!
I have data in B2:H4 i need to generate allocation of prepaid expense base on amount from start date to end date.I need to generate even amount but only two dicimal so the last amount may be a bit different to adjust sum of the allocation equals to the base amount.
Previously ,I used this formula
=IF((B3<=EOMONTH(--$G$2:$R$2,-1)+1)*(C3>=$G$2:$R$2),ROUND(E3/D3,2),0)
i attached a template

Note I use excel 365


thanks

[norkaz]

...

G3
=IF(COLUMNS($G3:G3)=MONTH($C3),$E3-ROUND($E3/$D3,2)*($D3-1),IF(COLUMNS($G3:G3)<MONTH($C3),IF(COLUMNS($G3:G3)<MONTH($B3),"",ROUND($E3/$D3,2)),""))

Copy to the right-hand side and copy down.

Bigcat9
RCA Bangkok Thailand

.......

[norkaz]

...

For the spill array formula, you can try this.

G3
=LET(s,SEQUENCE(,12),IF(s=MONTH(C3:C4),E3:E4-ROUND(E3:E4/D3:D4,2)*(D3:D4-1),IF(s<MONTH(C3:C4),IF(s<MONTH(B3:B4),"",ROUND(E3:E4/D3:D4,2)),"")))

Bigcat9
RCA Bangkok Thailand

.......

[sna]

Thx,will take a look

[sna]

thank you for your nice solution.i come up with a formula =IF(
OR(
MONTH(DATEVALUE(G$2&" 1")) < MONTH($B3),
MONTH(DATEVALUE(G$2&" 1")) > MONTH($C3)
), "-",IF(MONTH(DATEVALUE(G$2&" 1")) = MONTH($C3),
ROUND($E3 - SUM($F3:F3), 2),
ROUND($E3/$D3, 2)
)
)

[norkaz]

thank you for your nice solution.i come up with a formula =IF(
OR(
MONTH(DATEVALUE(G$2&" 1")) < MONTH($B3),
MONTH(DATEVALUE(G$2&" 1")) > MONTH($C3)
), "-",IF(MONTH(DATEVALUE(G$2&" 1")) = MONTH($C3),
ROUND($E3 - SUM($F3:F3), 2),
ROUND($E3/$D3, 2)
)
)

Great!


Bigcat9
RCA Bangkok Thailand

[sna]

Thank you for always help!