Spreadsheet 1: Amortization Table
Create an amortization table in MS-Excel in the format shown below:
Scenario: 2 years ago Janice got a $100,000, 15-year mortgage with an annual interest rate of 6% and monthly payments.
1) What is her monthly payment?
2) How much does she owe today (after 24 payments)?
3) How much will she owe in 3 years (after 60 payments)?
4) How much will she owe in 3 years (after 60 payments) if she makes an extra $200 payment every month starting today?
1) | Monthly payment using loan amortization formula = 100000*0.005*1.005^180/(1.005^180-1)= | $ 843.86 |
2) | Amount owed after 24 payments = PV of the remaining 156 | |
installments = 843.86*(1.005^156-1)/(0.005*1.005^156) = | $ 91,255.39 | |
3) | Amount owed after 60 payments = PV of the remaining 120 | |
installments = 843.86*(1.005^120-1)/(0.005*1.005^120) = | $ 76,009.38 | |
4) | CV of 200 paid for 60 months = 200*(1.005^60-1)/(0.005) = | $ 13,954.01 |
Amount owed at the end of 60 months = 76009.38-13954.01 = | $ 62,055.38 |
Spreadsheet 1: Amortization Table Create an amortization table in MS-Excel in the format shown below: Scenario:...
Vanna has just financed the purchase of a home for $200 000. She agreed to repay the loan by making equal monthly blended payments of $3000 each at 4%/a, compounded monthly. a. Create an amortization table using a Microsoft Excel spreadsheet. In your answer include all the formulas used.b.How long will it take to repay the loan?c. How much will be the final payment?d. Determine how much interest she will pay for her loan.e. Use Microsoft Excel to graph the amortization...
Calculate all of the problems in the document below in an Excel spreadsheet or on a financial calculator. Please show your work in order to get credit. For each problem, state the inputs given, what you are being asked to find (the missing input), and then use the Finance function to get the correct answer (if using Excel). 1. If you wish to accumulate $100,000 in 5 years, how much must you deposit today in an account that pays an...
Loans Excel Homework Assignment Mortgage Amortization Schedule Excel Assignment 1. Excel: Complete the amortization table provided in the Excel document posted in in the homework area by setting the appropriate values for a $165,000, 30-year mortgage at 4.5% interest and using Excel's autofill (drag) feature to fill in the cells to the end of the mortgage period. Use this to answer the following: a. How much of the first payment goes towards the principal? How much goes toward the interest?...
7A) Use excel to build a monthly amortization table for a 30-year 5% fix-rate mortgage on a $320,000 home, with a $15,000 down payment. [Please be good to the forests and cut and paste only the first and last few rows of the table, not the whole thing.] 7B) Using the amortization table you built for the previous question, answer the following [Again, just cut and paste the segment of the amortization table that helps you answer the question.]: a....
Amortization Table In order to buy a house, Robert is going to borrow $220,000 today with an 8.50% nominal annual rate of interest. He is going to make monthly payments over 15 years. Assuming full amortization of the loan, what percentage of his second monthly payment will go toward the repayment of principal?(round each calculation to the nearest dollar.) a. 28.25% b. 29.65% c. 26.55% d. 27.90% e. 30.15%
b. Fill in the partial amortization schedule for the loan, rounding your answers to two decimal places. Holly received a loan of $36,000 at 3.5% compounded monthly. She had to make payments at the end of every month for a period of 5 years to settle the loan. a. Calculate the size of payments. 0.00 Round to the nearest cent Interest Principal Payment Principal Payment Portion Portion Balance Number $36,000.00 $0.00 $0.00 $0.00 $0.00 1 $0.00 $0.00 $0.00 $0.00 2...
14. Loan amortization and capital recovery After Shipra got a job, the first thing she bought was a new car. She took out an amortized loan for $20,000—with no ($0) down payment. She agreed to pay off the loan by making annual payments for the next four years at the end of each year. Her bank is charging her an interest rate of 6% per year. Yesterday, she called to ask that you help her compute the annual payments necessary...
i need part B and the rest on excel Question 1 Prepare the amortization schedule for a thirty-year loan of $100,000. The APR is 3% and the loan calls for equal monthly payments. The following table shows how you should prepare the amortization schedule for the loan. a. Interest Principal Ending Month lPayment Payment Payment Balance Beginning ota S100,000.00 b. Use the annuity formula to find how much principal you still owe to the bank at the end of the...
Good evening, everyone , may someone answer this engineering economics question on excel spreadsheet? Some years ago, Penny purchased the car of her dreams for $25,000 by paying 20% down at purchase time and taking a $20,000, 5-year, 6% per year, compounded monthly loan with 60 monthly payments of $386.66 each. She is examining her loan situation and would like to have some specific information. Help her obtain the following: a) ) Verification of the current monthly payment amount. b)...