Macro to run scenarios and save cash flows to corresponding scenario sheets

**dariushou** · 06-14-2008, 05:45 PM

I'm new to macros so forgive me ahead of time. Here's my issue. I've created a macro that amoritzes and aggregates the cash flows from rows of assumptions for each loan. That's great for one scenario, but i would also like to add 8 more scenarios for a total of 9. I have my assumptions next the loan name across many columns. My list of loans are currently in order like this:

Loan 1
Loan 2

I would like to order them like this with mulitple scenarios:

Loan 1 Scenario 1
Loan 1 Scenario 2
Loan 2 Scenario 1
Loan 2 Scenario 2
and so on

Each scenarios cash flows need to be aggregated in its own worksheet (ex. scen1 and scen2 and so on). There will end up being 28 different loans and 9 scenarios (total of 252 rows of assumptions). I'm not sure how to get the cash flows from each scenario into its correct sheet. Because i have it grouped by loan instead of scenario, it will have to move down each loans respective 9 scenarios before moving to the next loan. As it is moving down scenarios it will need to write the related scenario sheet (scen1, scen2...). Each loan will always be run the same number of scenarios as the other bonds.

It would be neat if i could simply turn the scenarios on and off with a check box or even entering in the number of scenarios i want to run out of the 9 for each loan as well as turning on and off the loans i want to run. Way beyound my skills though.

Here is what i have that does one scenario correctly.

Thanks ahead of time,

Darius

Sub Aggregate_My_Principal_Balances()
numLoans = 28

Worksheets("Scen1").Range("C12:aq393").ClearContents

For currLoan = 1 To numLoans

Worksheets("Assum2").Range("Q38") = currLoan
Worksheets("Mod").Range("gi12:hw393").Copy
Worksheets("Scen1").Range("C12:aq393").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Next currLoan

End Sub

Items from the Macro:

Scen1 = The sheet that will total the cash flows for scenario 1. The data that i'm aggregating is aggranged over several columns.

Assum2 --cell Q38 = A box where you enter the corresponding row of assumptions to run through the model--with each row indicating one of the 9 rows of assumptions for each loan.

Mod--This is the amortizer. The cells that are in the macro from this sheet are summing the current loans cash flows that is indicated on the Assum2 sheet together to the previously run loans cash flows. The previous months cash flows is in the related scenario sheet.

thanks Again.

**Paul** · 06-14-2008, 09:29 PM

Dariushou,

Please read the forum rules (link in my signature, or post at the top of each forum), paying close attention to the rules regarding wrapping any code text with proper code tags [ code ] and [ /code ] (without the spaces), as well as posting to the correct forum. I've added the code tags this time, and moved the post to the correct forum.

In the future, posts that do not adhere to the forum rules may be locked or deleted.

Thank you,
Paul