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 jus heir 45th payment, and the current balance on the loan is $208,555.87. nterest 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%, p 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: rate the interest rate for the loan per the total number of payments pv= present value (the amount borrowed) fv = future value [the desired cash balance after the last payment (usually 0)] type payment type (0 = end of period, 1 = beginning of the period) 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 becaus 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 t 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 cur dollars is:
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 jus heir 45th payment, and the current balance on the loan is $208,555.87. nterest 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%, p 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: rate the interest rate for the loan per the total number of payments pv= present value (the amount borrowed) fv = future value [the desired cash balance after the last payment (usually 0)] type payment type (0 = end of period, 1 = beginning of the period) 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 becaus 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 t 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 cur dollars is:
Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
Related questions
Question
![### Evaluating a Refinancing Offer: An Educational Guide
#### Scenario:
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 45th 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.
#### Objective:
Build a spreadsheet model to evaluate this offer using the Excel function:
```
=PMT(rate, nper, pv, fv, type)
```
#### Function Explanation:
The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this function are:
- **rate** = the interest rate for the loan
- **nper** = the total number of payments
- **pv** = present value (the amount borrowed)
- **fv** = future value (the desired cash balance after the last payment, usually 0)
- **type** = payment type (0 = end of period, 1 = beginning of the period)
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.
#### Discounting Savings:
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:
\[
\frac{K}{(1 + r)^{t-1}}
\]
where \( r \) is the monthly inflation rate. Assume that \( r = 0.002 \) and that Dave and Jana make their](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F52476ee9-c360-4937-8f5a-8576cacac103%2Ffd4b91a7-9d91-4e3c-bf55-7fab0acba4df%2Flj1jjwf_processed.png&w=3840&q=75)
Transcribed Image Text:### Evaluating a Refinancing Offer: An Educational Guide
#### Scenario:
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 45th 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.
#### Objective:
Build a spreadsheet model to evaluate this offer using the Excel function:
```
=PMT(rate, nper, pv, fv, type)
```
#### Function Explanation:
The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this function are:
- **rate** = the interest rate for the loan
- **nper** = the total number of payments
- **pv** = present value (the amount borrowed)
- **fv** = future value (the desired cash balance after the last payment, usually 0)
- **type** = payment type (0 = end of period, 1 = beginning of the period)
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.
#### Discounting Savings:
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:
\[
\frac{K}{(1 + r)^{t-1}}
\]
where \( r \) is the monthly inflation rate. Assume that \( r = 0.002 \) and that Dave and Jana make their
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 2 images
Recommended textbooks for you
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Finance
ISBN:
9780077861759
Author:
Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:
McGraw-Hill Education