Hello,

I need help with creating a loan amortization schedule. I have 30- year mortgage, total amount of the mortgage is $300,000, interest rate is 6%. The mortgage requires monthly payment of $1,798.65, with a final payment of $1,800.23. I included Excel document containing a table. This table was created by the teacher who calculated payments for the first two periods. I have to fill in the rest of the payments of the 360 payments (30 years of mortgage) total. To do that, I have to determine how each of the amounts in the table are obtained. I was able to find formulas to calculate PAYMENT AMOUNT, 6% INTEREST RATE, PRINCIPAL, ENDING BALANCE, NON-CURRENT. However, I could not figure out how to calculate columns 5 and 6, which are CURRENT and ANNUAL INTEREST EXPENSE. I need a formula to calculate these payments.

The six column, headed CURRENT reflects the current portion of the principal (12 months).

The ANNUAL INTERSET EXPENSE column provides a running total of the interest expense on the mortgage for the entire 12-month period.

Please, explain how to calculate columns CURRENT and ANNUAL INTERSET EXPENSE using formulas.

What formulas I have to use to have $3,702.44 for #1 period and $3,720.95 for #2 period in column CURRENT. I could not figure out how the teacher calculated those numbers.

Below, I have attached Excel document with the table.

Thank you very much!

Loan amount	$300,00
Interest rate	0.06
Interest rate each payment	0.005
Loan term	30 years
Payment amount	$1,798.65
Final payment	$1,800.23
Loan Amortization Schedule
For a loan of $300,000 at 6% interset, Over 30 years
Payment	Payment	6%	Principal	Ending	Current	Non-	Annual
Number	Amount	Interest Expense		Balance	Principal	Current	Interest Expense
0				$ 300,000.00	$ 3,684.02	$ 296,315.98	$0
1	$ 1,798.65	$ 1,500.00	$ 298.65	$ 299,701.35	$ 3,702.44	$ 295,998.91
2	$ 1,798.65	$ 1,498.51	$ 300.14	$ 299,401.21	$ 3,720.95	$ 295,680.26
3	$ 1,798.65	$ 1,497.01	$ 301.64	$ 299,099.56
			Break in sequence
359	$ 1,798.65	$ 17.86	$ 1,780.79	$ 1,791.28	$ 1,791.27
360	$ 1,800.23	$ 8.96	$ 1,791.27	$ -	$ -		$ 685.50