Loan Amortisation Loop in VBA

Question

Hi,

I need to create a cumulative cash flow schedule for a pool of loans in vba and was hoping for some help??

I can create amortisation schedules in Excel for single loans but I would need something to run schedules for 000's of loans in one execution....I.e

Starting with a spreadsheet containing loan level information (interest rate, term, balance etc) code that picks up the first row in the spreadsheet (the first loan) and runs an amortisation schedule for that loan and then

1. picks up the second row of data and runs a schedule for that loan

2. aggregates the data for loan 2 onto loan 1 and then runs a schedule for row 3 data etc etc

The end result being just one aggregated amortisation schedule for all of the loans....

I'm not too hot on vba hence the plea for help.

Any help would be greatly appreciated.

Many thanks, Matt

Answer 1

I would be looking to stay away from VBA in this case is I don’t think you are going to need it. Excel has lots of functions for working with loans.

http://www.exceltip.com/st/Formulas_to_calculate_Loan_payments_in_Microsoft_Excel/301.html

http://www.meadinkent.co.uk/excel-loan-repayments.htm

http://www.exceluser.com/tools/termloans.htm

As they say you should only reinvent the wheel if you want to learn about wheel building

Answer 2

With Data like this in A1:F4

Term Start Date Rate   Amount    Payment    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 make a cumulative amortization table like this

F8:J8 = Date, Payment, Interest, Principle, Balance
F9:F? = 1/1/2010, 2/1/2010, etc..
G9 = {=SUM(($B$2:$B$4<F9)*($F$2:$F$4>=F9)*($E$2:$E$4))}
    copied down
H10 = =-ROUND(SUM(IF(ISERR(IPMT($C$2:$C$4/12,((YEAR(F10)-YEAR(B$2:B$4))*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)-YEAR(B$2:B$4))*12)+MONTH(F10)-(MONTH(B$2:B$4)),$A$2:$A$4*12,$D$2:$D$4))),2)
    copied down
I10 = =G10-H10
    copied down
J9 = {=SUM(($B$2:$B$4=F9)*($D$2:$D$4))}
J10 = {=SUM(($B$2:$B$4=F10)*($D$2:$D$4))+J9-I10}
    copied down

Clear as mud huh? You can download my test worksheet here

http://www.dailydoseofexcel.com/excel/CumAmort.zip

Obviously there are some simplifications here. All the loans are originated and paid on the first of a month and so on. Those formulas might get a little more complex as your data gets more complex. I don't expect you'll be able to plug your numbers in there and have it work, but hopefully it's the start of a model you can build on.

Note that formulas in curly braces are array formulas and must be entered with control+shift+enter.