Pls help with date and money formula!

Hi there

Thanks in advance for your help and advice.

I have been scratching my head for some weeks over a way to show interest payments in specific date periods. In summary:

I am trying to keep track of loans that last for 6 months with interest changed monthly. I need to show revenue from interest by date month.

I have got to the point where I am showing the interest due by month, so each loan has a column showing Loan Month1, Loan Month2, Loan Month3 etc.

This is further complicated by the fact the loans can be paid off in any month, so there may be revenue in Loan Months 1,2 and 3 but then not in 4 5 and 6.

Now, the loans start in different Date Months (ie Jan, Feb, March etc!) and I have a series of columns for the revenue for each Date Month (Jan, Feb, March…etc), so what I need is someway of excel recognising what Date Month, Loan Month 1 of a loan is and placing the Loan Month 1 interest figure in the right Date Month.

Phew. Not sure that’s totally clear. Let me put down some examples:

Loan 1: Loan Month 1 = Jan etc

Loan 2: Loan Month 1 = Feb etc

Loan 3: Loan Month 1 = March etc

So revenue for the loans by month is:

Jan = Loan 1 [month 1 interest]

Feb = Loan 1 [month 2 interest] + Loan 2 [month 1 interest]

Mar = Loan 1 [month 3 interest] + Loan 2 [month 2 interest] + Loan 3 [month 1 interest]

Etc etc

Is that any more help?

I’m really struggling with this, I am very proficient with if statements and most formulae, but not VBA.

Any help or thoughts much appreciated. If it helps, I can put an example up of where I’ve got to?

Cheers

Matti