Question

I have bought a house for $300,000. I made a 20% down payment and borrowed the rest at 4.2% APR with monthly compounding. It is a 30-year amortized loan. Prepare the first two rows of the amortization table (beginning balance, PMT, interest paid, principal paid, ending balance).

*Please pot any formulas used or calculations done on calculator*

Answer 1

Price = $ 300000

Doen Payment = $ 300000 * 20%

= $ 60000

Loan = $ 300000 - $ 60000

=$ 240000

EMI :
EMI or Instalment is sum of money due as one of several equal payments for loan/ Mortgage taken today, spread over an agreed period of time.

EMI = Loan / PVAF (r%, n)
PVAF = SUm [ PVF(r%, n) ]
PVF(r%, n) = 1 / ( 1 + r)^n
r = Int rate per period
n = No. of periods

How to calculate PVAF using Excel:
=PV(Rate,NPER,-1)
Rate = Disc Rate
NPER = No.of periods

Opening Balance = Previous month closing balance
EMI = Instalment calculated
Int = Opening Balance * Int Rate
Principal repay = Instalment - Int
Closing Balance = Opening balance - Principal Repay

Particulars	Amount
Loan Amount	$          240,000.00
Int rate per Month	0.3500%
No. of Months	360

EMI = Loan Amount / PVAF (r%, n)
Where r is Int rate per Month & n is No. of Months
= $ 240000 / PVAF (0.0035 , 360)
= $ 240000 / 204.4918
= $ 1173.64

Loan Amortization Schedule:

Opening Balance = Previous month closing balance
EMI = Instalment calculated
Int = Opening Balance * Int Rate
Principal repay = Instalment - Int
Closing Balance = Opening balance - Principal Repay

Period	Opening Bal	EMI	Int	Principal Repay	Closing Outstanding
1	$          240,000.00	$         1,173.64	$               840.00	$                333.64	$             239,666.36
2	$          239,666.36	$         1,173.64	$               838.83	$                334.81	$             239,331.55