Need help to set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.


Rent for the month
Start Date Year 1 Year 2 Year 3 01/07/14 Explanation
01/08/13 10 20 30 10 < 1 yr = 10
01/07/13 40 50 60 50 enter 2nd yr = 50
16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80)
16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)


I'm not sure any formula can help on this problem. Maybe vba, but I have zero knowledge on vba. Any help would be great, formula or vba using Excel 2003.

Thanks in advance.

Macy