[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”

	A	B	C	D	E
1	Principal	Last Payment	Term (years)	Rate( %)	2
2	135000	64	20	3.50%
3	234567	37	15	3.60%
4

Worksheets “Principal”

	A	B	C	D	E
1	Principal
2		107532.6793	107062.2537	106590.4776	106117.347
3		195947.0366	194843.6829	193737.0726	192627.196
4	Total	303479.7159	301905.9366	300327.5502	298744.543

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”

	A	B	C	D	E
1	Interest
2		308.7154312	307.364887	306.0104655	304.6521555
3		578.3591216	575.1024525	571.836171	568.5602488
4	Total	887.0745528	882.4673395	877.8466365	873.2124043

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”

	A	B	C	D	E
1	Amortization
2		470.4255946	471.7761388	473.1305603	474.4888703
3		1103.353658	1106.610327	1109.876608	1113.152531
4	Total	1573.779252	1578.386466	1583.007169	1587.641401

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”

	A	B	C	D	E	F
1	CASH FLOWS BOND
2	Principal	Interest	Amortization	Total Payment
3	303479.7159	887.0745528	1573.779252	2460.853805
4	301905.9366	882.4673395	1578.386466	2460.853805
5	300327.5502	877.8466365	1583.007169	2460.853805
6	298744.5430	873.2124043	1587.64140	2460.853805

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 doesn’t run properly.

Proposed Code