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:
a. (A): Includes the principal at the beginning of the mortgage
b. (B): Includes the number for the next payment of the mortgage
c. (C): Includes the time for the mortgage (15 or 20 years only)
d. (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 Principal for 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 Interest for 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 Interest for 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 mvergara@pace.edu on Nov 30th, 2016 no later than midnight.
Submit your HW, even if the program doesn’t run properly.
Proposed Code
Sub Baked_Sec_Mtg()
‘Define variables
‘Read total mortgages from Cell E1 in Worksheet Input, store the value in TotMtg
For k = 1 to TotMtg
‘Read the first mortgage : Principal, Term, Rate and Payment
‘current workbook
wkb1 = ActiveWorkbook.Name ‘ Set new worksheet
‘cancel messages from the system to the user
Application.DisplayAlerts = False
‘Open file with the term and rate for the mortgage
‘ Calculate how many payments left are in the mortgage, save the total in last _payment
For j = 1 to the last_payment
‘ NewPayment = Payment + j ‘ if the last payment in Input is 64, the next is 65, next 66, etc
‘Read factors for Principal, Amortization and Interest for NewPayment
‘multiply those factors by the initial principal
‘ save the values of Principal, Interest, and Amortization in the
‘ correct worksheet (use worksheet(“Name”).activate)
‘Move accordingly among worksheet wkb1 to wkb2 using Activate or Close
wkb2.Activate
wkb2.Close
next j ‘ means to repeat with the next payment
‘Once the first mortgage was calculated we proceed with the next
Next k ‘we did the process for k = 1 , now we will do it again for next k , which is k=2
‘ Once all mortgages have been calculated
‘ here we introduce the formulas for totals
‘ We read totals from each worksheet and copy that info in Main in the correct column
‘Done , Good Job!
End Sub