[SOLVED] VBA: Homework # II

Voluntary Makeup Homework # II

(30% points to be added in any Exam + 1 HW grade)

FALL 2016

Lubin School of Business Pace University

Securitization of Mortgages II

Using the code from the HW # 3 Securitization of Mortgages I you will improve your program according the next guidelines:

1. You will include the Worksheets: Principal, Interest, Amortization, Main and Input.
2. The Worksheets Input will have the columns A, B, C and D with the following information:
   1. (A): Includes the principal at the beginning of the mortgage
   2. (B): Includes the number for the next payment of the mortgage
   3. (C): Includes the time for the mortgage (15 or 20 years only)
   4. (D):Includes the rate for the mortgage (3.5%, 3.6%, 3.7% and 3.9%)
3. The Worksheets Principal will store the principal balance for all mortgages included in Input using as a maximum of 240 columns and a number of rows equal to the total mortgages included in Input.
4. The Worksheets Interest will store the Interest for all mortgages included in Input using as a maximum of 240 columns and a number of rows equal to the total mortgages included in Input.
5. The Worksheets Amortization will store the Amortization for all mortgages included in Input using as a maximum of 240 columns and a number of rows equal to the total mortgages included in Input.
6. Each column for the Spreadsheets: Principal, Interest and Amortization will have a final row with the totals for all values above.
7. The program reads each mortgage from Input, and calculates the: Principal, Interest and Amortization for all future payments of the mortgage. The values are copied in Worksheets: Principal, Interest and Amortization.
8. All values are totalized in the last row of each Worksheets.

9. The program calculates the cash flows for the bond in Worksheets Main
10. The program receive the total number of mortgages from Cell E1 from Worksheets Input (using a function to count in range A2:A1002)
11. Use the Excel files 15-35, 15-36, 15-37, 15-38, 20-35, 20-36, 20-37 and 20-38 from Blackboard.
12. The program must be activated for a button Macro Run located in Worksheet Main.

Example for a bond with 2 mortgages:

Worksheet Input

Worksheets Principal

The value 107532.6793 = 0.796538365 x 135000 and

0.796538365 is the factor for Principalfor payment 65 from file 20-35.xlsx for the Mortgage of 135000.

Worksheets Interest

The value 1308.7154312 = 0.002286781 x 135000 and

0.002286781 is the factor for Interestfor payment 65 from file 20-35.xlsx for the Mortgage of 135000.

Worksheets Amortization

The value 470.4255946 = 0.003484634 x 135000 and

0.003484634 is the factor for Interestfor payment 65 from file 20-35.xlsx for the Mortgage of 135000.

Worksheets Main

The value 303479.7159 = 107532.6793 + 195947.0366 from Worksheets Principal

The value 887.0745528 = 308.7154312 + 578.3591216 from Worksheets Interest etc..

Hints:

• Use the code from HW 3
• Do the calculations for the first mortgage until the last payment
• Proceed with the next mortgage
• Use the proposed general statements to code your program

Results and due date

The results of the HW should be on a spreadsheet and submitted as an attachment to [email protected]on Nov 30th, 2016 no later than midnight.

Submit your HW, even if the program doesnt run properly.

Proposed Code