Homework # 3
FALL 2016
Lubin School of Business Pace University
Securitization of Mortgages I
The following is the code developed in class, which reads 2 factors for the principal from the files 35-31.xlsx and 35-32.xlsx. These factors are used to calculate the remaining balance in both mortgages after the payment #15.Sub readfactor_2()
Dim wkb1, wkb2 As Workbook
Dim row_number As Integer
Dim txttocolrange As Range
‘current workbook
wkb1 = ActiveWorkbook.Name
‘cancel messages from the system to the user
Application.DisplayAlerts = False
‘selecting drive
‘ChDrive (“C:Libraries\Documents”)
‘Open file 15-31.xlsx and 15-32-xlsx – 15-31 includes mortgages 15 yrs at 3.1% and
’15-32 mortgages at 15 yrs at 3.2%
For k = 1 To 2
aux = 4
‘ the path to the file in this case is C:\Users\mvergara.PACE.000\Downloads, but you
‘ need to change it to the path in your computer
Set wkb2 = Workbooks.Open(“C:\Users\mvergara.PACE.000\Downloads\15-3” & aux + k & “.xlsx”)
‘ Now the new workbook (wkb2) with the factors is activated in order to read the data
wkb2.Activate
‘find factor in row 16 + 8 = 24 , which is the factor , review screenshot 1 below
row_number = 16 + 8
‘ the information is stored in the variable “factor”
factor = Cells(row_number, 2).Value
‘ Once we have the value in “factor” we activate our worksheet in order to copy the value
”moves from the window wkb2 to the window wkb1
Windows(wkb1).Activate
‘Now the factor is copied in cells (2+k,2) in worksheet wkb1
Cells(2 + k, 2).Value = factor
‘in the following line we move from worksheet wkb1 to wkb2 in order to close it
wkb2.Activate
wkb2.Close
Next k ‘we did the process for k = 1 , now we will do it again for next k , which is k=2
End Sub
Questions
Modify the code above, in order to read the principal, Interest and amortization for the following mortgages:
1. 15 years at 3.8% after the payment 37
2. 20 years at 3.5% after the payment 67
Mortgages
Factors
Initial Principal
Period
Rate
Last Payment
Principal
Interest
Amortization
$235,000
15
38
37
$153,467
20
35
67
· Your program will read: Period, Rate and Last Payment from the left side (purple section) from the Spreadsheets 15-38.xlsx and 20-35.xlsx and copy the data in the right side (orange section), under the column “Principal” , “Interest” and “Amortization”.
· You can use any range in your spreadsheet to copy the purple and orange tables. But the orange section must be filled automatically with your program.
· The field “Initial Principal” (which is the loan amount at beginning of the mortgage) is not used in this HW (not yet!).
· Use the files: 15-38.xlsx and 20-35.xlsx located in Documents in Blackboard. Copy and paste this files in your computer.
· Your worksheet (where you will write the code) should be named: LastName_FirstName-HW3.xlsm.
Screenshot 1: Spreadsheet with the factors. The code above reads the data in Cells (24,2) , meaning the factor in the column of principal and for the 16th month. In this example we used the factor in row 24, which is factor to calculate the principal after 15 payments of the mortgage (outstanding balance). However, using the same program, we can read any variable from the spreadsheet : Principal, Interest, Amortization or PMT.
Results and due date
The results of the HW should be on a spreadsheet and submitted as an attachment to mvergara@pace.edu on Friday November 18th, 2016 no later than midnight.
Submit your HW, even if the program doesn’t run properly.