Loan Amortisation Loop in VBA
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
picks up the second row of data and runs a schedule for that loan
aggregates the data for loan 2 onto loan 1 and then runs a schedule for row 3 d开发者_开发技巧ata 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
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.
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
精彩评论