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 2 years 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...
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...
Here is a script created a few years back. It has a few issues. See if...
Here is a script created a few years back. It has a few issues.
See if you can make it work.
#!/bin/bash
# created by Your Name
# date
quit=no
while [ $quit = no ]
do
cat <<++
1) Roll Again?
2) Quit
++
# code to roll dice
read selection
case $selection in
1)
echo
“Enter a number between 1 and 10”
read
repeats
let
seed=0
let
r=0
while [...
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
- Training and Development assignment. Describe the usage of Computer Based Training (CBT) in McDonald's Company training...
- PUT IN JAVA PROGRAMMING LANGUAGE The Rectangle class: Design a class named Rectangle to represent a...
- Journal article summary on inter-group conflict Peer Reviewed Journal article from past 3 years. Please include...
- "Ethical Considerations" Please respond to the following: Reflect upon the responsibilities placed on auditors by the...
- A Japanese company has a bond outstanding that sells for 93 percent of its ¥100,000 par...
- What is the difference among the principles of modularization, abstraction, encapsulation, and separation of interface and...
- An investment project costs $20,100 and has annual cash flows of $4,400 for six years. ...
ADVERTISEMENT