please please help! Im so confused! Show all work in excel as well thank you!
1. Mortgage Analysis: When giving housing loans, financial institutions check whether applicants qualify for the loan. For example, your down payment should exceed a certain percentage of the property value and your monthly payments (mortgage, property taxes etc.) should be less than a certain percentage of households gross (pre-tax) monthly income. Using the following parameters as variable inputs, please develop a model, which addresses the questions at the bottom Inputs: Household annual pre-tax income Household liquid assets available for down payment House price: Real estate (property tax Loan characteristics Maturity APR: Payment frequency Minimum down payment requirement: All house-related payments (mortgage property taxes etc.) as a percentage of gross (pre-tax) income should not exceed Questions: 1.-Create loan amortization schedule (double check your calculations by using alternative methods to calculate payments) 2. Should this household receive the loan? What is the maximum mortgage loan that can be given to this household? 3.-Now assume that you have purchased the house 5 years ago (has to be an input) and you get an offer to refinance into a new 30 year (has to be an input) mortgage with 4.009 APR (has to be an input. The fixed cost of refinancing is a one-time fee another input). -Is it to your benefit to refinance? -What factors influence your decision? (Make any additional assumptions you deem necessary.) 2. 529 Plan: A client comes to your wealth management firm and is trying to assess the feasibility of 529 plan for her daughter (tax-free investment plans, proceeds from which can be used only for educational purposes). Using the following parameters as variable inputs, please develop a model, which addresses the questions at the bottom Inputs (should be variables in your modell: Child's current age: Expected age to enter the college Years in College: Current annual contribution to 529 plan: Expected contribution appreciation: Current tuition per semester (after financial ald) Expected annual tuition growth The return on your contributions is based on the following assumptions about portfello allocation and expectation about future: Until the child starts college: 100% market portfolio. During years in College: 10086 T-bills (Tresury Rates Website or Federal Reserve data also provided in the file attached). For modeling purposes you assume that the last 25 years of annual stock market returns and T-bill rates will repeat forever (e. next 25 years will see exactly same stock market returns as the last 25 years - return in 2014 will be the same as in 1989, 2015 will replicate 1990 etc). Questions: 1.-Will her contributions to 529 plan be enough to finance daughter's education? 2. -Perform the following sensitivity -Tuition annual growth can vary from 29 to 10% -Years in college can vary from 3 to 6 years Present your results in an easy to follow manner (Table, graph, etc.) 1. Mortgage Analysis: When giving housing loans, financial institutions check whether applicants qualify for the loan. For example, your down payment should exceed a certain percentage of the property value and your monthly payments (mortgage, property taxes etc.) should be less than a certain percentage of households gross (pre-tax) monthly income. Using the following parameters as variable inputs, please develop a model, which addresses the questions at the bottom Inputs: Household annual pre-tax income Household liquid assets available for down payment House price: Real estate (property tax Loan characteristics Maturity APR: Payment frequency Minimum down payment requirement: All house-related payments (mortgage property taxes etc.) as a percentage of gross (pre-tax) income should not exceed Questions: 1.-Create loan amortization schedule (double check your calculations by using alternative methods to calculate payments) 2. Should this household receive the loan? What is the maximum mortgage loan that can be given to this household? 3.-Now assume that you have purchased the house 5 years ago (has to be an input) and you get an offer to refinance into a new 30 year (has to be an input) mortgage with 4.009 APR (has to be an input. The fixed cost of refinancing is a one-time fee another input). -Is it to your benefit to refinance? -What factors influence your decision? (Make any additional assumptions you deem necessary.) 2. 529 Plan: A client comes to your wealth management firm and is trying to assess the feasibility of 529 plan for her daughter (tax-free investment plans, proceeds from which can be used only for educational purposes). Using the following parameters as variable inputs, please develop a model, which addresses the questions at the bottom Inputs (should be variables in your modell: Child's current age: Expected age to enter the college Years in College: Current annual contribution to 529 plan: Expected contribution appreciation: Current tuition per semester (after financial ald) Expected annual tuition growth The return on your contributions is based on the following assumptions about portfello allocation and expectation about future: Until the child starts college: 100% market portfolio. During years in College: 10086 T-bills (Tresury Rates Website or Federal Reserve data also provided in the file attached). For modeling purposes you assume that the last 25 years of annual stock market returns and T-bill rates will repeat forever (e. next 25 years will see exactly same stock market returns as the last 25 years - return in 2014 will be the same as in 1989, 2015 will replicate 1990 etc). Questions: 1.-Will her contributions to 529 plan be enough to finance daughter's education? 2. -Perform the following sensitivity -Tuition annual growth can vary from 29 to 10% -Years in college can vary from 3 to 6 years Present your results in an easy to follow manner (Table, graph, etc.)
