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!!!
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected] or
Ashish Jain @ [email protected]
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
