John borrows $150,000. The terms of the loan are 7.5% over the next 5 years. It is important to note that he makes annual rather than monthly payments.

Construct a loan amortization schedule that shows the 5 payments of John's loan.

EXCEL TEMPLATE

To create a loan amortization schedule, we need to calculate the regular payment amount and allocate how much of each payment goes towards the principal and interest. Here's how you can create the loan amortization schedule for John's loan:

Step 1: Calculate the regular payment amount:
To calculate the regular payment amount, we can use the formula for an ordinary annuity:

P = A * (1 - (1 + r)^(-n)) / r

where:
P = Loan Principal ($150,000 in this case)
A = Regular payment amount
r = Interest rate per period (7.5% per year or 0.075)
n = Number of periods (5 years)

Plug in the values:
$150,000 = A * (1 - (1 + 0.075)^(-5)) / 0.075

Solve for A:
A = $150,000 * 0.075 / (1 - (1 + 0.075)^(-5))
A ≈ $37,702.12

So, John's regular payment amount is approximately $37,702.12 per year.

Step 2: Calculate interest and principal payments for each year:

Year 1:
Interest Payment = Principal * Interest Rate
Interest Payment = $150,000 * 0.075 = $11,250
Principal Payment = Regular Payment - Interest Payment
Principal Payment = $37,702.12 - $11,250 = $26,452.12

Year 2:
Interest Payment = (Principal - Principal Payment from Year 1) * Interest Rate
Interest Payment = ($150,000 - $26,452.12) * 0.075 = $9,725.41
Principal Payment = Regular Payment - Interest Payment
Principal Payment = $37,702.12 - $9,725.41 = $27,976.71

Year 3:
Interest Payment = (Principal - Principal Payment from Year 2) * Interest Rate
Interest Payment = ($150,000 - $26,452.12 - $27,976.71) * 0.075 = $8,207.05
Principal Payment = Regular Payment - Interest Payment
Principal Payment = $37,702.12 - $8,207.05 = $29,495.07

Year 4:
Interest Payment = (Principal - Principal Payment from Year 3) * Interest Rate
Interest Payment = ($150,000 - $26,452.12 - $27,976.71 - $29,495.07) * 0.075 = $6,869.31
Principal Payment = Regular Payment - Interest Payment
Principal Payment = $37,702.12 - $6,869.31 = $30,832.81

Year 5:
Interest Payment = (Principal - Principal Payment from Year 4) * Interest Rate
Interest Payment = ($150,000 - $26,452.12 - $27,976.71 - $29,495.07 - $30,832.81) * 0.075 = $5,997.89
Principal Payment = Regular Payment - Interest Payment
Principal Payment = $37,702.12 - $5,997.89 = $31,704.23

Now you can construct the loan amortization schedule:

Year 1:
Regular Payment: $37,702.12
Principal Payment: $26,452.12
Interest Payment: $11,250
Remaining Balance: $150,000 - $26,452.12 = $123,547.88

Year 2:
Regular Payment: $37,702.12
Principal Payment: $27,976.71
Interest Payment: $9,725.41
Remaining Balance: $123,547.88 - $27,976.71 = $95,571.17

Year 3:
Regular Payment: $37,702.12
Principal Payment: $29,495.07
Interest Payment: $8,207.05
Remaining Balance: $95,571.17 - $29,495.07 = $66,076.10

Year 4:
Regular Payment: $37,702.12
Principal Payment: $30,832.81
Interest Payment: $6,869.31
Remaining Balance: $66,076.10 - $30,832.81 = $35,243.29

Year 5:
Regular Payment: $37,702.12
Principal Payment: $31,704.23
Interest Payment: $5,997.89
Remaining Balance: $35,243.29 - $31,704.23 = $3,539.06

This completes the loan amortization schedule for John's loan. You can create a table or use a spreadsheet program like Excel to present this information in a more organized format.