Adding Amortization Schedule of Many Loans-Macro Help

Any ideas on this one? I've been working on it for hours now and can't figure it out. I'm a newbie when it comes to Macros.

I appreciate your time.
Darius

I think I know what you mean. I am faced with having to do this often. For example, if I want to aggregate the principal balances of all the loans in each period. Here is how I do it (I hope you can understand my explanation).

--------------------

From what you have said you have:

* all of the various loan details (row by row) in a lookup table,
* a set of columns that model the paydown of a given loan, and
* a field somewhere (lets say, for the sake of this discussion, that it is cell B2) which specifies which loan in the lookup table you want to feed into your paydown modelling (1, 2, 3, etc).

I am assuming that one of the columns in your paydown model is your "remaining principal" column (lets say, for the sake of this discussion, it is column M). Lets say we want to aggregate this profile over all of your loans. The way I do it is as follows.

Lets say that columns Y:Z are free and that the first row of your paydown column is row 2 (because you have a heading in row 1). In cell Y2 I put the formula:

=SUM(M2,Z2)

I then copy this formula down as far as your paydown column goes.

What you can then do is write a program to step through each loan (B2 = 1, 2, 3, etc) and at each loan you copy and paste values from column Y to column Z. Column Z will end up with the aggregate of all cashflows over time. A code example is below (obviously you always need to start by clearing the contents of column Z)


I hope this answers your question.

I tried your solution, but column z just ends up with the last loans info. It doesn't aggregate all previous loans.

I'm basically trying to create a cash flow engine which amortizes all of my loans and aggregates them into totals for each column (Beginning Balance, Interes, Principal, etc...). Of course it would amortize each loan according to its characteristics (coupon, term, balance, etc...).

I have each loans characteristics in a row and have a vlookup which populates the loans characteristics of the loan to be amortized. Your code worked perfect for that, but it just wasn't aggregating (also can your code do more than one column at a time).

Thanks
Darius

nevermind on that it didn't work. It did--i just had the sum formula off. By the way do you know how i can do more than one column at a time. I would like to do this to several columns at the same time. Also, any idea on how instead of summing, i can take the weighted average as i would like the weighted average coupon to be one of my outputs (weighted average coupon of all of the loans based on the beginning balance of the loans that period).

Thanks for all of the help.

darius

ok, i played around with it and figured out how to do multiple columns at the same time, but can't figure out how to take the weighted average per my last post. Any idea how to pull this one off?

Thanks,
darius

Just do this:

Y2 = SUM(M2*weight,Z2)

and then divide the numbers in the Z column by the sum of all of the weights.

awesome, thank you very much. Worked like a charm.

Glad I could help.

Hi cganivet,

I am wondering if you would be able to revisit this topic with me, creating an amortization table that aggregates loan data - yearBegBal, prinComponent, intComponent, yearEndBal - for an "n" number of loans.

I have studied the code that you provided above and I just cannot understand where in that code the amortization calculations take place.

So far, I have in one sheet the headings; opening balance, payment, interest, principal and ending balance.

I also have a table with loan data - remaining term , yield, and pv - in a row, that is, each loan (identified by a uique number) has its own row, with each of those peices of data.

My question is, how do I create and run a macro that basically runs a seperate amortization table for each loan, and then aggregates all of this together into a complete "aggregate amortization table."

Any advice would be greatly appreciated.

Thank-you.