1. Individual Project: You have to choose one among the following types of CMBS securities: • Sequential Pay with three tranches • Sequential Pay with three tranches with a Z-bond • Floater / Inverse Floater • PAC + Support Tranche • TAC + Support Tranche After that, build an excel model that will compute cash flows towards each tranche. The model will feature the following: a) Customizable inputs for the following: • Initial principal in each tranche (summing to total principal on the underlying mortgages) • Maturity of the underlying loans in months • Interest rate of the underlying loans • Interest rate to investors in each tranche • Servicing fee paid by the investors (same for each tranche) • Prepayment assumption (PSA or constant CPR) • Current market rate b) User will input all the criteria in a) and be able to see the following update automatically: • Amortization table for the underlying pool (initial principle, scheduled payments, prepayments, scheduled principal, interest) • Servicing fee of each tranche • Cash flow of each tranche • Investor valuation for the security c) An additional tab should include the following, fully annotated graphs (title + labeled axis) • Initial principal of each tranche over time • Total payments to each tranche The grades will be awarded based on the following criteria: ● Accuracy: the spreadsheet should compute accurate cash flows for any inputs of the parameters above. The spreadsheets must necessarily be dynamic. ● User-Friendliness: it should be obvious where one should enter the parameters above and where to look for cash flows towards each tranche. Clearly state what type of security you chose. Note: This is an individual project and intended for you to learn how to build a spreadsheet yourself from scratch. The use of a pre-made template or collaboration of any kind is strictly prohibited. It would be treated as plagiarism and duly measures would be taken against that. Excel Functions That Might be Useful: I. ADDITION, SUBTRACTION, MULTIPLICATION, AND DIVISION LOGIC II. SUM() AND AVERAGE() FUNCTIONS III. TRUE AND FALSE LOGIC IV. IF() FUNCTION V. AND() & OR() FUNCTIONS VI. MAX() AND MIN() FUNCTIONS VII. COUNTIF() AND COUNTA() FUNCTIONS VIII. IRR() AND NPV() FUNCTIONS Additional Excel Features: 1. DATA VALIDATION: Data Validation restricts the type of data or the values that users enter into a cell. So for instance, with Data Validation you can limit inputs in a given cell to a specific type of value (e.g. whole numbers only). You can also restrict inputs to only predetermined values (e.g. a drop-down menu with a specific list). 2. CONDITIONAL FORMATTING: Conditional Formatting allows you to create a custom look for certain cells (or the entire model) based on user inputs. 3. DATA TABLE: The Data table feature is a feature in Excel that calculates multiple results (i.e. outputs) instantly. This feature is especially useful for scenario analysis in real estate, as well as displaying outputs in a dynamic way. 4. CHARTING TOOL: Charting tools allows you to visually present your outputs in a compelling (and dynamic) way.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
