Detailed Excel Mortgage Calculator

Need someone who is very familiar with mortgages and mortgage calculators. This may appear confusing but it's actually very easy: The Excel Mortgage Calculator will perform 2 major functions. The first function will calculate mortgage payments based on the HAMP waterfall guidelines(see attached page 78 for waterfall details). The second function will calculate a mortgage payment based on the gross monthly income. here are the details... User will input mortgage data. Based on the data: 1. The current balance will be calculated by adding current loan balance with the total past due amount. 2. Per the interest rate waterfall step (6.3.2 on the attached document), each mortgage payments will be calculated by reducing current interest rate by 0.5% increments until the rate reaches 2.0%. Example: if the current interest rate is 4%, then the spreadsheet will calculate payments at 3.5%, 3.0%, 2.5% and 2.0%. 3. Per the term extension waterfall step (6.3.3 on the attached document), each payments will be calculated based on a 2.0% interest rate and an increment of 1 year extension until the maturity date reaches 40 years. Example: If the input data specifies that the loan was orginated on 2007 and the term is 30 years, then the original payoff date is 2037 (2007 + 30 years). The loan can be extended to 40 years from the current date, therefore, the payoff date will be 2052. The term of the loan can be extended by 15 years from it's original term. The spreadsheet will calculate the monthly mortgage payments for each of the year that the loan is extended for the next 15 years. 4. Per the principal forbearance/reduction waterfall step (6.3.4 on the attached document) each payment will be calculated based on a 2.0% interest rate and a 40 year term and an increment of 1% principal reduction (forbearance) until the loan balance reaches a 30% reduction, or when the principal balance equals the fair market value. Example: If the current balance is $255,000 then the first payment will be calculated at $252,450.00 (1% reduction or $255,000 - $2,550) this will continue at 2% reduction, 3% reduction and so on until you reach 30% reduction. Function 2 will be very quick and easy. I will describe further when we establish communication.