Question

Mortgage Information
Annual Interest Rate	4.90%
Repayment Years	30
Price of House	$275,000
Down Payment	$55,000
Principal of Loan
Monthly Payments

On the Mortgage worksheet, use the data provided to enter a formula in cell B6 to calculate the principal of the loan that will be required to purchase the house.

On the Mortgage worksheet, use the PMT function in cell B7 to calculate the monthly payments of the mortgage. Use cell locations from this worksheet to define each argument of the function. Assume that payments are made at the end of each month.

Answer 1

Following information is given in the question:

Mortgage Information
Annual Interest Rate	4.90%
Repayment Years	30
Price of House ($)	2,75,000
Down Payment ($)	55,000

We need to compute the Principal of loan ant the Monthly payment when the payments are made at the end of each month.

Principal of Loan = (Price of House - Down Payment) = 275000-55000 =$ 220000

Monthly Payments using PMT

Annual Interest Rate = 4.90%

So, Monthly interest Rate = (Annual Interest Rate/12) = 4.90%/12 = 0.41%

Repayment Years = 30 years

So, Repayment Months = (Repayment Years*12) = 30*12 = 360 months

Principal is already calculated above, and the value has been shown in Cell B6. Lets calculate Monthly payment using excel PMT function:

Monthly Payment = PMT(Rate,Nper,Pv,[Fv],[Type])

Rate is monthly interest rate

Nper is repayment period in months

Pv is Present Value i.e. Principal amount or borrowed amount

Fv is Future value which after repayment will be “0”

Type is if the monthly payment is done at the end of every month it will be “0”

Mortgage Information
Monthly interest Rate = (Annual Interest Rate/12)	0.41%	Rate
Repayment Months = (Repayment Years*12)	360	Nper
Price of House	2,75,000
Down Payment	55,000
Principal of Loan = (Price of House - Down Payment)	220000	Pv
Monthly Payments	-1167.60	PMT

This when we calculate in the excel will look like this:

Monthly Payment= PMT(Rate,Nper,Pv,[Fv],[Type])

=PMT(B2,B3,B6,0,0)

Monthly Payment= $ -1167.60