I work for a child welfare agency and I am trying to create a formula that will calculate the total amount paid for a child in foster care where there are three rate categories based on age range.

The three rate categories are:
Age Rate
0-8 $22.83
9-15 $26.28
16-21 $28.72

My spreadsheet has 4 columns: Entry date, exit date, date of birth , Amount paid.

I would like the amount paid column to calculate the amount paid based on the number of days (exit date - discharge date) x the appropriate rate based on age on each day of service. So if the child spent two days in care and was 8 years old on both days the amount paid would equal $45.66 (22.83*2) but if the child turned 9 on the second day the total amount paid would be $49.11(22.83+26.28).

Can a formula be written for the amount paid column that would automatically calculate all of this?