Write a consulting report on mortgage payment for a bank using the current market data you can find on internet The format of the report is as follows, which has imbedded the DAESI procedures. 1. Title page (3%): This includes the title of the report, author’s name, affiliation (you may use your fictional consulting company’s name and your home address), contact information (email, phone, and website), and date. This is a single page. 2. Executive summary (or called Abstract) (7%): Limited to one page about the main results and conclusion of your report. This is another single page. 3. Introduction section (15%): This is the first two steps (P: problem identification, i.e., a statement of the problem; and A: abstraction of the problem, i.e., selection of a math modeling method) of the 5-step DAESI math modeling approach. The purpose of your consulting is to provide your client, the bank, a clearly written mortgage loan document which can be easily used by a potential borrower, and hence help the bank to attract more customers. You need to cite at least one reference (e.g., your data source). This section is about 1-2 pages. 4. Data and method section (25%): This section is basically Step 3 (M: model formulation) and part of Step 4 (M: model solution) of the 5-step approach: formulate the mathematics equations for the problem, and describe the mathematical formulas including the mathematical solution of the modeling equations. The section should include the data, at least one diagram and a set of formulas, including the derivation of mathematics results. 5. Results section (35%): This section includes Steps 4 (M: model solution) and 5 (I: interpretation of the model and its results) of the 5-step approach and should have mathematical and numerical results from the solution of the math equations. The emphasis is on numerical results, calculated by R based on formulas. Imbed the R code in your report. You should attempt to interpret the numerical results using words for your client. Please make a comprehensive sensitivity analysis. The sensitivity analysis may be the most valuable information to your client. This section should include at least one table. He often presents this section when one reports to his client. This is the most important section for your client because he wants to know the results first. 6. Conclusion section and discussion (10%): Summarize your work and discuss other alternatives to the problem. Discuss pros and cons of your method. 7. References section (5%): List at least one reference. Total: 100% You should use double space and 12-point font size. The total number of pages should be at least 8, including a title page, an abstract page, and six or more report body pages. It is usually a good idea to include more figures and tables. Data Insights Consulting Maximizing Interest-Profit with Respect to Different Mortgage Lengths Name 3222 House Street, San Diego , California 92000 Phone: 619-999-9999 Email: author@gmail.com 2|Page Abstract: When dealing with fixed mortgages, one of the primary questions many lending institutions have is how interest-profit, defined as profit generated from charging interest to each remaining monthly principal amount, can be maximized depending on factors including the annual interest rate, principal amount and length of the loan. Considering the 10-year, 15-year, 20-year and 30-year fixed loan types, we concluded interest-profit maximization was dependent on higher annual interest rates and longer loan lengths—both of which contributed to smaller monthly payments over longer periods of time, thus allowing the principal loan amount to be depleted slowly and interest-profit to greatly accumulate. In reaching this conclusion, we first derived a formula for calculating the overall monthly payment and subsequently quantified how much of the monthly payment was paid towards interest accumulation (interest profit) and the principal amount. Using the resulting monthly payments towards interest, we examined each fixed loan type individually and comparatively, finally concluding with a sensitivity analysis on the annual interest rate factor. Introduction: In the world of fixed mortgages, in which length until maturity, interest rates—and its associated interest-revenue—and monthly payments are all subject to variability, this report seeks to determine the most profitable outcomes viable to mortgage lending institutions based on each borrower’s criteria. 3|Page In assessing these criteria, we will construct comparisons for the 30-year and 15-year fixed mortgages, including an examination of more unique mortgage lengths such as the 10year and 20-year fixed mortgages. Assuming a fixed mortgage, we will construct a generalized model that can be tailored to each specific situation, in which further adjustments to the interest rate are applied to assess relevant interest-profit questions. All computations are done through the statistical programming software R, with relevant mathematical notation first being presented in its most generalized form, followed by specific examples of different fixed mortgages for comparative discussion of maximizing interest-profit given borrowers’ preferences. Interest rates associated with the 30-year and 15-year fixed mortgages are gathered from the Federal Reserve Economic Data (FRED) as the average annual rate recorded on November 14th, 2019. Interest rates associated with the 10-year and 20-year fixed mortgages are retrieved from Bankrate as national averages on November 20th, 2019 which are 3.14% and 3.65%, respectively. Data and Method: In forming our fixed mortgage model, we must define certain parameters necessary for specific calculations. Among these parameters, are: • P = principal mortgage loan ($) • • 𝑷𝒊 = remaining total mortgage ($) for i = { 1, 2, 3, … ,k-1, k } month r = monthly interest rate (annual interest rate / 12 months) 4|Page • n = number of months until maturity (number of years * 12 months) • x = monthly payment ($) Since interest is a compounding property, any model we construct must take this property into account so that with each monthly iteration, the interest on the principal loan— denoted as interest-revenue from this point forward—is considered. Hence, we begin constructing our model at the end of the first month, when the first monthly payment is due: 𝑃1 = 𝑃 + 𝑟 ∗ 𝑃 − 𝑥 𝑃1 = 𝑃 ∗ (1 + 𝑟) − 𝑥 *Notice: the interest-revenue term: (𝑟 ∗ 𝑃) is added together with the original loan amount. Proceeding to the second month, we write: 𝑃2 = 𝑃1 + 𝑟 ∗ 𝑃1 − 𝑥 𝑃2 = 𝑃1 ∗ (1 + 𝑟) − 𝑥 Then, substituting for 𝑃1: 𝑃2 = [𝑃 ∗ (1 + 𝑟) − 𝑥] ∗ (1 + 𝑟) − 𝑥 𝑃2 = 𝑃(1 + 𝑟)2 − 𝑥(1 + 𝑟) − 𝑥 𝑃2 = 𝑃(1 + 𝑟)2 − 𝑥 ∗ [(1 + 𝑟) + 1] Considering the compounding effects on interest on principal and the monthly payment, we are ready to extend the model to the principal loan amount remaining after an arbitrary k-months: 𝑃𝑘 = 𝑃𝑘−1 + 𝑟 ∗ 𝑃𝑘−1 − 𝑥 𝑃𝑘 = 𝑃𝑘−1 ∗ (1 + 𝑟) − 𝑥 5|Page Then, substituting for 𝑃𝑘−1, the month prior to 𝑃𝑘: 𝑃𝑘 = 𝑃(1 + 𝑟)𝑘 − 𝑥(1 + 𝑟)𝑘−1−. . . −𝑥(1 + 𝑟)2 − 𝑥(1 + 𝑟) − 𝑥 𝑃𝑘 = 𝑃(1 + 𝑟)𝑘 − 𝑥 ∗ [(1 + 𝑟)𝑘−1+. . . +(1 + 𝑟)2 + (1 + 𝑟) + 1] Again, noticing interest’s compounding effect on previous monthly payments, we detect a recurring pattern that the longer a mortgage takes to fulfill, the less profit—from interestrevenue in the long-run—remains to be earned, since the interest detracts from each prior monthly payment. Furthermore, we can re-write the generalized expression in its closed form, such that: 1 − (1 + 𝑟)𝑘 𝑃𝑘 = 𝑃(1 + 𝑟)𝑘 + 𝑥 ∗ [ ] 𝑟 Finally, let’s note certain assumptions of our generalized monthly mortgage model: • The monthly interest rate cannot be equal to 0%, (𝑟 ≠ 0), and by extension, the annual interest rate cannot be equal to 0% • The principal loan amount at the end of each month, (𝑃𝑖) will decrease for every i = {1, 2, 3, …, k} months, assuming the monthly payment is greater than $0, (𝑥 > 0) • Should 𝑃𝑘 = $0, then the loan is paid off, allowing us to determine the monthly payment required to fully pay off the loan provided values for the interest rate, mortgage term, and original loan amount parameters Further results stemming from the calculated monthly payment are derived as the payment towards the principal loan amount and payment towards interest-generated profit, denoted, respectively, as 𝒙𝒑 and 𝒙𝒊 . 6|Page The monthly payment can then be broken into two parts, which when summed, equal the monthly payment: 𝑥 = 𝑥𝑝 + 𝑥𝑖 Both 𝑥𝑝 and 𝑥𝑖 are subject to change each month, as the portion of the monthly payment going towards interest-generated profit (𝑥𝑖) declines in the long-run. This phenomenon is caused by consistent monthly payments reducing the original loan amount, thereby limiting the amount of interest-profit to be generated from the loan closer to its maturity date. Calculating the monthly payment towards interest-profit for the first month involves, 𝑥𝑖𝑗 = 𝑃𝑗 ∗ 𝑟 , 𝑓𝑜𝑟 𝑗 = {1, 2, 3, … , 𝑘 − 1, 𝑘} 𝑚𝑜𝑛𝑡ℎ𝑠 Where each month’s loan amount is multiplied by the interest rate to get the portion of the monthly payment devoted to interest-profit for the bank. Knowing 𝑥𝑖 for each month, we can then calculate the remaining amount of the monthly payment devoted to paying off the principal amount of the initial loan: 𝑥𝑝𝑗 = 𝑥 − 𝑥𝑖𝑗 , 𝑓𝑜𝑟 𝑗 = {1, 2, 3, … , 𝑘 − 1, 𝑘} 𝑚𝑜𝑛𝑡ℎ𝑠 Results: After determining the generalized model, our next step is to figure out the advantages/disadvantages of monthly payments provided at different loan maturity dates, including those of the 30-year, 15-year, 40-year and 10-year mortgages. Let’s first determine the monthly payment necessary to fully pay off a 30-year loan at $150,000 with an annual average interest rate of 3.89%. 7|Page Principal loan Monthly Rate # of Months $150,000 0.32% 360 Set the amount of principal leftover to $0 to determine monthly payment, such that: 𝑘+ 𝑥 ∗ [1 − (1 + 𝑟)𝑘] 𝑃360 = $0 = 𝑃 ∗ (1 + 𝑟) 𝑟 $0 Isolating the monthly payment (x) to one side, we have: 𝑃∗(1+𝑟)𝑘∗𝑟 𝑥= $150000∗(1+00.0032)360∗0.0032 (1+𝑟)𝑘−1 → Now knowing the monthly payment, we can inspect the monthly interest profit in several ways: Figure 1: (a) 8|Page (b) (c) 9|Page Examining figure 1(a), we see a concave curve generating a strong amount of monthlyinterest profit near the issuance of the loan—its rate of change, referred to as the change in monthly interest profit for each month decreases relatively rapidly as the loan approaches its maturity date. As presented in figure 1(b), the cumulative monthly interest profit curve (derived by adding each prior month’s interest profit to the current month’s interest profit) affirms the previous assertion that the beginning months of the loan generate the greatest, or most significant, interest payments from the borrower, while the time span nearing the loan’s maturity, represented by the curve’s nearly flat slope after 300 months, presented relatively insignificant contributions to the total interest profit of $104,391.60. (d) 10 | P a g e Figure 1(c) plots both the monthly interest profit curve and the monthly payment towards the principal loan curve, both of which, when added for any month, equal the monthly 11 | P a g e payment of $694.67. At the issuance of the loan, monthly interest profit payments are greater than monthly principal payments—graphically represented by the interest profit curve being above the monthly principal curve—until reaching the 149th month, in which both curves intersect, resulting in monthly payments to the principal loan taking a greater proportion of the overall monthly payment, x. The two curves—monthly interest payment and monthly principal payment—demonstrate a complementary relationship over the monthly payment graph’s length of time—as the monthly principal payment curve continually increases over time, the monthly interest payment curve continually decreases. As shown in figure 1(d), the percentages of the monthly payment towards interestgenerated profit (displayed in decimal form) are plotted as a histogram, with the horizontal and vertical axes corresponding to the proportion of the monthly payment towards interest on the loan and frequency, respectively. On average, 41% of $694.67, or $284.82, will be paid towards the interest generated from the loan each month for 360 months—holding in consideration that percentages less than 40% (0.4) maintain an approximate uniform shape, while percentages greater than 40% (0.4) take on a more hierarchical shape. It should be noted that larger percentages of the monthly payment towards interest profit are directly tied to the initial months of the loan’s issuance, and continually decrease as it approaches maturity. Secondly, let’s examine the monthly payment necessary to fully pay off a 15-year loan of $150,000 at an annual interest rate of 3.2%. Then we will discuss the interest-generated profit associated with each monthly payment. Principal loan Monthly Rate # of Months $150,000 0.267% 180 12 | P a g e Using the formula previously provided, the monthly payment, x, is $1,050.36. Figure 2(a): (b) (c) 13 | P a g e Over the course of 180 months, the monthly interest profit curve (figure 2(a)) maintains a relatively constant rate of change, implying fluctuations in how much monthly interest profit the banking establishment receives are approximately linear and constant in nature. The cumulative interest profit curve in figure 2(b) demonstrates that, using both the 30-year (figure 1(b)) and 15-year loans as examples, despite the length of time assigned to the loan, monthly contributions to the total interest profit generated by the loan prove significant until reaching the month in which the loan is 72% fully paid. For the 30-year loan, the cumulative interestprofit curve begins to flatten, thereby reducing its rate of growth in total interest-profit, around the 260-month mark. The 15-year loan’s cumulative interest profit curve begins to flatten around the 130-month mark. Hence, 260 𝑚𝑜𝑛𝑡ℎ𝑠 130 𝑚𝑜𝑛𝑡ℎ𝑠 72% 360 14 | P a g e (d) Unlike the 30-year loan, the 15-year loan’s monthly principal payment and monthly interest payments never intersect. The monthly principal payments grow as the loan matures, but given the short length of time required to fully pay the loan off, as opposed to the 30-year loan, monthly interest payments will never exceed monthly principal payments for two main reasons: • The interest rate of the 15-year loan is smaller (by approximately 17%) than that of the 30-year loan • The monthly payment of the 15-year loan is much larger (by approximately 33.4%) than that of the 30-year loan 15 | P a g e Because of the significantly larger monthly payment—subtracting equally large amounts of the original loan away each month—the interest-profit suffers greatly. Hence, we can reasonably conclude the loan’s maturity plays a significant role in generating interest-profit for the lender. In figure 2(d), the average monthly interest payment is 21% of the entire monthly payment, or $220.58. Now that we’ve completed individual assessments of the more popular 30-year and 15-year loans, we’ll do the same with the lesser known 20-year and 10-year variants. Examination of a $150,000 10-year loan at an annual interest rate of 3.14%: Principal loan Monthly Rate # of Months $150,000 0.261% 120 Using the formula previously provided, the monthly payment, x = $1,458.13. Figure 3(a): 16 | P a g e (b) 17 | P a g e (c) 18 | P a g e As expected, given the shorter length of time to pay off the loan, the required monthly payment will be much larger than the 15-year and 30-year, necessarily prompting the interest rate to be adjusted lower as well. The monthly payments (figure 3(a)) follow an approximately linear line, resulting from a smaller interest rate and shorter time to pay off the loan. The total interest profit generated is expectedly less, only amounting to $24,974.99, and is explained by figure 3(c), showing the monthly interest payment curve starting far below the monthly principal payment curve. (d) On average, the monthly interest payment will be 14% of the monthly payment ($1,458.13), or $204.14 (figure 3(d)). The monthly interest payment of the 10-year loan is a little over 29% less than that of the 30-year loan and about 7% less than that of the 15-year loan. Compared to the 30-year loan, the 10-year loan produces a whopping 76% less total 19 | P a g e interest profit (given its shorter duration), and compared to the 15-year loan, generates 36% less total interest profit. Examination of a $150,000 20-year loan at an annual interest rate of 3.65%: Principal loan Monthly Rate # of Months $150,000 0.304% 240 The monthly payment given the above parameters is 𝑥 = $881.55. Figure 4(a): The 20-year monthly payment curve is not as linear (or, flat) as the previous 10-year monthly payment curve, demonstrating the effect of a higher interest rate and longer loan length in maximizing interest-profit. 20 | P a g e (b) (c) 21 | P a g e Like the 10-year loan, the 20-year loan is more uncommon than its widely-offered 15year and 30-year counterparts and offers an interesting variant for borrowers and lenders alike. The monthly payment is smaller than that of the 10-year and 15-year, but also offers monthly interest payments exceeding monthly principal payments for a short period of time. (d) 22 | P a g e The average percent monthly interest payment histogram is skewed to the right, suggesting that at issuance of the loan, monthly payments toward interest exceed 40%. As the loan matures however, the histogram approximately follows a uniform distribution with an average monthly payment of $255.65. Figure 5(a): 23 | P a g e (b) The graphs of figure 5 present a comparison of the monthly interest payments—notice: 24 | P a g e as both the loan length and interest rate decline, the curves become increasingly linear and steeper, representative of a larger monthly payment more rapidly reducing the original loan amount—and cumulative total interest-profit. Now our attention turns to assessing the effect of slight variations in the annual interest rate of each loan length to determine the average monthly interest payment as a percent of the overall monthly payment, x. Assume the original loan amount remains fixed at $150,000, and the interest rate is subject to change (a variable)—each element of the table represents the average percentage of the monthly payment devoted to paying for the monthly-interest profit, see figures 1,2,3,4(d) for reference. Average Percent of Monthly Payment Paid Towards Interest-Profit Generated from Loan (Table 1) 3.00% 3.25% 3.5% 3.75% 4.00% 4.25% 4.50% 10-Year 13.69% 14.72% 15.73% 16.71% 17.69% 18.65% 19.59% 15-Year 19.55% 20.94% 22.29% 23.61% 24.89% 26.15% 27.38% 20-Year 24.87% 26.54% 28.16% 29.72% 31.24% 32.71% 34.14% 30-Year 34.11% 36.17% 38.14% 40.02% 41.82% 43.53% 45.18% Provided the parameters listed above, the average percentage table (table 1) demonstrates how the annual interest rate, when raised by a quarter of a percentage, increases the average percent of monthly payment paid towards interest-profit. Shorter loan lengths exhibit smaller average monthly interest payments, since the overall monthly payment is larger 25 | P a g e and reduces the principal at a faster rate than the 30-year or 20-year loan types, which feature smaller monthly payments, allowing for greater accumulation of interest-profit. In line with our individual results for each loan type, the comparative analysis confirms the two factors significantly affecting the amount of interest-profit generated for the lending institution are the loan’s annual interest rate and length of time until full repayment. Conclusion: The aim of this report was to provide insight into interest-profit maximization of loans given their annual interest rates, principal amount values and lengths of time until repayment. By calculating their overall monthly payment, we derived two explicit characteristics including the monthly payment towards interest-profit (or, monthly interest payment) and the monthly payment towards the principal amount (or, monthly principal payment). Focusing on the monthly interest payments, we examined the cumulative—total—interest profitability associated with the 10-year, 15-year, 20-year and 30-year loan types assuming a fixed principal amount of $150,000 and varying annual average interest rates retrieved from the Federal Reserve and Bankrate.com. From our analysis of loan types, individually and comparatively, we concluded that interestprofit maximization occurred when: 1. The length of the loan was long 2. The annual interest rate associated with the loan was high 26 | P a g e Although these results are mathematically correct, we must note that in terms of economic theory, proper incentive must be offered to entice borrowers (demanding a loan) to the lending institution (supplying the loan). Therefore, excessively high annual interest rates outside of the 2-5% interval offered by many lending competitors are unrealistic, as demand naturally flows to institutions with credibility and low annual interest rates. Since borrowers have unique needs, credit ratings and other liabilities, both the length of the loan and annual interest rate should be adjusted accordingly to best accommodate both the client and lending institution. Our analysis was limited to fixed interest rate loan types, and application of these results to loans featuring variable interest rates should be cautioned against. References: “Bankrate: Master Life's Financial Journey.” Bankrate.com - Compare Mortgage, Refinance, Insurance, CD Rates, https://www.bankrate.com/. “Mortgage Rates.” FRED, Federal Reserve Bank of St. Louis, https://fred.stlouisfed.org/categories/114. Shen, Samuel S.P. Introduction to Modern Mathematical Modeling with R. N.p.: WileyInterscience, 2017. Print. Appendix: #R-Code to generate monthly payment, monthly principal & interest payments #Set principal to specific value P30
Purchase answer to see full attachment