In: Accounting
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
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