D
dianaweinstein
Hi, i have a spreadsheet that has cashflows for multiple bonds under
different stress scenarios. There are 4 sets of cashflows per bond,
with the following fields:
date, principal, interest, cashflow, balance, and principal writedown.
The tabs are dated by month and bonds are in the spreadsheet based on
settle date. currently, the date is in the following format:
Bond Name
Cusip PROJECTION 1
Date Principal Interest Cash Flow Balance
Principal Writedown
80,994,212 34,760,692 115,754,904
12/30/2008 - - -
139,453,270
1/25/2009 1,013,525 691,559 1,705,084 138,439,745
2/25/2009 1,002,238 686,537 1,688,775
137,437,507
3/25/2009 991,078 681,572 1,672,649
136,446,429
4/25/2009 980,044 676,661 1,656,705
135,466,385
5/25/2009 969,135 671,806
1,640,941 134,497,250
6/25/2009 958,350 667,004
1,625,354 133,538,900
etc....
So in total, there are 24 columns per bond (4x 6 columns with fields
in the format above) 3 of the cashflows are projections and the 4th
set is actual cash.
I'd like to have one tab where i can list the bonds and then pull in
the current month's projections and compare with the current month's
actual cashflow. Then from there i would be able to calculate the
differences and % differences, and any other calcs I would need to do.
The other tab i would like to have is where i have a pivot table
showing the difference in projections over the next 5 years by
quarter. What I am looing for is a way to find the cusip or bond
name in the spreadsheets with the months labled in the mmm yyyy format
and then pull the information. I am thinking I need an intermediary
spreadsheet that wll generate cashflows for me in a workable format.
Anyone have any ideas??? If this doesnt make sense then i can send
over the spreadsheet. Thanks!!!
different stress scenarios. There are 4 sets of cashflows per bond,
with the following fields:
date, principal, interest, cashflow, balance, and principal writedown.
The tabs are dated by month and bonds are in the spreadsheet based on
settle date. currently, the date is in the following format:
Bond Name
Cusip PROJECTION 1
Date Principal Interest Cash Flow Balance
Principal Writedown
80,994,212 34,760,692 115,754,904
12/30/2008 - - -
139,453,270
1/25/2009 1,013,525 691,559 1,705,084 138,439,745
2/25/2009 1,002,238 686,537 1,688,775
137,437,507
3/25/2009 991,078 681,572 1,672,649
136,446,429
4/25/2009 980,044 676,661 1,656,705
135,466,385
5/25/2009 969,135 671,806
1,640,941 134,497,250
6/25/2009 958,350 667,004
1,625,354 133,538,900
etc....
So in total, there are 24 columns per bond (4x 6 columns with fields
in the format above) 3 of the cashflows are projections and the 4th
set is actual cash.
I'd like to have one tab where i can list the bonds and then pull in
the current month's projections and compare with the current month's
actual cashflow. Then from there i would be able to calculate the
differences and % differences, and any other calcs I would need to do.
The other tab i would like to have is where i have a pivot table
showing the difference in projections over the next 5 years by
quarter. What I am looing for is a way to find the cusip or bond
name in the spreadsheets with the months labled in the mmm yyyy format
and then pull the information. I am thinking I need an intermediary
spreadsheet that wll generate cashflows for me in a workable format.
Anyone have any ideas??? If this doesnt make sense then i can send
over the spreadsheet. Thanks!!!