Question

You have just taken out a 29 -year, $ 118 ,000 mortgage loan at an annual interest rate of 6.2 percent. The mortgage has monthly payments. What is the amount of each payment? Calculate your answer to the nearest $.01. Enter your answer as a positive number.

Please show work on how to compute in MS Excel

Answer 1

EMI :

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

Particulars	Amount
Loan Amount	$         1,18,000.00
Int Rate per month	0.5167%
No. of Months	348

EMI = Loan Amount / PVAF (r%, n)
Where r is Int rate per month & n is no. of months
= $ 118000 / PVAF (0.0052 , 348)
= $ 118000 / 161.3423
= $ 731.36

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

Alt 2 using Excel:

EMI = Loan / PVAF
PVAF Calculation in Excel:
=PV(Rate,NPER,-1)
Rate = Int Rate per month
Nper = No. of Monthly instalments

=PV(0.5167%,348,-1)
161.3356

EMI = 118000 / 161.3356
= 731.36