get cash flow recovered from 4 columns of data

[dariushou]

I would like to start out and saying hello to everyone. This is my first post and hope that i can get a little help with finding the right formula for my problem. I've tried for days and can't figure it out. I'm not sure if it can even be done, but then again i'm an amature compared to some of you. Here it goes:

It's really a simple concept, but doing it in excel is causing me problems. Basically, i have two dollar amounts which represents two loans made in each month (per the example below, in month one a loan was made for $100 to one guy and $400 to another guy and in period 2 $500 was made to someone and $1,000 was made to another and so on). I would like to know when i can expect to recover these amounts according to a curve. The curve is in columns D & E. So for the loans made in the first period, i would receive 10% of the $100 in the first period and 20% of the $400 in the first period. In period 2, i would further receive 30% (40% - 10%) on the $100 dollar loan made in the first period and 40% on the $400 loan that i made in period 1. Also, in period 2 i made two additional loans ($500 & $1,000, respectively). In period 2 i would receive 10% of the $500 and 20% of the $1,000 (the first time a loan is made you receive cash in that period equal to the starting point of the curve).

I would like to have the cash flows in the same column for all periods of advances as i've done in the table below. However, i can't figure out how to do it as when you have 360 periods or anything much larger than what is in the table. I'm currently doing this in a spreadsheet with each advance amount taking up its own column (however, there aren't even 360 columns in a spreadsheet and that's a messy way to do it). Let me know if i can provide you with anything else. I've shown the way i'm doing it in the bottom table and the way i wish i could do it in the top table. I've limited it to only 4 months of making loans to keep things simple.


I had to zip the file up because it wouldn't fit. I wish i had more space for the table to upload. It's in the zip file though.


Thanks Again for your help.