Customer aging based on last payment date

[kashaikh78]

Hi Excel gurus,

I am working on a customer aging report based on the last payment date and looking for some formula help to populate the outstanding amounts under each bucket. for example, if the last payment was received 83 days ago then the monthly rental amount (column B) should be populated in 0-30, 31-60 and 61-90 days bucket (column F, G & H). I have uploaded the sample data file for reference.

[Limor_OP]

Hi,
how about that?

Put in line 1 the valued 0,30,60,90,120 respectively,

And apply this in F4 and across:
=IFERROR(IF(LOOKUP($D4,F$1:J$1,F$1:J$1)>=F$1,$B4),"")

[kashaikh78]

Great!. This works.