Question

Total Loan amount: The total mortgage loan amount is the amount you borrow after paying your down payment. Here, we assumed that you would pay 20% of the home value (property value) as a down payment. 2. Months: The mortgage payment period is set to 30 years. In terms of months, this is equivalent to 30 years multiplied by 12 months. We put our primary basis of payments in terms of months, which is why we need to convert everything into months. 3. Monthly Rate: The monthly rate relates to the “actual rate” that we have learned in class. Since the Annual Percentage Rate (APR) is given as 4.25%, the actual rate is 4.25% divided by 12. 4. Payment: Using the Excel function for amortized payments, we would need the following information to compute the monthly payment. =PMT(rate, nper, -pv) =PMT(monthly rate, 360 months, -total loan) Note that the payment amount per month is the same every month. Do also note to anchor all payment amounts. This is necessary because you will be dragging the cells down until period 360, without changing its value. 5. Beginning Value: The beginning value equals the outstanding loan amount. Note that the “Ending Balance” of the previous period is equal to the “Beginning Value” in the following period. 6. Interest: For the interest in an amortization schedule, we use a new Excel formula called IPMT, short for interest payment. = IPMT(rate, per, nper, -pv) = IPMT($Monthly rate$, Nth payment, $360 payment periods$, -$total loan amount$) It is noted that except for the “Nth payment,” you would need to anchor the rest of the information because you will drag down the cells, whereby only the “Nth payment” differs by cells. FIRE 371 Excel #5 2 7. Principal: Here, again, we will be utilizing a new Excel formula called PPMT, which stands for principal payment. =PPMT(rate, per, nper, -pv) =PPMT($Monthly rate$, Nth payment, $360 payment periods$, -$total loan amount$) Again, it is noted that except for the “Nth payment,” you would need to anchor the rest of the information because you will drag down the cells, whereby only the “Nth payment” differs. 8. Ending Balance: Recall that the ending balance is the remaining amount in your loan when you deduct the amount of the principal payment you have made in a certain period. Thus, the first ending balance is the remainder of $240,000 minus $330.66. This ending balance in the first period will then be the beginning value (or balance) in the second period. 1. Monthly Income: We assume that you will be earning $100K year. Again here, we convert this amount at a monthly basis by dividing $100,000 into 12 months. 2. Qualify?: Here, we have to assess whether you qualify for a mortgage. For this assessment, we use the Debt-to-Equity ratio of 28% as a threshold. If you are below this rate, you are qualified. For the evaluation, we will utilize the IF function in Excel. Your monthly debt is the outstanding mortgage payment, plus the taxes and insurance fee of $350. =IF (logical test, value if True, value if False) = IF ((Monthly Payment +Tax and Insurance) / Monthly Income < Debt/Income Ratio Threshold, “YES”, “NO”) Now, we want to estimate your home value at the end of year 6 (72nd period). For this, we assume that the market is at an upmarket where home values are at an increasing trend of 4%. Again, the present value, or in other words, the value of your home is given as $300,000. 1. FV: The future value is based on the assumption that the value of your home will increase by 4% per year. Here, we will shift gears and assess based on a yearly basis. =FV(rate, nper, pmt, -pv) =FV(4%, 6 years, 0, -home value) 2. FV-PV: The difference between the future and present value is the “Increase in Value.” This output is used in computing the “Total Housing Equity at the End of Year 6.” FIRE 371 Excel #5 3 When computing this value, we have ‘Three Pillars’ to consider. 1) The down payment that you already have invested in your home, 2) Increase of your home value in the market, and 3) The amount that you already have paid in your mortgage. 3. Loan – Balance End of Year 6: When you look at the “Ending Balance” of the 72nd period (end of year 6) in the amortization table, the balance left to pay is $212,936.30. The total loan amount you have to pay is again $240,000 after your down payment of $60,000 (i.e., 20% of the home value). The amount difference between the total loan amount and the balance left to pay is the money that you have paid so far until year 6. This is because Total Loan = “Money you have paid” + Ending Balance. This is the third pillar in computing your home’s equity value at the end of year 6: The Principal Draw Down. 4. The SUM of the three pillars consists of the initial down payment, the increase in your property value, and the money you have paid so far until the end of year 6.