Question

In: Finance

Home: $180,000 with $40,000 down payment. Financed for 20 years at 6% APR. Create loan amortization...

Home: $180,000 with $40,000 down payment. Financed for 20 years at 6% APR.

Create loan amortization schedule in excel for constant payment method.

Rework with an extra $40 in principal

Create a loan amortization schedule in excel for constant amortization method.

Expert Solution

We can use following Present Value of an Annuity formula to calculate the periodic annual payments with 6% interest rate

PV = PMT * [1-(1+i) ^-n)]/i

Where,

Present value of loan (PV) =$140,000

Annual payments PMT =?

Number of annual payments n = 20 (years)

Annual interest rate I =6%

Therefore

$140,000 = PMT * [1- (1+6%) ^-20]/6%

Or PMT = $12,205.84

Therefore the annual payment for loan is $12,205.84

Amortization schedule of loan:

Home cost =	$180,000
Down payment =	$40,000
Loan amount =	$140,000.0
Annual payment =	$12,205.84
Interest rate =	6%
Time period =	20
Amortization Schedule
Year	Beginning Balance	Total payment (PMT)	Interest Payment @ 6% of beginning balance	Principal payment (Total Payment - Interest)	Ending Balance (Beginning balance - Principal Payment)
1	$140,000.00	$12,205.84	$8,400.00	$3,805.84	$136,194.16
2	$136,194.16	$12,205.84	$8,171.65	$4,034.19	$132,159.97
3	$132,159.97	$12,205.84	$7,929.60	$4,276.24	$127,883.73
4	$127,883.73	$12,205.84	$7,673.02	$4,532.81	$123,350.92
5	$123,350.92	$12,205.84	$7,401.06	$4,804.78	$118,546.14
6	$118,546.14	$12,205.84	$7,112.77	$5,093.07	$113,453.07
7	$113,453.07	$12,205.84	$6,807.18	$5,398.65	$108,054.41
8	$108,054.41	$12,205.84	$6,483.26	$5,722.57	$102,331.84
9	$102,331.84	$12,205.84	$6,139.91	$6,065.93	$96,265.91
10	$96,265.91	$12,205.84	$5,775.95	$6,429.88	$89,836.03
11	$89,836.03	$12,205.84	$5,390.16	$6,815.68	$83,020.35
12	$83,020.35	$12,205.84	$4,981.22	$7,224.62	$75,795.74
13	$75,795.74	$12,205.84	$4,547.74	$7,658.09	$68,137.64
14	$68,137.64	$12,205.84	$4,088.26	$8,117.58	$60,020.06
15	$60,020.06	$12,205.84	$3,601.20	$8,604.63	$51,415.43
16	$51,415.43	$12,205.84	$3,084.93	$9,120.91	$42,294.52
17	$42,294.52	$12,205.84	$2,537.67	$9,668.17	$32,626.35
18	$32,626.35	$12,205.84	$1,957.58	$10,248.26	$22,378.09
19	$22,378.09	$12,205.84	$1,342.69	$10,863.15	$11,514.94
20	$11,514.94	$12,205.84	$690.90	$11,514.94	$0.00

Amortization schedule of loan with an extra $40 in principal:

Home cost =	$180,000
Down payment =	$40,000
Loan amount =	$140,000.0
Annual payment =	$12,205.84
Extra payment in principal	$40.00
Total payment (pricipal + interest)=	$12,245.84
Interest rate =	6%
Time period =	20
Amortization Schedule
Year	Beginning Balance	Total payment (PMT)	Interest Payment @ 6% of beginning balance	Principal payment (Total Payment - Interest)	Ending Balance (Beginning balance - Principal Payment)
1	$140,000.00	$12,245.84	$8,400.00	$3,845.84	$136,154.16
2	$136,154.16	$12,245.84	$8,169.25	$4,076.59	$132,077.57
3	$132,077.57	$12,245.84	$7,924.65	$4,321.18	$127,756.39
4	$127,756.39	$12,245.84	$7,665.38	$4,580.45	$123,175.94
5	$123,175.94	$12,245.84	$7,390.56	$4,855.28	$118,320.65
6	$118,320.65	$12,245.84	$7,099.24	$5,146.60	$113,174.06
7	$113,174.06	$12,245.84	$6,790.44	$5,455.39	$107,718.66
8	$107,718.66	$12,245.84	$6,463.12	$5,782.72	$101,935.94
9	$101,935.94	$12,245.84	$6,116.16	$6,129.68	$95,806.26
10	$95,806.26	$12,245.84	$5,748.38	$6,497.46	$89,308.80
11	$89,308.80	$12,245.84	$5,358.53	$6,887.31	$82,421.49
12	$82,421.49	$12,245.84	$4,945.29	$7,300.55	$75,120.94
13	$75,120.94	$12,245.84	$4,507.26	$7,738.58	$67,382.36
14	$67,382.36	$12,245.84	$4,042.94	$8,202.90	$59,179.46
15	$59,179.46	$12,245.84	$3,550.77	$8,695.07	$50,484.39
16	$50,484.39	$12,245.84	$3,029.06	$9,216.77	$41,267.62
17	$41,267.62	$12,245.84	$2,476.06	$9,769.78	$31,497.84
18	$31,497.84	$12,245.84	$1,889.87	$10,355.97	$21,141.87
19	$21,141.87	$12,245.84	$1,268.51	$10,977.33	$10,164.54
20	$10,164.54	$10,774.41	$609.87	$10,164.54	$0.00

jeff jeffy answered 1 year ago

Create an Amortization Schedule for a home that cost $259,000 with 20% down for a down-payment....

Create an Amortization Schedule for a home that cost $259,000 with 20% down for a down-payment. Do not use an online amortization schedule. Note the textbook amortization schedule is using an annual payment. Please complete a schedule for both 180 months (15 years) and 360 months (30 years). The interest rate for the year is 2.75% according to Bankrate for the area the home is in. Remember to divide your interest by 12 to get a monthly rate. How much...

A home has price $260,000. After a down payment of $40,000, the rest is financed over...

A home has price $260,000. After a down payment of $40,000, the rest is financed over 25 years at an annual interest rate of 6% compounded monthl y. a) Use the appropriate table in your book to find the monthly payment to the bank. (Do NOT use annuity formulas). b) Suppose that annual taxes are $3120 and homeowner’s insurance has an annual premium of $720. Lenders often collect these amounts in equal monthly installments along with the mortgage payment and...

You bought a $225,000 house with a $40,000 down payment and financed the remaining $185,000 with...

You bought a $225,000 house with a $40,000 down payment and financed the remaining $185,000 with a 30-year mortgage (home loan). The interest rate on the loan is 5%. a) Find the monthly payment. b) After 12 years of paying on this mortgage, you decide to explore refinancing. With 18 years left to go, what is your remaining loan balance? c) If you do refinance the amount from part (b) with a new 15-year loan at 3% interest, how much...

You financed the purchase of a $300,000 apartment with a down payment in cash of 20%...

You financed the purchase of a $300,000 apartment with a down payment in cash of 20% of the purchase price. The remaining 80% is financed with a mortgage with a 1% monthly interest rate over the next 20 years. The mortgage is repaid with equal monthly installments. Compute the monthly installments on the mortgage. (5 points) What is the outstanding principal balance of the mortgage after 5 years (i.e., after 60 installments)? (5 points) Ten years later (after 120 installments),...

An investor has $60,000 to invest in a $280,000 property. He can obtain either a $220,000 loan at 9.5 percent for 20 years or a $180,000 loan at 9 percent for 20 years and a second mortgage for $40,000 at 13 percent for 20 years.

An investor has $60,000 to invest in a $280,000 property. He can obtain either a $220,000 loan at 9.5 percent for 20 years or a $180,000 loan at 9 percent for 20 years and a second mortgage for $40,000 at 13 percent for 20 years. All loans require monthly payments and are fully amortizinga. Which of alternative should the borrower choose, assuming he will own the property for the full loan term?b. Would your answer change if the borrower plans...

You put 20% down on a home with a purchase price of $250,000. The down payment...

You put 20% down on a home with a purchase price of $250,000. The down payment is thus $50,000, leaving a balance owed of $200,000. A bank will loan you this remaining balance at 3.91% APR. You will make monthly end-of-the-period payments with a 30-year payment schedule. What is the monthly annuity payment under this schedule?

Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment:...

Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment: Equal annual payment Interest rate: Annual interest rate is 3.0% in year 1, and increases 0.1% in year 2, etc. In other words, the annual interest rates will be 3.0%, 3.1%, 3.2%,…. M1 Assignment - Goal-seek Loan Amortization Schedule Rubric Successfully created year-by-year table with Year, Beginning balance, PMT, interest, principal, ending balance 3 pts Successfully created a column to reflect floating interest rates...

Personal Finance For a $27,000 student loan with a 6% APR, how much of the payment...

Personal Finance For a $27,000 student loan with a 6% APR, how much of the payment will go toward the principal and how much will go toward paying interest for each of the first six payments? Assume this is a 10-year loan with monthly payments. (Do not round intermediate calculations. Round your answers to 2 decimal places.) Payment Number Interest Paid Principal Paid

Consider a 15 year mortgage with a 3% APR interest rate and a 20% down payment...

Consider a 15 year mortgage with a 3% APR interest rate and a 20% down payment if you can afford a $1500 monthly payment, how expensive a house can you buy? a. $254,250 b. $217,208 c. $271,510 d. $298,402

Loan Amortization Schedule, $80,000,000 at 8% for 15 years Year Beginning Amount Payment Interest Payment of...

Loan Amortization Schedule, $80,000,000 at 8% for 15 years Year Beginning Amount Payment Interest Payment of Principal Ending Balance 1 $ 80,000,000.00 $ 1,933.28 $ 533,333.33 $ (531,400.05) $ 80,531,400.05 2 $ 80,531,400.05 $ 1,933.28 $ 536,876.00 $ (534,942.72) $ 81,066,342.77 3 $ 81,066,342.77 $ 1,933.28 $ 540,442.29 $ (538,509.01) $ 81,604,851.78 4 $ 81,604,851.78 $ 1,933.28 $ 544,032.35 $ (542,099.07) $ 82,146,950.84 179 $ 260,404,969.06 $ 1,933.28 $ 1,736,033.13 $ (1,734,099.85) $ 262,139,068.91 180 $ 262,139,068.91 $ 1,933.28 $ ...