Loan Amortisation Loop in VBA -

I need to build a cumulative cash flow schedule for a pool of credit in VBA and whether he hopes for some help Used to be?

I can make a refinement program in Excel for a single loan but I will need to run programs for a loan of up to € 000 in execution .... Ie

A spreadsheet To begin with, the loan level information (interest rate, duration, balance etc.), which raises the first line in the spreadsheet (first loan) and runs the refinement program for that loan and then

1. raises the second line of data and the loan Runs a program for

2. Adds data 1 on loan for loan 2 and then runs a schedule for row 3 data, etc.

The end result is just an aggregate refinement program for all loans ....

I'm not very hot on Viba, so arguments for help. / P>

Any help would be greatly appreciated.

Many thanks, Matt

A1: F4 Data with date

 Term Start Date Rate Date Payment Term End Date 30 1/1/2010 5% 120000 $ 644.19 1/1/2040 20 5/1/2010 5.50% 80000 $ 550.31 5/1/2030 30 8/1/2010 6% 190000 $ 1,139.15 8/1/2040 

You can create a cumulative refinement table like this

  F8: J8 = date, Payment, Interest, Principle, Remaining F 9: F? = 1/1/2010, 2/1/2010, etc. G9 = {= SUM (($ B $ 2: $ B $ 4> F9) * ($ F $ 2: $ F $ 4> = F9) * ($ E $ 2: $ E $ 4))} H10 = = -ROUND (SUM (IF (ISERR (IPMT) ($ C $ 2: $ C $ 4/12, (YEAR (F10) -Year (B $ 2: B $ 4) Copy Done)) * 12) + month (F10) - (MONTH (B $ 2: B $ 4)), $ A $ 2: $ A $ 4 * 12, $ D $ 2: $ D $ 4), 0, IPMT ($ C $ 2: $ C $ 4/12, (YEAR (F10) Yr (B $ 2: B $ 4)) * 12) + MONTH (F10) - (MONTH (B $ 2: B) $ 4)), $ A $ 2: $ A $ 4 * I9 = = G10-H10 is copied down J9 = {= SUM (($ B $ 2: $ B $ 4 = F9) * ($ D $ 2 : $ D) is copied down I = 10 = $ 2: $ D $ 4)) = J10 = {= SUM ($ B $ 2: $ B $ 4 = F10) * ($ D $ 2: $ D $ 4)) + J9-I10}  

Clear the soil as huh? Here you are my exam Worksheets can be downloaded

Obviously there are some simplifications here, all are generating debt and paying one month before and so on. Those formulas can be a bit more complicated Because your data becomes more complex. I do not expect that you will be able to plug your numbers there and it will work, but hopefully this is the beginning of a model that you can create.

Note that formulas in curly braces should be entered with array formulas and controls + shift + enter.