Question
In: Finance
A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an...
|
A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an annual fixed rate of 5.49% (15-year term) with monthly payments of $1,881.46. They just made their 25th payment, and the current balance on the loan is $208,555.87. Interest rates are at an all-time low, and Dave and Jana are thinking of refinancing to a new 15-year fixed loan. Their bank has made the following offer: 15-year term, 3.0%, plus out-of-pocket costs of $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: =PMT(rate, nper, pv, fv, type) calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this function are:
For example, for Dave and Jana’s original loan, there will be 180 payments (12*15 = 180), so we would use =PMT(0.0549/12, 180, 230415,0,0) = $1,881.46. Note that because payments are made monthly, the annual interest rate must be expressed as a monthly rate. Also, for payment calculations, 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 dollars. The formula for converting K dollars saved t months from now to current dollars is: where r is the monthly inflation rate. Assume that r = 0.002 and that Dave and Jana make their payment at the end of each month. Use your model to calculate the savings in current dollars associated with the refinanced loan versus staying with the original loan. If required, round your answer to the nearest whole dollar amount. If your answer is negative use “minus sign”. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Solutions
Expert Solution
As per the new term loan, the monthly Payment would be $1440.25
That would mean a saving of $441.21 per month for the remaining 155 months (180-25). However we have to keep in mind the extra 1440.25 that we have to pay for extra 25 months as the new term is of 15 years.
So the NPV comes out to be 33010.85
But we have to pay upfront cost of 2937. That would still be better than the current Loan Structure
as 33010.85-2937 is still positive Savings
| Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 | 63 | 64 | 65 | 66 | 67 | 68 | 69 | 70 | 71 | 72 | 73 | 74 | 75 | 76 | 77 | 78 | 79 | 80 | 81 | 82 | 83 | 84 | 85 | 86 | 87 | 88 | 89 | 90 | 91 | 92 | 93 | 94 | 95 | 96 | 97 | 98 | 99 | 100 | 101 | 102 | 103 | 104 | 105 | 106 | 107 | 108 | 109 | 110 | 111 | 112 | 113 | 114 | 115 | 116 | 117 | 118 | 119 | 120 | 121 | 122 | 123 | 124 | 125 | 126 | 127 | 128 | 129 | 130 | 131 | 132 | 133 | 134 | 135 | 136 | 137 | 138 | 139 | 140 | 141 | 142 | 143 | 144 | 145 | 146 | 147 | 148 | 149 | 150 | 151 | 152 | 153 | 154 | 155 | 156 | 157 | 158 | 159 | 160 | 161 | 162 | 163 | 164 | 165 | 166 | 167 | 168 | 169 | 170 | 171 | 172 | 173 | 174 | 175 | 176 | 177 | 178 | 179 | 180 | |
| Old Pmt | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | 1881.46 | ||||||||||||||||||||||||||
| New Pmt | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | 1,440.25 | |
| Saving | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | $441.21 | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | ######## | |
| Rate | Discounted Value | 440.33079 | 439.45189 | 438.5747 | 437.6993 | 436.8257 | 435.9538 | 435.0836 | 434.2152 | 433.3485 | 432.4835 | 431.6203 | 430.7588 | 429.899 | 429.0409 | 428.1845 | 427.3299 | 426.4769 | 425.6256 | 424.7761 | 423.9282 | 423.0821 | 422.2376 | 421.3948 | 420.5537 | 419.7143 | 418.8765 | 418.0404 | 417.206 | 416.3733 | 415.5422 | 414.7128 | 413.885 | 413.0589 | 412.2344 | 411.4116 | 410.5904 | 409.7709 | 408.953 | 408.1367 | 407.322 | 406.509 | 405.6976 | 404.8879 | 404.0797 | 403.2731 | 402.4682 | 401.6649 | 400.8632 | 400.063 | 399.2645 | 398.4676 | 397.6722 | 396.8785 | 396.0863 | 395.2957 | 394.5067 | 393.7193 | 392.9334 | 392.1491 | 391.3664 | 390.5852 | 389.8056 | 389.0275 | 388.251 | 387.4761 | 386.7027 | 385.9308 | 385.1605 | 384.3917 | 383.6244 | 382.8587 | 382.0945 | 381.3319 | 380.5707 | 379.8111 | 379.053 | 378.2964 | 377.5413 | 376.7878 | 376.0357 | 375.2851 | 374.536 | 373.7885 | 373.0424 | 372.2978 | 371.5547 | 370.813 | 370.0729 | 369.3342 | 368.597 | 367.8613 | 367.1271 | 366.3943 | 365.6629 | 364.9331 | 364.2047 | 363.4777 | 362.7522 | 362.0282 | 361.3055 | 360.5844 | 359.8646 | 359.1464 | 358.4295 | 357.7141 | 357.0001 | 356.2875 | 355.5763 | 354.8666 | 354.1583 | 353.4514 | 352.7459 | 352.0418 | 351.3391 | 350.6379 | 349.938 | 349.2395 | 348.5424 | 347.8467 | 347.1524 | 346.4595 | 345.768 | 345.0778 | 344.389 | 343.7016 | 343.0156 | 342.3309 | 341.6476 | 340.9657 | 340.2851 | 339.6059 | 338.9281 | 338.2516 | 337.5764 | 336.9026 | 336.2301 | 335.559 | 334.8892 | 334.2208 | 333.5537 | 332.8879 | 332.2235 | 331.5604 | 330.8986 | 330.2381 | 329.5789 | 328.9211 | 328.2646 | 327.6093 | 326.9554 | 326.3028 | 325.6515 | 325.0015 | 324.3528 | 323.7054 | -1054.56 | -1052.46 | -1050.36 | -1048.26 | -1046.17 | -1044.08 | -1042 | -1039.92 | -1037.84 | -1035.77 | -1033.7 | -1031.64 | -1029.58 | -1027.53 | -1025.47 | -1023.43 | -1021.38 | -1019.35 | -1017.31 | -1015.28 | -1013.25 | -1011.23 | -1009.21 | -1007.2 | -1005.19 |
| 0.002 |
jeff jeffy answered 1 year agoRelated Solutions
A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an...
A few years back, Dave and Jana bought a new home. They borrowed
$230,415 at an annual fixed rate of 5.49% (15-year term) with
monthly payments of $1,881.46. They just made their 40th payment,
and the current balance on the loan is $208,555.87. Interest rates
are at an all-time low, and Dave and Jana are thinking of
refinancing to a new 15-year fixed loan. Their bank has made the
following offer: 15-year term, 3.0%, plus out-of-pocket costs of
$2,937. The...
A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an...
A few years back, Dave and Jana bought a new home. They borrowed
$230,415 at an annual fixed rate of 5.49% (15-year term) with
monthly payments of $1,881.46. They just made their 50th payment,
and the current balance on the loan is $208,555.87. Interest rates
are at an all-time low, and Dave and Jana are thinking of
refinancing to a new 15-year fixed loan. Their bank has made the
following offer: 15-year term, 3.0%, plus out-of-pocket costs of
$2,937. The...
Bill has bought a new home in Canberra. He borrowed $600 000 at a rate of...
Bill has bought a new home in Canberra. He borrowed $600 000 at
a rate of 3.5% p.a., which is to be repaid in annual instalments
over a thirty year period. The first instalment is due on 19 March
2020.
Like Bill, on the situation above, Scott has bought a house in
Canberra, borrowing the same amount, and on the the same terms.
Scott’s bank, however, offers an ‘interest offset’ account facility
with the loan. Like Bill, Scott’s first payment...
1. Bill has bought a new home in Canberra. He borrowed $600 000 at a rate...
1. Bill has bought a new home in Canberra. He borrowed $600 000
at a rate of 3.5% p.a., which is to be repaid in annual instalments
over a thirty year period. The first instalment is due on 19 March
2020.
a. Choosing a valuation date of 19 March 2019, write down the
equation of value that will give Bill’s annual repayments. Support
your answer with a fully labelled cash flow diagram, drawn from
Bill’s perspective.
b. What are Bill’s...
During the last few years, Jana Industries has been too constrained by the high cost of...
During the last few years, Jana Industries has been too
constrained by the high cost of capital to make many capital
investments. Recently, though, capital costs have been declining,
and the company has decided to look seriously at a major expansion
program proposed by the marketing department. Assume that you are
an assistant to Leigh Jones, the financial vice president. Your
first task is to estimate Jana’s cost of capital. Jones has
provided you with the following data, which she...
During the last few years, Jana Industries has been too constrained by the high cost of...
During the last few years, Jana Industries has been too
constrained by the high cost
of capital to make many capital
investments. Recently, though, capital costs have been declining,
and the company has decided to look seriously at a major expansion
program proposed by the marketing department. Assume that you are
an assistant to Leigh Jones, the financial vice president. Your
first task is to estimate Jana’s cost of capital. Jones has
provided you with the following data, which she...
During the last few years, Jana Industries has been too constrained by the high cost of...
During the last few years, Jana Industries has been too
constrained by the high cost
of capital to make many capital
investments. Recently, though, capital costs have been declining,
and the company has decided to look seriously at a major expansion
program proposed by the marketing department. Assume that you are
an assistant to Leigh Jones, the financial vice president. Your
first task is to estimate Jana’s cost of capital. Jones has
provided you with the following data, which she...
During the last few years, Jana Industries has been too constrained by the high cost of...
During the last few years, Jana Industries has been too
constrained by the high cost of capital to make many capital
investments. Recently, though, capital costs have been declining,
and the company has decided to look seriously at a major expansion
program proposed by the marketing department. Assume that you are
an assistant to Leigh Jones, the financial vice president. Your
first task is to estimate Jana’s cost of capital. Jones has
provided you with the following data, which she...
During the last few years, Jana Industries has been too constrained by the high cost of...
During the last few years, Jana Industries has been too
constrained by the high cost of capital to make many capital
investments. Recently, though, capital costs have been declining,
and the company has decided to look seriously at a major expansion
program proposed by the marketing department. Assume that you are
an assistant to Leigh Jones, the financial vice president. Your
first task is to estimate Jana’s cost of capital. Jones has
provided you with the following data, which she...
During the last few years, Jana Industries has been too constrained by the high cost of...
During the last few years, Jana Industries has been too
constrained by the high cost of capital to make many capital
investments. Recently, though, capital costs have been declining,
and the company has decided to look seriously at a major expansion
program proposed by the marketing department. Assume that you are
an assistant to Leigh Jones, the financial vice president. Your
first task is to estimate Jana’s cost of capital. Jones has
provided you with the following data, which she...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- 111 m3/min of hydrogen measured at normal conditions and nitrogen at a 300% excess enter an...
- A researcher wants to determine the impact of soil type on the growth of a certain...
- As a project manager, what can you do to ensure even consideration of each leg of...
- A company wants to reward key employees and is considering a restricted stock plan. They have...
- A spring stretches 4.0cm when a load of 20N suspended. How much will the spring stretch...
- 3. Problem 3-11 (Balance Sheet Analysis) Balance Sheet Analysis Complete the balance sheet and sales information in...
- Please typing if you can i will appreciate had my answers but i need to compare my...
ADVERTISEMENT