1 a) How to calculate the monthly payment using PMT function. b) How to calculat
ID: 3873355 • Letter: 1
Question
1 a) How to calculate the monthly payment using PMT function.
b) How to calculate total morgage cost using the Product function
Is the 13 Monthly Total Mortgage Payment Cost Annual Monthly Monthly # of Years | Downpayment Affordable? Scenario #1 Income | Expenses | payment available for Mortgage $2,860.00| $2,860.00| $2,860.00| $300,000| $300,000| $300,000| 100.00% 20.00% 15.00% 530 $1,000 $30 40,000 $1,640.00| $1,640.00| $1,640.00| $1,640.00$2,860.00$300,000 $1,640.001 $2.860.00| s300,000 $1,640.00$2,860.00 $300,000 $1,640.00| $1,640.00$2,860.00$300,000 $1,640.00$2,860.00$300,000 1,640.00$2,860.00 $4,500.00| 15 16 17 18 19 25 2$4,500 3 $4,500 $4,500 $4,500 6 $4,500 7$4,500 8 $4,500 $4,500 10 SA,500 2.00% 5.00% 5.00% 25 $100,000 $10 $10 $10 $10 $10 $2.860.00| $300,000| 15.00% 21 25 6.00% 6.00% 7.00% 25 $300,000 25 26 27Explanation / Answer
1a)
The monthly payment can be calculated using PMT function in excel the formula is :
PMT(rate, nper, pv)
where
rate is the interest rate for motnhly payments divide it by 12
nper is the no.of payments for monthly payments multiply it by 12
pv is the loan amount
Ex:
IN scenario 2 :
annual mortgage payment is : 300,000
Interest rate is : 20%
no.of years is : 25
downpayment : 1000
So from the above values we can find values of rate, nper and pv;
here rate = 20%/12 = 1.67%
nper = 25 * 12 = 300 months or 300 payments
pv = annual mortgage amount - downpayment = 300000-1000 = 299000
therefore monthly payment = PMT(1.67,300,299000)
monthly payyment = $5,018.57
1b)
Total motgage cost can be calculated by multiplying monthly payment and total no.of payments
this can be calulated using PRODUCT function
Toatal mortgage cost = PRODUCT(A,B) where A is the monthly payment and B is total no.of payments
Ex:
for scenario 2
we already calculated monthly payment value and it is $5,018.57.
total no.of payments = 25 * 12 = 300
Toatal mortgage cost = PRODUCT(5,018.57,300) = $1505571.00
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.