Question
In: Finance
Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over...
Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over 20 years with monthlypayments. The annual interest rate is 5.5 %.
What is your monthly payment? $
What is the total dollar amount of payments made over the life of this loan? $__
What is the total dollar amount of interest paid over the life of this loan? $_
How many months will it take to pay off the loan if you pay an extra $100 per
month? ____
Solutions
Expert Solution
Ans 1) Monthly payment is given by PV of annuity formula
PV of annuity = paymnet * (1 - (1+r/12)^- 12*n)/(r/12)
275000 = payment * (1 - 1.0046^-240)/.0046
payment = $1891.69
total dollar amount of payments made over the life of this loan = 240 * $1891.69
= $454005.6
total dollar amount of interest paid over the life of this loan = total payment - principal
= $454005.6 - $275000
= $179005.6
Again we will use the PV of anuity formula
275000 = 1991.69 * (1 - (1.0046)^-n)/.0046
n = 219.1 Months
Loan Amortization schdule is given below but due to size issue it can't be be for 240 months.
| rate | 0.46% | |||||
| nper | 240 | |||||
| PV | 275000 | |||||
| FV | 0 | PMT | $1,891.69 | |||
| Month | Opening Balance | Payment | Interest | Principal Payment | Closing Balance | |
| 1 | $ 275,000.00 | $ 1,891.69 | $ 1,260.42 | $ 631.27 | $ 274,368.73 | |
| 2 | $ 274,368.73 | $ 1,891.69 | $ 1,257.52 | $ 634.17 | $ 273,734.56 | |
| 3 | $ 273,734.56 | $ 1,891.69 | $ 1,254.62 | $ 637.07 | $ 273,097.49 | |
| 4 | $ 273,097.49 | $ 1,891.69 | $ 1,251.70 | $ 639.99 | $ 272,457.49 | |
| 5 | $ 272,457.49 | $ 1,891.69 | $ 1,248.76 | $ 642.93 | $ 271,814.57 | |
| 6 | $ 271,814.57 | $ 1,891.69 | $ 1,245.82 | $ 645.87 | $ 271,168.69 | |
| 7 | $ 271,168.69 | $ 1,891.69 | $ 1,242.86 | $ 648.83 | $ 270,519.86 | |
| 8 | $ 270,519.86 | $ 1,891.69 | $ 1,239.88 | $ 651.81 | $ 269,868.05 | |
| 9 | $ 269,868.05 | $ 1,891.69 | $ 1,236.90 | $ 654.79 | $ 269,213.26 | |
| 10 | $ 269,213.26 | $ 1,891.69 | $ 1,233.89 | $ 657.80 | $ 268,555.46 | |
| 11 | $ 268,555.46 | $ 1,891.69 | $ 1,230.88 | $ 660.81 | $ 267,894.65 | |
| 12 | $ 267,894.65 | $ 1,891.69 | $ 1,227.85 | $ 663.84 | $ 267,230.81 | |
| 13 | $ 267,230.81 | $ 1,891.69 | $ 1,224.81 | $ 666.88 | $ 266,563.93 | |
| 14 | $ 266,563.93 | $ 1,891.69 | $ 1,221.75 | $ 669.94 | $ 265,893.99 | |
| 15 | $ 265,893.99 | $ 1,891.69 | $ 1,218.68 | $ 673.01 | $ 265,220.98 | |
| 16 | $ 265,220.98 | $ 1,891.69 | $ 1,215.60 | $ 676.09 | $ 264,544.89 | |
| 17 | $ 264,544.89 | $ 1,891.69 | $ 1,212.50 | $ 679.19 | $ 263,865.70 | |
| 18 | $ 263,865.70 | $ 1,891.69 | $ 1,209.38 | $ 682.31 | $ 263,183.39 | |
| 19 | $ 263,183.39 | $ 1,891.69 | $ 1,206.26 | $ 685.43 | $ 262,497.96 | |
| 20 | $ 262,497.96 | $ 1,891.69 | $ 1,203.12 | $ 688.57 | $ 261,809.38 | |
| 21 | $ 261,809.38 | $ 1,891.69 | $ 1,199.96 | $ 691.73 | $ 261,117.65 | |
| 22 | $ 261,117.65 | $ 1,891.69 | $ 1,196.79 | $ 694.90 | $ 260,422.75 | |
| 23 | $ 260,422.75 | $ 1,891.69 | $ 1,193.60 | $ 698.09 | $ 259,724.67 | |
| 24 | $ 259,724.67 | $ 1,891.69 | $ 1,190.40 | $ 701.29 | $ 259,023.38 |
jeff jeffy answered 2 years agoRelated Solutions
Create a loan amortization schedule in excell for a $27,000 car loan that will be repaid...
Create a loan amortization schedule in excell for a $27,000 car
loan that will be repaid over 48 months at an
annual interest rate of 6%.
What is your monthly payment? _$_
What is the total dollar amount of payments made over the life
of this loan? $____
What is the total dollar amount of interest paid over the life
of this loan? $___________
How many months will it take to pay off the loan if you pay an
extra...
Create an Amortization Schedule for a $275,000 office building to be purchased with a 30-year loan...
Create an Amortization Schedule for a $275,000 office building
to be purchased with a 30-year loan at an APR of 5% compounded
monthly. Determine the monthly payments and how much interest is
paid over the life of the loan. (format amortization schedule with
the following columns: month, beginning principal, monthly payment,
monthly interest, principal reduction, ending principal)
AMORTIZATION SCHEDULE a. Complete an amortization schedule for a $25,000 loan to be repaid in equal...
AMORTIZATION SCHEDULE
a. Complete an amortization schedule for a $25,000 loan to be
repaid in equal installments at the end of each of the next three
years. The interest rate is 8% compounded annually. Round all
answers to the nearest cent.
Beginning
Repayment
Ending
Year
Balance
Payment
Interest
of Principal
Balance
1
$
$
$
$
$
2
3
b. What percentage of the payment represents interest and what
percentage represents principal for each of the three years? Round
all...
Amortization schedule Set up an amortization schedule for a $36,000 loan to be repaid in equal...
Amortization schedule
Set up an amortization schedule for a $36,000 loan to be repaid
in equal installments at the end of each of the next 3 years. The
interest rate is 6% compounded annually. Round all answers to the
nearest cent.
Beginning
Remaining
Year
Balance
Payment
Balance
1
$
$
$
2
$
$
$
3
$
$
$
What percentage of the payment represents interest and what
percentage represents principal for each of the 3 years? Round all
answers...
Finance- Amortization Schedule Create the amortization schedule for a loan of $5,000, paid monthly over two...
Finance-
Amortization Schedule Create the amortization schedule for a
loan of $5,000, paid monthly over two years using an 8 percent
APR.
a. Complete an amortization schedule for a $44,000 loan to be repaid in equal installments at...
a. Complete an amortization schedule for a $44,000 loan to be
repaid in equal installments at the end of each of the next three
years. The interest rate is 11% compounded annually. Round all
answers to the nearest cent.
Beginning
Repayment
Ending
Year
Balance
Payment
Interest
of Principal
Balance
1
$
$
$
$
$
2
$
$
$
$
$
3
$
$
$
$
$
b. What percentage of the payment represents interest and what
percentage represents principal...
a. Complete an amortization schedule for a $38,000 loan to be repaid in equal installments at...
a. Complete an amortization schedule for a $38,000 loan to be
repaid in equal installments at the end of each of the next three
years. The interest rate is 12% compounded annually. Round all
answers to the nearest cent.
Beginning Balance -Payment -Interest -Repayment of Principal
-Ending Balance
1 $ $ $ $ $
2 $ $ $ $ $
3 $ $ $ $ $
b. What percentage of the payment represents interest and what
percentage represents principal for...
a. complete an amortization schedule for a 33,000 loan to be repaid in equal installments at...
a.
complete an amortization schedule for a 33,000 loan to be repaid in
equal installments at the end of each of the next three years. The
interest rate is 12% compounded annually. round all answers to the
nearest cent.
b. what percentage of the payment represents interest and what
percentage represents principal for each of the three years? round
all answers to two decimal places .
Complete an amortization schedule for a $23,000 loan to be repaid in equal installments at the...
Complete an amortization schedule for a $23,000 loan to be
repaid in equal installments at the end of each of the next 3
years. The interest rate is 11% compounded annually. If an amount
is zero, enter "0". Do not round intermediate calculations. Round
your answers to the nearest cent.
Beginning
Repayment
Remaining
Year
Balance
Payment
Interest
of Principal
Balance
1
$
$
$
$
$
2
3
What percentage...
Complete an amortization schedule for a $42,000 loan to be repaid in equal installments at the...
Complete an amortization schedule for a $42,000 loan to be
repaid in equal installments at the end of each of the next three
years. The interest rate is 12% compounded annually. Round all
answers to the nearest cent.
Beginning
Repayment
Ending
Year
Balance
Payment
Interest
of Principal
Balance
1
$
$
$
$
$
2
$
$
$
$
$
3
$
$
$
$
$
b. What percentage of the payment represents interest and what
percentage represents principal for...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- Please explain your view of the most difficult aspect of Customs Compliance?
- You are required to write an interactive program that prompts the user for seven (7) real...
- Describe in detail the five-forces model of industry competition. What causes each of the forces to...
- Java Programming. I'm writing this in an IDE called Eclipse. If you could also use comments...
- Scenario: You are a departmental manager in a large community hospital in an urban area. Your...
- Need to answer the following 3 questions from "The Sixties, AMerican Yawp" (just google, you will...
- Write a c++ program to load two column of numbers, do arithmetic operations to the data...
ADVERTISEMENT