Question

In: Accounting

Bob Jones bought a new home for $150,000.00 at a rate of 8% interest for 30...

Bob Jones bought a new home for $150,000.00 at a rate of 8% interest for 30 years. He made a 20% downpayment on the home. Prepare an amortization schedule for the first three periods assuming the 20% downpayment and use a Rate per $1,000 of 7.34 (from the amortization table). For each of the three payments, state the
1.) Beginning of the Month Balance,
2.) Interest paid,
3.) Principle paid, and
4.) End of the Month Balance

Solutions

Expert Solution

An Amortization schedule lists all the monthly payments of a loan and how much of each payment goes to interest and how much to the principal.

This is a $150,000 30 year loan charging 8% interest (with monthly payment). A 20% of downpayment on $150,000 is $30,000 which made it a total of $120,000 of home loan.

Firstly, monthly payment is calculated on loan and then the amorization schedule is made. We can calculate the monthly payment with the help of excel. Below is the image of how we can calculate the monthly payment in excel spreadsheet.(Formula is in the function box)

Now, we have a 30-year mortgage for $120,000 at a 8% interest rate that carries a monthly payment of $880. In 1st month, we will take $120,000 and multiply it by 8% to get $9,600. Divide that by 12, and we will have $800 in interest for your first monthly payment. The remaining $80 goes toward paying down principal.

In second month, the outstanding principal balance is $120,000 minus $80, or $119,920. Multiply that by 8% and divide by 12, and you get a slightly smaller amount $799.46, going toward interest. Gradually in the coming months, less money will go toward interest, and your principal balance will get less.

Month Beginning of the month balance Payment Interest paid Principal paid End of the month balance
1. $120,000.00 $880.52 $800.00 $80.52 $119,919.48
2. $119,919.48 $880.52 $799.46 $81.05 $119,918.95
3. $119,918.95 $880.52 $798.92 $81.59 $119,918.41

Loan amortization schedule can also be created in excel through the following steps:

(1) PMT function for Monthly payment

(2)PPMT function for Principal payment

(3) IPMT function for interest payment

(4) To calculate the end of the balance


Related Solutions

Bob Jones bought a new log cabin for $63,000 at 10% interest for 30 years. Prepare...
Bob Jones bought a new log cabin for $63,000 at 10% interest for 30 years. Prepare an amortization schedule for the first three periods. (Use Table 15.1.) (Do not round intermediate calculations. Round your final answers to the nearest cent.)
Mortgage interest rates and home prices 30-year mortgage rates year interest rate (%) Median home price...
Mortgage interest rates and home prices 30-year mortgage rates year interest rate (%) Median home price 1988 10.30 183,800 1989 10.30 183,200 1990 10.10 176,900 1991 9.30 173,500 1992 8.40 172,900 1993 7.30 173,200 1994 8.40 173,200 1995 7.90 169,700 1996 7.60 174,500 1997 7.60 177,900 1998 6.90 188,100 1999 7.40 203,200 2000 8.10 230,200 2001 7.00 258,200 2002 6.50 309,800 2003 5.50 329,800 1.    Generate two separate scatter plots, following the requirements below, with the data provide. a.    year...
Jackie bought a $200,000 house and has a 30 year mortgage at anominal interest rate...
Jackie bought a $200,000 house and has a 30 year mortgage at a nominal interest rate of 4.8% convertible monthly. Jackie must pay level payments at the end of each month. Find the amount of her monthly mortgage payment. After 200 payments have been made, what is the ratio of total interest paid to total principal repaid?
Suppose you bought a new home for $240,000 using a 30-year mortgage with monthly payments of...
Suppose you bought a new home for $240,000 using a 30-year mortgage with monthly payments of $1,548.667. The annual interest rate of the mortgage is 6.7%. After the first 3 years (36 monthly payments), approximately how much money have you paid in interest and how much in principal? Group of answer choices Interest: $55,752.02; Principal: $8,294.67 Interest: $47,457.35; Principal: $8,294.67 Interest: $1,548.67; Principal: $231,705.33 Interest: $47,457.35; Principal $1,548.67
Bill has bought a new home in Canberra. He borrowed $600 000 at a rate of...
Bill has bought a new home in Canberra. He borrowed $600 000 at a rate of 3.5% p.a., which is to be repaid in annual instalments over a thirty year period. The first instalment is due on 19 March 2020. Like Bill, on the situation above, Scott has bought a house in Canberra, borrowing the same amount, and on the the same terms. Scott’s bank, however, offers an ‘interest offset’ account facility with the loan. Like Bill, Scott’s first payment...
Consider a 30-year fixed-rate home loan of $545,500 with an interest rate of 3.25%. What is...
Consider a 30-year fixed-rate home loan of $545,500 with an interest rate of 3.25%. What is the total amount of interest paid? (Round your answer to the nearest cent.)?
George bought a new car with a 4.1% interest rate for 5 years. The loan is...
George bought a new car with a 4.1% interest rate for 5 years. The loan is for $35,000. What is the interest portion of the first 12 payments?
1. Bill has bought a new home in Canberra. He borrowed $600 000 at a rate...
1. Bill has bought a new home in Canberra. He borrowed $600 000 at a rate of 3.5% p.a., which is to be repaid in annual instalments over a thirty year period. The first instalment is due on 19 March 2020. a. Choosing a valuation date of 19 March 2019, write down the equation of value that will give Bill’s annual repayments. Support your answer with a fully labelled cash flow diagram, drawn from Bill’s perspective. b. What are Bill’s...
a. The cost of a new automobile is $11,200. If the interest rate is 8%, how...
a. The cost of a new automobile is $11,200. If the interest rate is 8%, how much would you have to set aside now to provide this sum in eight years? (Do not round intermediate calculations. Round your answer to 2 decimal places.) Present value            $ b. You have to pay $15,000 a year in school fees at the end of each of the next nine years. If the interest rate is 11%, how much do you need to set...
You borrow $675,000 to buy a home using a 30-year mortgage with an interest rate of...
You borrow $675,000 to buy a home using a 30-year mortgage with an interest rate of 3.675 percent and monthly payment. Calculate the interest portion of the 20th payment. Disregard property taxes and mortgage insurance. Group of answer choices $2,006 $1,999 $2,002 $1,818 $1,821
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT