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 Principal for 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 Interest for 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 Interest for 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 mvergara@pace.edu on Nov 30th, 2016 no later than midnight.

Submit your HW, even if the program doesn’t run properly.

Proposed Code