Create an Amortization Table from scratch in Excel. Make it for a 30-year loan and have...
Create an Amortization Table from scratch in Excel. Make it for a 30-year loan and have the payments start in January 2019. Use a loan amount of $200,000 and an interest rate of 5.5%.
Then, do the following:
1. Highlight remaining balance as of January 2020.
2. Highlight the total interest paid on the loan.
Homework Answers
Using excel formula to calculate
| Years | Beginning Balance= 200000 | PMT =PMT(5.5%,30,-20000) | Interest part of PMT=5.5%*Beginning Principal | Principal part of PMT= PMT-Interest | Ending Balance= Beginning balance-Principal part of PMT |
| 1.00 | 200000.00 | 13761.08 | 11000.00 | 2761.08 | 197238.92 |
| 2.00 | 197238.92 | 13761.08 | 10848.14 | 2912.94 | 194325.98 |
| 3.00 | 194325.98 | 13761.08 | 10687.93 | 3073.15 | 191252.84 |
| 4.00 | 191252.84 | 13761.08 | 10518.91 | 3242.17 | 188010.66 |
| 5.00 | 188010.66 | 13761.08 | 10340.59 | 3420.49 | 184590.17 |
| 6.00 | 184590.17 | 13761.08 | 10152.46 | 3608.62 | 180981.55 |
| 7.00 | 180981.55 | 13761.08 | 9953.99 | 3807.09 | 177174.46 |
| 8.00 | 177174.46 | 13761.08 | 9744.60 | 4016.48 | 173157.98 |
| 9.00 | 173157.98 | 13761.08 | 9523.69 | 4237.39 | 168920.59 |
| 10.00 | 168920.59 | 13761.08 | 9290.63 | 4470.45 | 164450.14 |
| 11.00 | 164450.14 | 13761.08 | 9044.76 | 4716.32 | 159733.82 |
| 12.00 | 159733.82 | 13761.08 | 8785.36 | 4975.72 | 154758.11 |
| 13.00 | 154758.11 | 13761.08 | 8511.70 | 5249.38 | 149508.73 |
| 14.00 | 149508.73 | 13761.08 | 8222.98 | 5538.10 | 143970.63 |
| 15.00 | 143970.63 | 13761.08 | 7918.38 | 5842.69 | 138127.93 |
| 16.00 | 138127.93 | 13761.08 | 7597.04 | 6164.04 | 131963.89 |
| 17.00 | 131963.89 | 13761.08 | 7258.01 | 6503.06 | 125460.83 |
| 18.00 | 125460.83 | 13761.08 | 6900.35 | 6860.73 | 118600.10 |
| 19.00 | 118600.10 | 13761.08 | 6523.01 | 7238.07 | 111362.02 |
| 20.00 | 111362.02 | 13761.08 | 6124.91 | 7636.17 | 103725.86 |
| 21.00 | 103725.86 | 13761.08 | 5704.92 | 8056.16 | 95669.70 |
| 22.00 | 95669.70 | 13761.08 | 5261.83 | 8499.24 | 87170.46 |
| 23.00 | 87170.46 | 13761.08 | 4794.38 | 8966.70 | 78203.75 |
| 24.00 | 78203.75 | 13761.08 | 4301.21 | 9459.87 | 68743.88 |
| 25.00 | 68743.88 | 13761.08 | 3780.91 | 9980.16 | 58763.72 |
| 26.00 | 58763.72 | 13761.08 | 3232.00 | 10529.07 | 48234.64 |
| 27.00 | 48234.64 | 13761.08 | 2652.91 | 11108.17 | 37126.47 |
| 28.00 | 37126.47 | 13761.08 | 2041.96 | 11719.12 | 25407.35 |
| 29.00 | 25407.35 | 13761.08 | 1397.40 | 12363.67 | 13043.68 |
| 30.00 | 13043.68 | 13761.08 | 717.40 | 13043.68 | 0.00 |
| Total Interest | 212832.34 |
Add Answer to:
Create an Amortization Table from scratch in Excel. Make it for
a 30-year loan and have...
PLEASE EXPLAIN HOW TO CREATE THE AMORTIZATION TABLE IN EXCEL! Mini-Project 1: Mortgage Loan Analysis (50...
PLEASE EXPLAIN HOW TO CREATE THE AMORTIZATION TABLE IN EXCEL! Mini-Project 1: Mortgage Loan Analysis (50 points): You plan to buy a house in December 2019. The sale price is $385,000. You need to pay 20% down payments and borrow additional 80% from Bank of America with a 25-year, 3.0% fixed-rate mortgage loan. You are expected to pay an equal MONTHLY payment starting from January 2020 for a total of 25 years. (1) Calculate your expected monthly mortgage payment. (2)...
Amortization of a Loan EXAMPLE EXERCISES For the following exercises, create an amortization table for a...
Amortization of a Loan EXAMPLE EXERCISES For the following exercises, create an amortization table for a $21,500 car loan with 4.5% APR over 72 months. 1. What is the monthly payment? 2. How much is actually paid over the life of the loan? How much interest? 3. How much is saved if you round up to the next $50 increment? Next $100? For the following exercises, create an amortization table for $135,000 home purchase at 4.6% fixed APR. Remember mortgages...
5. Create a complete amortization table for the following loan: Plain vanilla fully amortizing constant payment...
5. Create a complete amortization table for the following loan: Plain vanilla fully amortizing constant payment loan with monthly payments 30 year term Rate on loan 4.5% Loan amount $777,777 ****do work in excel please****
A loan of $470,000 is amortized over 30 years with payments at the end of each...
A loan of $470,000 is amortized over 30 years with payments at
the end of each month and an interest rate of 6.5%, compounded
monthly.
Use Excel to create an amortization table showing, for each of the
360 payments, the beginning balance, the interest owed, the
principal, the payment amount, and the ending balance.
Answer the following, rounding to the nearest penny.
a) Find the amount of each payment. $
b) Find the total amount of interest paid during the...
Create an amortization schedule for a 20-year, $200,000 loan at 7.5% interest with monthly payments. During...
Create an amortization schedule for a 20-year, $200,000 loan at 7.5% interest with monthly payments. During which payment number does the amount applied to the principal exceed the amount paid to interest? True or False? After half of the time has passed in making payments, we also note that exactly half of the overall balance has been paid off. Explain. After completing the schedule, show two different ways for finding the total amount of interest paid over the 20 years.
Create an amortization schedule for a 20-year, $200,000 loan at 7.5% interest with monthly payments. During...
Create an amortization schedule for a 20-year, $200,000 loan at 7.5% interest with monthly payments. During which payment number does the amount applied to the principal exceed the amount paid to interest? True or False? After half of the time has passed in making payments, we also note that exactly half of the overall balance has been paid off. Explain. After completing the schedule, show two different ways for finding the total amount of interest paid over the 20 years.
Please use excel functions 6. What is the payment in month 234 on a 30-year loan...
Please
use excel functions
6. What is the payment in month 234 on a 30-year loan for $925,000 at 4.85% that requires no payments during the first five years and fully amortizing payments for the remaining years of the loan? Answer Loan Amount Years Periods Per Year Interest Rate Balance at EOM 60 Total Amortization Years 7 What is the balloon nayment du at maturity for a 10-year loan for $325 000 at 5 9507 if
Excel Online Structured Activity: Amortization schedule The data on a loan has been collected in the...
Excel Online Structured Activity: Amortization schedule The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Complete an amortization schedule for a $10,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 6% compounded annually. Round all answers to the nearest cent. Ending Beginning Balance Repayment of...
A loan of $450,000 is amortized over 30 years with payments at the end of each...
A loan of $450,000 is amortized over 30 years with payments at
the end of each month and an interest rate of 6.3%, compounded
monthly.
Use Excel to create an amortization table showing, for each of the
360 payments, the beginning balance, the interest owed, the
principal, the payment amount, and the ending balance.
a) Find the amount of each payment. $
b) Find the total amount of interest paid during the first 15
payments. $
Suppose that payment number...
Topical Assignment Create a loan example for yourself and create an amortization for your loan. Criteria:...
Topical Assignment Create a loan example for yourself and create an amortization for your loan. Criteria: • State the purpose of the loan, the terms of the loan (including principal, interest rate and loan term) • Create an amortization schedule for the loan. (this may be done by hand or in Excel) • Provide the total amount of principal and interest that will be paid back over the life of the loan.
A loan of $470,000 is amortized over 30 years with payments at
the end of each month and an interest rate of 6.5%, compounded
monthly.
Use Excel to create an amortization table showing, for each of the
360 payments, the beginning balance, the interest owed, the
principal, the payment amount, and the ending balance.
Answer the following, rounding to the nearest penny.
a) Find the amount of each payment. $
b) Find the total amount of interest paid during the...
Please
use excel functions
6. What is the payment in month 234 on a 30-year loan for $925,000 at 4.85% that requires no payments during the first five years and fully amortizing payments for the remaining years of the loan? Answer Loan Amount Years Periods Per Year Interest Rate Balance at EOM 60 Total Amortization Years 7 What is the balloon nayment du at maturity for a 10-year loan for $325 000 at 5 9507 if
Excel Online Structured Activity: Amortization schedule The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Complete an amortization schedule for a $10,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 6% compounded annually. Round all answers to the nearest cent. Ending Beginning Balance Repayment of...
A loan of $450,000 is amortized over 30 years with payments at
the end of each month and an interest rate of 6.3%, compounded
monthly.
Use Excel to create an amortization table showing, for each of the
360 payments, the beginning balance, the interest owed, the
principal, the payment amount, and the ending balance.
a) Find the amount of each payment. $
b) Find the total amount of interest paid during the first 15
payments. $
Suppose that payment number...
Topical Assignment Create a loan example for yourself and create an amortization for your loan. Criteria: • State the purpose of the loan, the terms of the loan (including principal, interest rate and loan term) • Create an amortization schedule for the loan. (this may be done by hand or in Excel) • Provide the total amount of principal and interest that will be paid back over the life of the loan.
Most questions answered within 3 hours.
-
convert grams into vloume with the density of water being at
.997514 g/cm^3 at 23.1 Celsuis...
asked 1 hour ago -
Foreman company obtained a $20,000, 6% loan on May 1, 2018.
Principal and interest will be...
asked 1 hour ago -
5. Problem 11.05 (Discounted Payback)
eBook
Project L costs $60,000, its expected cash inflows are $14,000
per...
asked 1 hour ago -
1. Which of the following is true about politics and health
organizations?
A. Health organizations are...
asked 2 hours ago -
A space vehicle is coasting at a constant velocity of 18.1 m/s
in the +y direction...
asked 3 hours ago -
5) Use the following balanced equation to answer questions
(a)-(i).
3 Cu(s) + 8 HNO3(aq) →...
asked 2 hours ago -
A three-year maturity bond with a 12% annual coupon rate is
currently traded at par $1000...
asked 3 hours ago -
1.
What is the minimum volume to use in the Spec 20 cuvette in order
to...
asked 3 hours ago -
A builder wants to install wall-to-wall carpeting in a living
room of length 8.87 m and...
asked 3 hours ago -
Organizational change can produce anxiety in those affected by
the change. In what ways can change...
asked 3 hours ago -
Ozone in the atmosphere may react with nitric oxide.
From the following data, calculate the DG...
asked 3 hours ago -
Steven Long, a bond analyst, is analyzing a convertible bond.
The characteristics of the bond are...
asked 3 hours ago