A few years back, Dave and lana bought a new home. They borrowed $230,415 at an annual foved rate of 5.49% (15-year term with monthly payments of $1.651.46. They just made their 300 payment and the current balance on the loan is $206,555.07. Interest rates are at an all-time low, and Dave and Jana are thinking of refinancing to a new 15-year fixed ban. Their bane has made the following offer 35-year term. 3.0% plus cut-of-pocket costs or 2.937. The out-of-pocket costs must be paid in full at the time of refinancing Build a spreadsheet model to evaluate this offer. The Excel function: PHT(rate, per, ov, pel calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this functionare rate the interest rate for the loan per the total number of payments py - present value the amount borrowed) future vue the desired cash balance after the last payment (usually o) Type payment type (0 - end of period, 1 - beginning of the period) For example, for Dave and Jana't anginat loan, there will be 180 payments (17*15= 180), so we would use MT(0.054912. 180, 220415.0,0) - $1.851.46. Note that because payments are made monthly, the annual interest rate must be expressed as a monthly rate. Also for payment calculation, we assume that the payment is made at the end of the month The savings from refinancing occur over time, and therefore need to be discounted back to current doars. The formula for converting dollars saved months from now to current dollari * where is the montre ination rate. Assume that r=0.002 and that Dave and Jan make their payment the end of each month Use your model to calculate the savings in current dotars associated with the refinanced loan rus staying with the originalan If required, round your answer to the rest whole doar amount. If your answer is negative use 'minus sin Screenrec A few years back, Dave and lana bought a new home. They borrowed $230,415 at an annual foved rate of 5.49% (15-year term with monthly payments of $1.651.46. They just made their 300 payment and the current balance on the loan is $206,555.07. Interest rates are at an all-time low, and Dave and Jana are thinking of refinancing to a new 15-year fixed ban. Their bane has made the following offer 35-year term. 3.0% plus cut-of-pocket costs or 2.937. The out-of-pocket costs must be paid in full at the time of refinancing Build a spreadsheet model to evaluate this offer. The Excel function: PHT(rate, per, ov, pel calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this functionare rate the interest rate for the loan per the total number of payments py - present value the amount borrowed) future vue the desired cash balance after the last payment (usually o) Type payment type (0 - end of period, 1 - beginning of the period) For example, for Dave and Jana't anginat loan, there will be 180 payments (17*15= 180), so we would use MT(0.054912. 180, 220415.0,0) - $1.851.46. Note that because payments are made monthly, the annual interest rate must be expressed as a monthly rate. Also for payment calculation, we assume that the payment is made at the end of the month The savings from refinancing occur over time, and therefore need to be discounted back to current doars. The formula for converting dollars saved months from now to current dollari * where is the montre ination rate. Assume that r=0.002 and that Dave and Jan make their payment the end of each month Use your model to calculate the savings in current dotars associated with the refinanced loan rus staying with the originalan If required, round your answer to the rest whole doar amount. If your answer is negative use 'minus sin Screenrec
