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:
Chapter4: Time Value Of Money
Section: Chapter Questions
Problem 28P
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
